SQL Server DAC: The emergency entrance when nobody can log in any more
The application hangs, and the login in Management Studio runs into a timeout. For exactly this moment, SQL Server keeps a separate entrance ready: the Dedicated Admin Connection, DAC for short. In the lab, we used it to find the cause of a standstill within seconds and end it. For that to work in a real incident, though, the DAC has to be set up and tested beforehand.
In short
- The DAC is a reserved entrance for
sysadmin. SQL Server keeps a dedicated scheduler and dedicated resources for it. It often still works when normal logins fail, for example because all workers are in use. - There is exactly one DAC per instance. If you leave it open, you lock out the next person. So close it right after the diagnosis.
- By default, it works only locally on the server. To reach it from an admin machine, set
remote admin connectionsto1, no restart needed. The port is in the error log; for a default instance it is usually TCP 1434. - Connecting: in SSMS through a query window with
ADMIN:Server, withsqlcmd -Aon Windows, and on Linux or macOS directly throughtcp:Server,1434. Always usemasteras the target database. - In an incident, keep it short: find the head of the blocking chain, check the worker situation, act precisely, close the connection. Query 2 contains exactly these three steps.
What the DAC is
SQL Server distributes work across schedulers, and every request needs a worker thread. If all workers are tied up, for example by many sessions waiting for the same lock, even a new login can't get one. It runs into a timeout before a single query has been sent.
For this case, SQL Server reserves the DAC: its own endpoint, its own scheduler and a small, fixed quota of resources. Microsoft describes it as a diagnostic connection for members of the sysadmin server role (Diagnostic connection for database administrators).
This sets its limits:
- One connection per instance. A second attempt is rejected as long as the first one is open.
- Meant for short queries. Look at sessions, requests, locks and waits, and intervene precisely. There are no parallel plans on the DAC, and
BACKUPorRESTOREdon't belong there. - No guarantee. Even the DAC can fail under extreme resource pressure. In the lab, this happened once under load; more on that below.
- Only with a valid
sysadminlogin. If you don't have one, you need another way, such as starting in single-user mode (Microsoft Learn).
Preparing in normal operation
The DAC only helps if you know how to reach it when an incident hits. Three steps are enough, and all of them belong in quiet times.
1. Find the DAC port
At startup, SQL Server writes the port to the error log. In the lab it looked like this:
Dedicated admin connection support was established for listening locally on port 1434.For a default instance, it is usually TCP 1434. For named instances, or if 1434 is taken, SQL Server assigns a different port. Don't mix this up: the SQL Server Browser listens on UDP 1434 and resolves instance names. If you know the DAC's TCP port, you connect directly and don't need the Browser.
Query 1 reads the error log entry, shows the setting for remote DAC and whether the DAC is currently in use.
/* PSG · DAC · Query 1: Is the DAC prepared? Run it during normal operation on an ordinary connection. Shows the remote DAC setting, the port the DAC listens on (from the current error log), and whether the DAC is in use right now. Reads only. Changes nothing. SQL Server 2017 and later, on Windows and Linux. Permissions: sysadmin (xp_readerrorlog; the DAC itself is open to sysadmin only anyway). If the error log was cycled since startup (sp_cycle_errorlog), the port line is missing: then read the older logs with 1, 2, ... instead of 0. OPTION (MAXDOP 1): the diagnostic should not take parallel workers. */SET NOCOUNT ON;DECLARE @log TABLE (log_time datetime, source nvarchar(50), text nvarchar(4000));INSERT INTO @log EXEC sys.xp_readerrorlog 0, 1, N'Dedicated admin connection'; SELECT CAST(c.value_in_use AS int) AS remote_dac, CASE CAST(c.value_in_use AS int) WHEN 1 THEN N'reachable from other machines' ELSE N'local on the server only' END AS meaning, (SELECT TOP (1) l.text FROM @log AS l ORDER BY l.log_time DESC) AS errorlog_entry, (SELECT COUNT(*) FROM sys.dm_exec_sessions AS s JOIN sys.endpoints AS e ON e.endpoint_id = s.endpoint_id WHERE e.name = N'Dedicated Admin Connection') AS dac_in_use, SERVERPROPERTY('InstanceName') AS instanceFROM sys.configurations AS cWHERE c.name = N'remote admin connections'OPTION (MAXDOP 1);2. Test locally
With a sysadmin account, open a DAC directly on the server, specify master as the database, run Query 2 (below) and close the connection. That proves that account, port and client fit together.
3. Enable remote DAC if needed
By default, SQL Server accepts the DAC only over loopback, that is, from the server itself. If you want to work from an admin machine during an incident, for example because even logging on to the server hangs, enable remote access:
EXEC sys.sp_configure N'remote admin connections', 1;RECONFIGURE; SELECT value, value_in_useFROM sys.configurationsWHERE name = N'remote admin connections';In the lab, this took effect immediately and without restarting the instance. After that, the DAC's TCP port must be reachable from the intended admin machines, so it has to be opened in the firewall. Then test from the admin machine and close the connection. With 0 instead of 1, access is restricted to the server again.
Connecting to the DAC
Server name in the query window’s connect dialog
ADMIN:SQLSRV-01- Open a new query window (Database Engine Query), not Object Explorer.
- Ideally a separate SSMS instance with IntelliSense turned off, so that no second connection takes the only seat.
- Under Options, Connection Properties, enter
masteras the database. - Authentication: Windows Authentication. The signed-in account must be
sysadmin. - Close it right after the diagnosis: there is only one DAC per instance.
The command never contains a password.
- SQL Server Management Studio: Not through Object Explorer, but through a new query window (Database Engine Query) with the server name
ADMIN:SQLSRV-01orADMIN:SQLSRV-01\INSTANCE. Microsoft recommends a separate SSMS instance with IntelliSense turned off for this, so that no second connection occupies the only slot. Choosemasteras the database. sqlcmdon Windows:sqlcmd -A -S SQLSRV-01 -d master, for a named instance-S SQLSRV-01\INSTANCE. The-Aswitch stands for the DAC.sqlcmdon Linux and macOS: The ODBCsqlcmdfrom mssql-tools doesn't know-Athere. In the lab, it reportedUnknown Option. Address the port directly instead:sqlcmd -S tcp:SQLSRV-01,1434 -d master -U <Login>. If the server only has a self-signed certificate, ODBC 18 aborts the connection withcertificate verify failed, on the DAC just as on an ordinary connection. Then-C(trust the certificate) helps; a trusted certificate on the server is the better fix.- Go
sqlcmd(Microsoft's newsqlcmd) knows-A, on Linux too. In the lab,sqlcmd -A -S SQLSRV-01without a port landed on TCP 1434, andtcp:SQLSRV-01,1434worked with and without-A. go-sqlcmd prompts for the password only in a real terminal. In a script without a terminal it aborted withliner: function not supported in this terminal. There it reads the environment variableSQLCMDPASSWORD.
Part 1 of Query 2 shows whether you really landed on the DAC: the endpoint is called Dedicated Admin Connection, and the scheduler of your own request has the status VISIBLE ONLINE (DAC). In the lab, it looked like this:
endpoint port scheduler
---------------------------- ----- ---------------------
Dedicated Admin Connection 1434 VISIBLE ONLINE (DAC)An ordinary connection, by contrast, shows TSQL Default TCP, port 1433 and a normal scheduler.
In an incident: diagnose briefly, act precisely
On the DAC, every query counts. Query 2 bundles three short looks: your own endpoint, the heads of the blocking chains and the worker situation.
/* PSG · DAC · Query 2: Situation report over the DAC In an incident, run it over the DAC in master. Three short result sets: (1) Am I really on the DAC? (2) Who is at the head of a blocking chain, even if asleep? (3) Is work waiting for workers (THREADPOOL, work_queue_count)? Reads only. Changes nothing. SQL Server 2017 and later. Permissions: sysadmin (a prerequisite for the DAC). The queries are deliberately kept small: the DAC has only limited resources. Close the connection afterwards. OPTION (MAXDOP 1): parallel plans are not available on the DAC anyway. */SET NOCOUNT ON; -- (1) Own endpoint: expect 'Dedicated Admin Connection' and a scheduler 'VISIBLE ONLINE (DAC)'SELECT e.name AS endpoint, c.local_tcp_port AS port, sch.status AS schedulerFROM sys.dm_exec_connections AS cJOIN sys.endpoints AS e ON e.endpoint_id = c.endpoint_idJOIN sys.dm_exec_requests AS r ON r.session_id = c.session_idJOIN sys.dm_os_schedulers AS sch ON sch.scheduler_id = r.scheduler_idWHERE c.session_id = @@SPIDOPTION (MAXDOP 1); -- (2) Head of the blocking chains: sessions that block others but are not waiting themselvesSELECT TOP (10) s.session_id AS session_id, s.status, s.open_transaction_count AS open_transactions, DATEDIFF(SECOND, s.last_request_end_time, SYSDATETIME()) AS seconds_since_last_request, s.login_name AS login_name, s.host_name AS host_name, s.program_name AS program_name, (SELECT COUNT(*) FROM sys.dm_exec_requests AS w WHERE w.blocking_session_id = s.session_id) AS blocked_directlyFROM sys.dm_exec_sessions AS sWHERE EXISTS (SELECT 1 FROM sys.dm_exec_requests AS w WHERE w.blocking_session_id = s.session_id) AND NOT EXISTS (SELECT 1 FROM sys.dm_exec_requests AS q WHERE q.session_id = s.session_id AND q.blocking_session_id <> 0)ORDER BY blocked_directly DESCOPTION (MAXDOP 1); -- (3) Worker situation: tasks waiting for a worker, and work in the scheduler queuesSELECT (SELECT COUNT(*) FROM sys.dm_os_waiting_tasks WHERE wait_type = N'THREADPOOL') AS threadpool_tasks, SUM(sch.work_queue_count) AS work_without_worker, SUM(sch.current_workers_count) AS workers_current, (SELECT max_workers_count FROM sys.dm_os_sys_info) AS workers_maxFROM sys.dm_os_schedulers AS schWHERE sch.status = N'VISIBLE ONLINE'OPTION (MAXDOP 1);How to read the result:
- Part 2,
status = sleepingwithopen_transactions = 1: The session isn't doing anything right now, but it holds an open transaction and therefore its locks. Typical causes are an application that sent neitherCOMMITnorROLLBACKafter an error, or a forgotten query window.seconds_since_last_requestshows how long it has been like this. Such sessions don't appear insys.dm_exec_requestsat all, because they have no request. That is why part 2 queriessys.dm_exec_sessions. - Part 2,
blocked_directly: counts only the sessions waiting directly on this head. When several writers wait for the same row, they often form a chain: the first waits on the head, the next ones on the first. The column then shows1even though many are affected. - Part 3,
threadpool_tasks > 0orwork_without_worker > 0: Work is waiting for a worker. Together withworkers_currentclose toworkers_max, this is worker starvation. It explains why new logins fail. The cause is usually not the worker starvation itself but whatever ties up the workers, often a blocking chain.
What happened in the lab
We recreated a standstill that happens in practice in this or a similar form: one open transaction, many waiters, no workers left.
6 · Reachable again All 160 writers complete: 160 of 160 done. A regular login works again.
Time intervals schematic. Numbers from the PSG lab with SQL Server 2025, snapshots; session id 58 is an example.
- One session changes a row and leaves the transaction open. It sleeps afterwards.
- 160 writers want to change the same row and wait for the lock. For the test, the instance has 128 workers. Each waiter ties one up.
- A new, ordinary login runs into a timeout. SQL Server doesn't even answer the connection setup (prelogin) any more.
- A freshly opened DAC shows the picture immediately: 76 lock waits in the test database, 12 tasks waiting for a worker (
THREADPOOL), and at the head of the chain a sleeping session with an open transaction. - After checking the session number, a
KILLover the same DAC ends the blocker. Its change is rolled back. - All 160 writers run through, and an ordinary login succeeds again.
The numbers in step 4 are snapshots. A second finding from a preliminary run is just as important: after several successful DAC diagnoses under the same load, a further DAC connection attempt failed. The DAC is robust, but not invulnerable. If you have a working DAC in an incident, do the diagnosis and the intervention in this one connection instead of closing and reopening it in between.
Pitfalls
- DAC in use. A second attempt fails. In the lab, the ODBC client reported only an abort during connection setup (
prelogin failure), go-sqlcmdconnection reset by peer. The actual cause, error 17810, appeared only in the error log. Anyone who left the DAC open, whether in an SSMS window, a script or a monitoring tool, locks everyone else out. Query 1 shows in the columndac_in_usewhether it is in use right now. - Remote not enabled. With
remote admin connections = 0, the DAC attempt from another machine in the lab ran into a timeout, even though the ordinary login from there worked. go-sqlcmd reportedconnection refused. None of the messages reveals the reason. Even on the server itself, only the loopback address counts as local: using the server's own name instead of127.0.0.1, go-sqlcmd failed the same way in the lab. - No
sysadmin. A login without this role could log in normally in the lab; the DAC rejected it. - Wrong client switch.
-Aworks withsqlcmdon Windows and with go-sqlcmd, not with the ODBCsqlcmdon Linux. The direct port helps there. - Certificate. ODBC 18 encrypts by default and validates the server certificate. With a self-signed certificate, the DAC fails without
-Cwithcertificate verify failed. You want to notice that during the test in normal operations, not during an incident. - Default database unavailable. If the login's default database is offline or locked, the login fails. That is why you should always specify
master. - SQL Server Express. According to Microsoft, the DAC listens there only if the instance was started with trace flag 7806.
For the on-call team
Four items that belong in the operations documentation before the next incident:
- Port: Which TCP port is in the error log, and is it reachable from the admin machine?
- Path: From which machine and with which client does the DAC demonstrably work? When was it last tested?
- Account: Which
sysadminaccount is used, and who can use it in an emergency? - Queries: Where are the two or three queries the team runs first in the DAC?
What the experiment shows and what it doesn't
The DAC is not a substitute for monitoring that reports blocking and worker starvation early. It is the way in when everything else is already closed. For that, it has to be tried out once in normal operation.
Sources
- Microsoft Learn: Diagnostic connection for database administrators, remote admin connections, sqlcmd, max worker threads, sys.dm_os_schedulers
- Microsoft Learn: Connect to SQL Server when system administrators are locked out
- Microsoft: go-sqlcmd v1.10.0, switch
-A
Read on: Blocking and Timeouts · Slow Under Load · Locking, Blocking and ADR · SQL Server on Linux for Windows Admins