DAC – Dedicated Admin Connection
Dedicated Admin Connection was introduced way back in SQL 2005. This is a back door connection to SQL Server Instance which should be used ONLY when your SQL instance run is in serious problem. Dedicated Admin Connection has a dedicated scheduler with a worker thread and a memory node. SQL Server internally allocates one worker thread to DAC. Also please note DAC connection doesn’t support parallelism, so it’s used to run simple troubleshooting queries on an emergency situation.
How do I use it?
By default you will not be able to use this connection. DAC should be firstly enabled at instance level to use it.
Enabling DAC from SQL Server Management Studio
Right click on your SQL Instance -> Facets -> From the Facets drop down list select Surface Area Configuration -> Set RemoteDACenabled to True
Enabling DAC using T-SQL
–To Check the status of DAC
EXEC sp_configure ‘remote admin connections’
–To enable DAC connection
EXEC sp_configure ‘remote admin connections’,1
Now we will see how to connect using DAC
To connect from management studio
To connect from command line
SQLCMD –S local –E -A
SQLCMD -S SQLInstanceName -E -A
Script to check if you are really using DAC
select session_id, net_transport, name as ‘Connectivity Name’ from sys.dm_exec_connections C
inner join sys.endpoints E on C.endpoint_id=E.endpoint_id
where [email protected]@spid
Frequent Issues which you can run into while using DAC
1. When you try connecting from Connect to Server dialog from the object explorer you will get the below error
Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)
Connect using the Database Engine Query button in SSMS
2. When browser service is not running
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 43 – An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number) (Microsoft SQL Server, Error: -1)
Make sure your SQL Server browser service is started to use DAC.
Feel free to reach out to [email protected] for additional information.