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 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

View facts

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

SQLQuery3

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

Connect to Server

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

Database Engine query

2. When browser service is not running

Connect to Database Engine

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.

Feel free to reach out to info@atlassystems.com for additional information.