SQL Server Dedicated Admin Connection – What’s that? How do I use it?

DAC - Dedicated Admin Connection

Dedicated Admin Connection was introduced way back in SQL 2005. This a back door connection to SQL Server Instance which should be used ONLY when your SQL instance run is 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

RECONFIGURE

Now we will see how to connect using DAC

To connect from management studio

Admin:SQLInstanceName

To connect from command line

SQLCMD –S local –E -A

or

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 session_id=@@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

Error Message:

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

Error Message:

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.

Hope this help. Check whether your DAC is enabled in your environment, because you don’t know when you will need it.

Cheers,

Naveen