How to Use Dedicated Administrator Connection in SQL Server

To use a Dedicated Administrator Connection (DAC) in SQL Server, open new query window and in Connect to Server dialog box one need to prefix the word “admin:” before the SERVERNAME as shown in the below snippet in case of a default instance of SQL Server.

DAC connection works with SQL Server and Windows Authentication Mode as long as you have SYSADMIN Privileges on the SQL Server Instance.

If you are not aware how to enable Dedicated Administrator Connection in SQL Server then read “Different Ways to Enable Dedicated Administrator Connection in SQL Server“.

The word “admin:” is case insensitive hence “Admin:” or “ADMIN” all works perfectly. If you have a named instance then type “ADMIN:SERVERNAME\INSTANCENAME”.

Port Number Used by DAC

By default, SQL Server assign dedicated administrator connection port 1434 if the port is not available then SQL Server will dynamically allocate a Port Number to DAC. The port number used by DAC is written to SQL Server Error Log File. Below mentioned is the sample entry on SQL Server Error Logs.

Dedicated admin connection support was established for listening remotely on port 1434. 
Server is listening on ['any' <ipv4> 1434]. 
Server is listening on ['any' <ipv6> 1434].

How to Identify if connection made to the database engine is DAC

You would be able to see the connection name at the bottom of the query window as shown in the snippet below.

Error Message

In case if you unable to establish a successful DAC connection or a DAC connection already exist. Then, you would end up receiving below mentioned error message.

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

Once you have established a successful DAC connection you can execute troubleshooting queries to identify the bottleneck and take action accordingly.

/* Returns one row per authenticated session on SQL Server */
SELECT * FROM sys.dm_exec_sessions 

/* Returns information about each request that is executing within SQL Server. */
SELECT * FROM sys.dm_exec_requests 

/* Returns information about currently active lock manager resources in SQL Server */
SELECT * FROM sys.dm_tran_locks

How to Use Dedicated Administrator Connection Using SQLCMD

To use DAC with SQLCMD you must be a member of SYSADMIN fixed server role.

Default Instance

SQLCMD –S [ServerName] –U [UserName] –P [Password] –A

Named Instance

SQLCMD –S [ServerName\InstanceName] –U [UserName] –P [Password] –A

Conclusion

This one very important feature which DBA’s must consider enabling it across all servers which they manage. This feature can help you troubleshoot issue rather than just restarting SQL Services or rebooting servers remotely.