[SOLVED] EXtended Event to track DAC Connections

EXtended Event to track DAC Connections

I am facing a sql server error log :

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

I would like to know if there is some way to create an extended event to track who is trying to connect via DAC

Does someone can help me ?

Solution

You can use the sqlserver.login event, which has a is_dac column. You can use the New Session wizard in SSMS to script this out.

CREATE EVENT SESSION [DacConnections] ON SERVER 
ADD EVENT sqlserver.login(
    SET collect_database_name=(1)
    ACTION(
      sqlserver.client_app_name,
      sqlserver.client_hostname,
      sqlserver.database_name,
      sqlserver.username
    )
    WHERE (is_dac = (1))
)
ADD TARGET package0.ring_buffer(
    SET max_events_limit = (100),
        max_memory = (5120)
)
WITH (STARTUP_STATE = ON);

Do note though that the error is commonly generated by someone using SSMS to open a DAC connection. SSMS by default connects both the Object Explorer and the Query Window separately. You should only open the Query Window, do not connect the Object Explorer. Alternatively use sqlcmd, which only opens one connection.