SQL Server Dedicated Administrator Connection (DAC): what it is, how to use it, and a mini-workshop

Summary

The Dedicated Administrator Connection is a back-door lifeline for SQL Server. When the instance is too stressed to accept normal connections, a sysadmin can still connect over the DAC to run lightweight diagnostics and fix problems. You can connect with sqlcmd -A or by using the ADMIN: prefix in SSMS. By default, only local DAC is allowed; you can enable remote DAC with sp_configure and open the right firewall port. Use DAC sparingly, keep queries simple, and know how to find the DAC port in the error log. Microsoft Learn

---

What is the DAC?

The DAC is a special, low-resource connection reserved for members of the sysadmin role to run diagnostic T-SQL when the server is in trouble or not accepting normal connections. Only one DAC session is allowed per instance. Microsoft Learn

Key points:

• Only sysadmin can use it. One session per instance. If it’s already in use, you get error 17810. Microsoft Learn
• It is meant for quick diagnostics. Avoid heavy queries, backups, restores, or anything parallel. Keep it surgical. Microsoft Learn

How to connect

sqlcmd (most reliable)

sqlcmd -S <server_or_ip> -A -d master -E
# or SQL auth
sqlcmd -S <server_or_ip> -A -d master -U sa -P <StrongPassword>

-A requests the DAC. Connecting to master is recommended so you bypass issues with an offline default database. Microsoft Learn

SSMS

In SSMS use File > New > Database Engine Query, then in Server name type:

ADMIN:<server_name>           -- default instance
ADMIN:<server_name>\<instance> -- named instance

Note you open a new Query window, not Object Explorer. Microsoft Learn

Enabling remote DAC

By default, DAC is only allowed from the server itself (loopback). To allow remote admin connections:

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

This allows DAC from a remote machine. Microsoft Learn

Ports and firewall

• Default instance DAC listens on TCP 1434 if available; named instances can use other ports. Check the SQL Server error log to confirm the actual port. Microsoft Learn+1
• If you allow remote DAC through a firewall, open the DAC port you confirmed in the error log. The Microsoft firewall guidance lists 1434 for the default instance and notes that named instances use other ports. Microsoft Learn

Quick way to read the error log for the DAC note:

EXEC xp_readerrorlog 0, 1, N'Dedicated admin connection';
-- Look for the line showing the listening port

(You will see the DAC port announcement in the error log; Microsoft docs direct you to check the error log for the port.) Microsoft Learn

Resource limits and safe usage

• No parallel plans. Avoid heavy DBCC like CHECKDB under DAC. Prefer quick DMVs, KILL, and small corrective steps. If you must run a query that might block, use READ UNCOMMITTED and a short LOCK_TIMEOUT to avoid getting stuck. Microsoft Learn
• SQL Server Express does not listen on the DAC port unless started with trace flag 7806. Microsoft Learn+1

Azure notes (quick)

• Azure SQL Database supports DAC, but you must include the database name when connecting. Microsoft Learn
• On Azure SQL Managed Instance, DAC listens on 1434 and does not work over private endpoints that only allow 1433; you need network access inside the VNET. Microsoft Learn

Quick scripts you will actually use

Find active sessions and requests (cheap view)

-- sessions and requests
SELECT s.session_id, s.login_name, s.host_name, s.program_name,
       r.status, r.command, r.cpu_time, r.total_elapsed_time, r.blocking_session_id
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
WHERE s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC;

Microsoft Learn+1

See locks and who is blocking whom

SELECT tl.request_session_id AS session_id,
       wt.blocking_session_id,
       tl.resource_type, tl.resource_description, wt.wait_type
FROM sys.dm_tran_locks AS tl
LEFT JOIN sys.dm_os_waiting_tasks AS wt
  ON tl.request_session_id = wt.session_id
ORDER BY tl.request_session_id;

Microsoft Learn

Kill a runaway SPID

-- replace 57 with the SPID you identified
KILL 57;

Microsoft calls out that KILL may not succeed depending on server state. Microsoft Learn

Who is using the DAC right now?

SELECT s.session_id, s.login_name, s.host_name, s.program_name
FROM sys.dm_exec_sessions AS s
JOIN sys.endpoints AS e ON s.endpoint_id = e.endpoint_id
WHERE e.is_admin_endpoint = 1;   -- Dedicated Admin Connection

sys.endpoints exposes is_admin_endpoint so you can detect an existing DAC session. Microsoft Learn

---

Mini-Workshop: learn and test DAC in a safe sandbox

Target: any dev or test instance where you are sysadmin.

1) Prove you can connect locally with DAC

1. On the SQL Server host, open a console and run:

sqlcmd -S 127.0.0.1 -A -d master -E
SELECT @@SERVERNAME AS ServerName, DB_NAME() AS Db;
GO

1. You just used DAC locally. If this fails, check that your account is sysadmin. Microsoft Learn

2) Enable remote DAC and confirm the port

1. Enable remote DAC:

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

Microsoft Learn

2. Find the DAC port:

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

Note the port and, if needed, allow it on your firewall for your admin workstation only. The default instance commonly uses 1434, but you should always confirm in the error log. Microsoft Learn

3) Create a blocking scenario you can fix with DAC

1. Create a small table:

USE tempdb;
IF OBJECT_ID('dbo.DacDemo') IS NOT NULL DROP TABLE dbo.DacDemo;
CREATE TABLE dbo.DacDemo(id int PRIMARY KEY, note nvarchar(100));
INSERT INTO dbo.DacDemo VALUES (1,'hello');

2. In Session A (normal connection), start a transaction and hold a lock:

BEGIN TRAN;
UPDATE dbo.DacDemo SET note = 'hold' WHERE id = 1;
-- Do not commit yet; leave this window open

3. In Session B (normal connection), try to update the same row:

UPDATE dbo.DacDemo SET note = ‘blocked’ WHERE id = 1;
— This should be blocked

4. Now connect via DAC from your admin workstation:

• SSMS: File > New > Database Engine Query, server name ADMIN:<server>
  or sqlcmd -S <server>,<dac_port> -d master -E if you want to target the port directly. Microsoft Learn

5. In the DAC session, identify and clear the blocker:

-- Find the blocking chain
SELECT s.session_id, r.blocking_session_id, r.status, r.command
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r ON s.session_id = r.session_id
WHERE s.is_user_process = 1;

-- Kill the blocker (replace with the blocking SPID from the list)
KILL <blocking_spid>;

Your blocked Session B should finish immediately. Microsoft Learn

6. Clean up:

-- In Session A, if still open:
ROLLBACK; -- or COMMIT if you prefer

4) Try minimal-config rescue + DAC (optional)

If you ever set a bad config and SQL Server will not start cleanly, you can start in minimal configuration and use DAC to repair settings. Microsoft’s doc calls out using sqlcmd with DAC when starting with minimal config. Don’t practice this on production. Microsoft Learn+1

---

Troubleshooting quick hits

• “ADMIN:” in Object Explorer fails: Use a Query window. Object Explorer opens multiple connections and the DAC allows only one. Microsoft Learn
• Can’t connect remotely: Enable remote admin connections and verify firewall rules for the DAC port shown in the error log. Microsoft Learn+1
• SQL Server Express: add startup trace flag -T7806 to reserve DAC resources. Microsoft Learn

---

Final thoughts

The DAC is your emergency key. Know the connection methods, keep a script bundle of light-touch diagnostics, and enable remote DAC now so you are not scrambling during an outage. Treat it with respect: short queries, fast fixes, and out.

---

References

• Microsoft Learn: Diagnostic connection for database administrators. Details on what DAC is, how to connect in SSMS and sqlcmd, limits, and Azure specifics. Microsoft Learn
• Microsoft Learn: Server configuration: remote admin connections. How to enable remote DAC with sp_configure and default listening behavior. Microsoft Learn
• Microsoft Learn: Configure the Windows Firewall to Allow SQL Server Access. Port list, including DAC port guidance and where to confirm the actual port. Microsoft Learn
• Microsoft Learn: DBCC TRACEON – Trace Flags. Trace flag 7806 enables DAC for SQL Server Express. Microsoft Learn
• Microsoft Learn: Start SQL Server with minimal configuration. When to rely on DAC during minimal-config startups. Microsoft Learn