Using the SQL Server Dedicated Administrator Connection

Q: Once in a while, my SQL Server system becomes unresponsive and I want to go in a try to kill one of the running jobs but I can’t get connected. I understand the Dedicated Administrator Connection (DAC) will let me connect to the server even if the system isn’t accepting normal client connections. How do I use the DAC?

A: SQL Server’s DAC is a special diagnostic connection that’s intended for administrators to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests. By default SQL Server listens for DAC on TCP port 1434. However, you can configure this. If you have changed the default connection port, you can find the port number the DAC is listening on in the Error log.

You can use the DAC in two different ways. You can use it via the sqlcmd prompt or you can use it from SQL Server Management Studio (SSMS).

To use DAC from the sqlcmd prompt, you need to start sqlcmd using the administrator switch (-A) as you can see in the following example.

sqlcmd -S MySQL Server -U sa -P –A

sqlcmd –A –d master

The first example shows how to open a DAC to the server named MySQLServer. By default, the DAC will open to the default database. The second example shows how you can open a DAC connection to the master database.

To use DAC from SSMS, select Database Engine Query on the toolbar. Then, in the Connect to Database Engine dialog box, type the word ADMIN: followed by the name of the server instance in the Server name prompt. For example, to connect to a server enter ADMIN:MySQLServer. Then, complete the Authentication section by entering the login information for a member of the sysadmin group and then select Connect.

Related: SQL Server TCP and UDP Ports