Quick Scan Report – Remote DAC

What this check looks for

The remote admin connections setting in sys.configurations. When it is 0, the dedicated administrator connection accepts connections only from the server itself. The check is skipped on Amazon RDS.

Why it matters

The dedicated administrator connection is the emergency door into a SQL Server that has stopped answering the front one.

SQL Server reserves a scheduler and a small amount of memory exclusively for it. That means when every worker thread is blocked, when the instance is out of workers, when a runaway query has consumed the memory grants, or when the connection pool is exhausted, the DAC still gets you in and still runs queries. It is how you find what is stuck and kill it, rather than restarting the instance and losing the evidence along with the uptime.

The limitation this check reports: by default, the DAC listens only on the loopback address. To use it you have to be logged on to the server console, through Remote Desktop, or on the machine some other way.

That is precisely the situation where you often cannot be. A server so overloaded that SQL Server has stopped accepting connections is frequently also a server where a Remote Desktop session takes several minutes to establish, or fails outright. In a cloud or a locked down environment, console access may need a separate approval process measured in tens of minutes. In a Windows cluster, the node may be in the middle of failing over.

Enabling remote DAC removes that dependency, and the cost is essentially nothing:

• It does not change performance. The scheduler and memory are already reserved whether the setting is on or off.
• It does not widen access. Only members of the sysadmin fixed server role can use the DAC, and that is not configurable. Enabling remote connections does not grant anybody anything.
• It needs no restart. The change is immediate.
• Only one DAC session can exist at a time, which is unchanged by this setting.

What it does require is a firewall rule, because the DAC listens on a different port. On a default instance that is TCP 1434. On a named instance the port is dynamic and allocated at startup, which is worth knowing before an emergency rather than during one.

The security consideration is honest and small: an additional listening port. Given that the only principals who can authenticate to it are already sysadmin, and that they can already connect on the normal port, the exposure is a port scan finding rather than a privilege one. Restricting the firewall rule to your administrative subnet handles it.

How to confirm it yourself

The setting:

SELECT [name], [value], [value_in_use], [description]
  FROM sys.configurations WITH (NOLOCK)
 WHERE [name] = 'remote admin connections';

Which port the DAC is listening on, which is the piece of information you want written down somewhere findable:

SELECT [port], [is_admin_endpoint], [type_desc], [state_desc], [ip_address]
  FROM sys.dm_tcp_listener_states WITH (NOLOCK)
 WHERE [is_admin_endpoint] = 1;

On a named instance the port is assigned at startup and can change, so check it after every restart, or fix it by configuring a static port.

It is also recorded in the error log at startup:

EXEC xp_readerrorlog 0, 1, N'Dedicated admin connection';

That entry states the address and port it is listening on.

Whether a DAC session is currently in use, since only one is allowed:

SELECT s.[session_id], s.[login_name], s.[host_name], s.[program_name], s.[login_time]
  FROM sys.dm_exec_sessions AS s WITH (NOLOCK)
 INNER JOIN sys.endpoints   AS e WITH (NOLOCK) ON e.[endpoint_id] = s.[endpoint_id]
 WHERE e.[name] = 'Dedicated Admin Connection';

And test that it works now, which is the part people skip and regret:

sqlcmd -S ADMIN:YourServerName -E
sqlcmd -S ADMIN:YourServerName,1434 -E

The ADMIN: prefix is what requests the DAC. From Management Studio, use a new query window with ADMIN:ServerName as the server, and connect before opening Object Explorer, because Object Explorer opens additional connections which the DAC does not permit.

How to fix it

Enable it, open the port, and test it while everything is healthy.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'remote admin connections', 1;
RECONFIGURE;

Immediate, no restart.

Then find the port and open it to your administrative network only:

SELECT [port] FROM sys.dm_tcp_listener_states WHERE [is_admin_endpoint] = 1;

On a default instance this is 1434. Note that the SQL Server Browser service uses UDP 1434, which is a different thing entirely; the DAC uses TCP.

On a named instance, give the DAC a static port if you can, so the firewall rule stays valid across restarts. Otherwise the port changes and the rule you wrote last year no longer matches.

Write down the connection string somewhere your team can reach it during an outage, which means not on the server in question. Something like:

sqlcmd -S ADMIN:SQLPROD01,1434 -E -Q "SELECT TOP 20 session_id, blocking_session_id, wait_type, wait_time FROM sys.dm_exec_requests WHERE blocking_session_id <> 0"

Practice using it. Connect via the DAC today, on a healthy instance, and confirm the firewall rule works from where you would actually be sitting. A DAC that has never been tested is a setting, not a capability.

What to run once you are in, which is worth having prepared:

-- what is blocking
SELECT [session_id], [blocking_session_id], [wait_type], [wait_time], [command]
  FROM sys.dm_exec_requests
 WHERE [blocking_session_id] <> 0;

-- what is consuming memory grants
SELECT [session_id], [requested_memory_kb], [granted_memory_kb], [wait_time_ms]
  FROM sys.dm_exec_query_memory_grants;

-- and the usual answer
KILL 123;

Keep DAC queries simple. The DAC has one scheduler and limited memory, so it is not the place to run a parallel query or a large report. Use it to diagnose and to kill, not to work.

How long it takes

About half an hour, including opening the firewall port and testing a connection.

Related reports

Related checks

Frequently asked questions

Does enabling this create a security risk? Only sysadmin members can use the DAC, and that is not configurable. Enabling remote connections opens a port but grants nobody anything they did not already have. Restrict the firewall rule to your administrative subnet.

Does it cost performance? No. The scheduler and memory are reserved for the DAC whether the setting is on or off.

Why does Management Studio refuse to connect? Object Explorer opens more than one connection, and only one DAC session is permitted. Use a new query window, connect with ADMIN:ServerName, and do not open Object Explorer against it.

What port does it use on a named instance? It is allocated at startup and is not fixed. Read it from sys.dm_tcp_listener_states or the error log, and consider configuring a static port so the firewall rule stays valid.