One of the best additions to the SQL Server feature set was the Dedicated Administrator Connection (DAC). Prior to this being a part of the platform, an overloaded instance might not even allow a DBA to connect and perform any actions. In the SQL Server 2000 and earlier days, I often found myself resorting to a server reboot to get control of my database server.

As this has become an emergency method of connecting to an overloaded SQL Server, there may occassionally be collissions with other system administrators, especially in a team. In this article, we'll look at determining who is connecting to your instance with the DAC.

Making the Connection

When connecting with the DAC, one can use a couple method. Either an SSMS window or SQLCMD.EXE can be used. With the former, you would prefix the server name with ADMIN:, as shown in Fig. 1. If you use SQLCMD, you can use the ADMIN: prefix or use the -A switch.

Fig. 1 - Connecting with SSMS

Once you've connected, your connection looks like any other connection from SQLCMD. If you are connected with SSMS, you will see the ADMIN: prefix in the lower right corner of SSMS (shown in Fig. 2).

Fig. 2 - SSMS Reports the DAC connection

Am I Using the DAC?

If you query the sys.endpoints DMV, you will see an endpoint created for the DAC. The name will be "Dedicated Admin Connection" and you will see which protocol is in use as well as the state. There is an is_admin_endpoint column as well. You can see this query on my SQL Server 2014 instance in Fig. 3.

Fig. 3 - sys.endpoints

If this is a T-SQL endpoint, there will also be a row in the sys.tcp_endpoints DMV as well.

With this information, I can start to determine who is connecting through this endpoint. Since each connection uses an endpoint, I can join to this table to find out which connection is using the DAC. The sys.dm_exec_sessions DMV contains a column, endpoint_id, that is useful to join back to this table. With this information, I can write a query such as this:

SELECT s.session_id ,
       e.name AS Endpoint ,
       s.login_name ,
       s.nt_domain ,
       s.nt_user_name ,
       s.login_time ,
       s.host_name ,
       s.program_name 
 FROM sys.dm_exec_sessions s
 INNER JOIN sys.endpoints e
 ON e.endpoint_id = s.endpoint_id 

The results from this are shown in Fig. 4 for an instance that doesn't have a DAC connection in use.

Fig. 4 - Finding who's connected and how

If I run this on an instance that has a DAC connection, I'll see this. Note that I'm not executing this from the DAC connection. Check the connection info in the status bat at the bottom.

Fig. 5 - Query an instance with the DAC connected

As you can see, SPID 51 is using the DAC, which is the SPID I connected with above.

There is a column, is_admin_endpoint, in sys.endpoints, that I can use to filter a busy server. If I add this to my query:

WHERE e.is_admin_endpoint = 1  

I will then see these results.

Fig. 6 - Just the DAC

This can be useful if I am trying to use the DAC and can't connect. If I can make a regular connection, I might find that another administrator has beaten me to the server and is already working on the problem.

Conclusion

In general, the DAC should be left open on your instances and not used. If you have used it in an emergency, it's possible that you might reconnect to that instance as a part of your daily work and use the DAC. If someone else had the need to connect in an emergency, they wouldn't be able to since only one connection is allowed. This is the type of simple mistake that could occur to anyone.

If you have a monitoring system, such as SQL Monitor, you might want to enable a check on a regular basis to ensure that no one is using the DAC. The query above could be easily modified to return a count of users. If this is 1, administrators should be alerted that the DAC is in use and might not be available for others. Unless you are in a crisis situation, the count should always be 0.

By default the DAC is only available from the server itself. If you want to allow this from other machines, you can enable remote administrator connections. We have a short article on the DAC that might help you.

References

• Diagnostic Connection for Database Administrators - https://docs.microsoft.com/en-us/sql/2014/database-engine/configure-windows/diagnostic-connection-for-database-administrators?view=sql-server-2017
• sys.endpoints - https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-endpoints-transact-sql?view=sql-server-2017
• sys.tcp_endpoints - https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tcp-endpoints-transact-sql?view=sql-server-2017
• sys.dm_exec_sessions - https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql?view=sql-server-2017