SQL Server dedicated admin connection – What’s that? How do I use it?

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

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.

Feel free to reach out to info@f8h.51d.myftpupload.com for additional information.

Free Component Quote

If you have any questions or would like to tour our facility,

please feel free to call us at 800-727-7844.

Transform your IT Operations with this exclusive eBook on AIOps

Subscribe to our mailing list

Categories

Categories

Get more industry trends and insights

Previous
Next