What is the DAC?

The Dedicated Admin Connection, Commonly called the DAC is used to manage SQL Server when a regular connection wont succeed.  Here’s what SQL Books Online (BOL) has to say about the DAC “This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests.”

DAC Errors

Occasionally, while troubleshooting SQL servers in a large environment, especially one thats managed from many different geographic locations you could come up with this error, if more than 1 person is using the DAC.  It should also be noted this only happens if you have remote DAC enabled in your environment

Error 17810

Could not connect because the maximum number of ’1′ dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process. [CLIENT: 127.0.0.1]

Since I could still connect with a regular connection currently, I set out looking for a query to determine who was using the DAC connection.  I whipped this up, and since I couldnt find anything in search, I thought id blog it

select conn.session_id, sess.login_name, sess.nt_domain, sess.nt_user_name, conn.connect_time, conn.last_read, conn.last_write, sess.host_name, conn.client_net_address
    from sys.dm_exec_connections conn
    join sys.endpoints edp
        on conn.endpoint_id = edp.endpoint_id
    join sys.dm_exec_sessions sess
        on sess.session_id = conn.session_id   
    where edp.is_admin_endpoint = 1

This should return everything you need to know about who is using your DAC connection so you can ask them to disconnect, or KILL their connection.