Dedicated Administrator Connection (DAC)

SQL Server Dedicated Admin Connection (DAC) allows database administrator to connect to SQL Server when it is in emergency. This diagnostic connection allows us to execute diagnostic queries and troubleshoot problems even when SQL Server isn't responding to standard connection requests. By default, the connection is only allowed from a local client running on the server. To guarantee resource available for DAC, only one DAC is allowed per instance of SQL Server. If a DAC connection is already active, any new DAC request is denied with error. Remote connections are disallowed unless being configured by using sp_configure 'remote admin connections option. SQL Server listens for the DAC on TCP port 1434 if available or a TCP port dynamically assigned upon server startup. The error log contains the port number the DAC is listening on. On Failover Cluster Instance (FCI), the DAC will be off by default, but Microsoft recommends enabling remote admin connections on FCI.

Below screenshot shows the mssql error log entry indicating the DAC port of a default instance, most likely TCP 1434.

If there are multiple instances, any subsequent named instances will get a dynamically allocated port in the range 49152 to 65535 as their DAC port.

DAC are not supported via SSMS as it establishes multiple connections by design.

Instead, you can open a new query window in SSMS by pressing the "New Query" button, specifying DAC by ADMIN:<ServerName>.

Then you can run below query to confirm you are in a DAC connection:

SELECT session_id, program_name, P.name AS endpoint_name, is_admin_endpoint

FROM sys.dm_exec_sessions S JOIN sys.tcp_endpoints P ON S.endpoint_id = P.endpoint_id;

If you try to open one more DAC connection, you will get the following error.

For FCI, run the follow SQL command to enable remote DAC:

EXEC sp_configure 'remote admin connections', 1;

GO

RECONFIGURE;

GO
It will take effect immediately.