It's 2 AM. Your phone is going off. Users can't connect to the application, and when you open SSMS to investigate, the connection spinner just keeps spinning. SQL Server is alive; you can see the process running, but it's too overwhelmed to let you in. You need to get in there and kill something, but you can't get a connection to do it.
This is exactly the scenario the Dedicated Admin Connection DAC was built for. And if you haven't set it up yet, now is the time. Because when you need it, you really need it.
What Is the DAC?
The Dedicated Admin Connection is a special diagnostic connection built into SQL Server that gives a sysadmin a way in even when the server is too stressed to accept regular connections. Think of it as a backdoor that SQL Server keeps open specifically for you in an emergency.
According to Microsoft's documentation, the DAC "allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests."
The way it works under the hood is clever. SQL Server reserves a dedicated scheduler thread specifically for DAC connections. This thread sits there quietly, waiting, reserved; it doesn't get consumed by normal workloads. So when everything else is maxed out, and no new connections are possible, that reserved thread is still there for you.
A few important things to understand about the DAC right off the bat:
- Sysadmin only. Only members of the
sysadmin fixed server role can connect using the DAC.
- One connection at a time. Only one DAC connection is allowed per SQL Server instance. If someone else already has it, you'll get error 17810.
- Local only by default. Out of the box, the DAC only accepts connections from the server itself. Remote DAC requires extra configuration (more on that below).
- No parallelism. Queries on the DAC run single-threaded. Don't try to run a BACKUP or RESTORE, or you'll get error 3637.
When Would You Actually Use It?
Here are the scenarios where the DAC becomes your best friend:
- SQL Server is under extreme resource pressure and is rejecting new connections
- TempDB is full, and SSMS won't connect (sound familiar? I wrote about this exact scenario here)
- A runaway query or blocking chain has locked up the server
- A logon trigger is failing and preventing all connections; the DAC bypasses logon triggers entirely
- You need to kill a session, but can't get a connection to do it
- A misconfigured Resource Governor classifier function has made normal connections unusable
The common thread in all of these: SQL Server is alive and running, but you can't get in the normal way. The DAC is your emergency entrance.
Enabling Remote DAC (Do This Before You Need It)
This is the part most DBAs skip, and then regret. By default, the DAC only works if you're sitting on the server itself (local connections only). In most production environments, you're connecting remotely via SSMS on your workstation. That means you need to enable remote DAC ahead of time.
Run this on each instance you manage:
-- Enable remote DAC connections EXEC sp_configure 'remote admin connections', 1; GO RECONFIGURE; GO
You can verify it's enabled with:
-- Confirm the setting EXEC sp_configure 'remote admin connections'; GO
One important note for clustered environments: Microsoft specifically recommends enabling remote admin connections on clustered systems because the DAC is off by default on clusters, and you may not be able to RDP to the active node in an emergency.
Find the DAC Port
By default, the DAC listens on TCP port 1434. But if that port is unavailable, SQL Server assigns a dynamic port at startup. Always verify the actual port by checking the error log:
-- Find the DAC port in the error log EXEC xp_readerrorlog 0, 1, N'Dedicated admin connection'; GO
Make a note of that port. You'll need it if you're connecting remotely via SQLCMD with an explicit port number.
How to Connect via the DAC
There are two ways to connect: SSMS and sqlcmd. I'll cover both.
Method 1: SSMS
SSMS can connect via DAC, but there's a catch: you can't use Object Explorer for this. Object Explorer opens multiple connections behind the scenes, and the DAC only allows one. You need to use a standalone query window instead.
- Close all existing connections to the instance in SSMS, including Object Explorer.
- From the menu, go to File > New > Database Engine Query.
- In the Server Name field, type
ADMIN:ServerName for a default instance, or
ADMIN:ServerNameInstanceName for a named instance.
- Click Connect.
Once connected, you'll have a query window with that reserved DAC thread. You'll notice that the status bar at the bottom of SSMS shows the connection details. Keep this window open; closing it releases the DAC connection.
Method 2: sqlcmd
The sqlcmd utility has a built-in switch for DAC. This is your go-to when SSMS itself won't open, or you're working from a command prompt on the server.
For a default instance:
sqlcmd -S ADMIN:ServerName -E
For a named instance:
sqlcmd -S ADMIN:ServerNameInstanceName -E
If you need to specify the port explicitly (remote connection with a dynamic DAC port):
sqlcmd -S tcp:ServerName,1434 -E
If the server's default database for your login is offline (error 4060), connect directly to master:
sqlcmd -A -d master -S ServerName -E
What to Do Once You're In
You're connected via DAC. Now what? Remember, this is a single reserved thread with limited resources. Keep your queries simple and targeted. Don't run anything resource-intensive. Microsoft recommends setting your isolation level and lock timeout right away to avoid getting the DAC session itself stuck:
-- Set these first thing after connecting via DAC SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SET LOCK_TIMEOUT 2000; -- 2 seconds GO
Now here are the queries you'll actually reach for:
See What's Running
-- Active 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.wait_type
, r.blocking_session_id
FROM sys.dm_exec_sessions s
LEFT JOIN sys.dm_exec_requests r
ON s.session_id = r.session_id
WHERE s.is_user_process = 1
ORDER BY r.cpu_time DESC;
GOFind Blocking Chains
-- Who is blocking whom
SELECT
blocking_session_id AS blocker,
session_id AS blocked,
wait_type,
wait_time / 1000 AS wait_seconds,
[sql_handle]
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0;
GOCheck Lock Activity
-- Current lock activity
SELECT
resource_type,
resource_database_id,
request_mode,
request_status,
request_session_id
FROM sys.dm_tran_locks
WHERE resource_type != 'DATABASE'
ORDER BY request_session_id;
GOKill the Problem Session
Once you've identified the culprit, you can kill it:
-- Replace 57 with the actual session_id KILL 57; GO
Check Who Is Using the DAC Right Now
Want to confirm your DAC session or check if someone else is already connected?
-- Who is connected via DAC
SELECT
CASE
WHEN es.session_id = @@SPID THEN 'This is you!'
ELSE es.host_name
END AS dac_user,
es.login_name,
es.session_id,
es.login_time,
es.status
FROM sys.endpoints AS ep
JOIN sys.dm_exec_sessions es ON ep.endpoint_id = es.endpoint_id
WHERE ep.name = 'Dedicated Admin Connection';
GO
DAC on Azure SQL and Managed Instance
The DAC isn't just a on-premises feature. It's also supported on Azure SQL Database and Azure SQL Managed Instance, which is especially useful since you can't restart services in Azure.
For Azure SQL Database, you connect to the DAC by including the database name in the connection string:
sqlcmd -S admin:yourserver.database.windows.net -d YourDatabase -U adminlogin -P yourpassword
For Azure SQL Managed Instance, the DAC listens on port 1434. Keep in mind that it does not work over private endpoints that allow only port 1433; you need network access within the VNet.
The Rules of the Road
The DAC is powerful, and like most powerful things, it deserves some respect. Here's what to keep in mind:
- Disconnect when done. Don't hold the DAC connection open longer than you need it. Release it so it's available if someone else needs it.
- No BACKUP or RESTORE. These require parallelism and will fail with error 3637.
- No heavy DBCC commands. Skip CHECKDB over the DAC. Stick to quick DMV queries and targeted KILL commands.
- SQL Server Express needs help. Express Edition doesn't listen on the DAC port by default. You need to start it with trace flag 7806 to enable it.
- One connection only. If a DAC is already active when you try to connect, you'll get error 17810. Check who's in there with the endpoint query above.
Add This to Your Runbook Now
The DAC is one of those things you configure once and hopefully forget about right up until the moment you desperately need it, and you're very glad it's there. My recommendation: add enabling remote DAC to your new instance checklist today. Run the sp_configure command, note the port from the error log, and make sure you know how to connect before the 2 AM phone call happens.
Speaking of which, if you haven't read my post on troubleshooting TempDB log full errors when SSMS won't connect, that's a great companion read. That post is a real-world scenario where the DAC saved the day.
As always, thanks for reading. If you've had a situation where the DAC pulled you out of a crisis, drop a comment below — I'd love to hear the war story.
The post How to Connect to SQL Server When Nothing Else Works – DAC appeared first on GarryBargsley.com.