DBA's Guide to SQL Server Dedicated Admin Connection

24. August 2025 Mehedi SQL Server , SQL Server Monitoring Comments (0)

Did you ever face any situation where your SQL Server completely stuck in the middle? The CPU rocketed to the sky and SSMS not responding. You cannot execute any query. What did you do to overcome this critical moment? Did you restart the SQL Server? You are not alone. Many DBAs unaware that there is a lifeline in this scenario and they could still access to their SQL Servers. In this article, we will walk through how to enable, configure and access of this lifeline. Let's start.

Figure-1: Dedicated administrator connection

Dedicated Admin Connection

In critical time when you cannot connect with SQL Server using standard connections due to high CPU pressure, still SQL Server reserves a diagnostic connection to access SQL Server to execute diagnostic queries and troubleshoot problems. This diagnostic connection is called dedicated administrator connection (DAC). By default, it is configured in local machine in port 1434.

Remote Dedicated Admin Connection

DAC and remote dedicated administrator connection are same. When you access SQL Server from a remote machine using DAC, it is called remote dedicated administrator connection. Though, DAC is open by default, you need to configure  the remote dedicated administrator connection. For this configuration:

  • Open the 1434 port in the firewall and ensure that you can telnet it from remote machine.
  • Run the below queries for DAC configuration.
-- Check for current settings
SELECT *
FROM sys.configurations
WHERE name = 'remote admin connections'
GO

-- Configure
EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO

Connect to DAC

Connecting from SSMS

From SSMS, you can connect with DAC from query window. Right click on a query window, then go to Connect from Connection option (Figure-2). Now, type "Admin:" before server instance and login to your server (Figure-3). Remember, you cannot login to DAC from Object Explorer. It will show error.

Figure-2: Connect with DAC from Query Window

Figure-3: Connect with DAC

Command Prompt

You can also connect with DAC from command prompt. Just type below command in command prompt.

sqlcmd -S {Servername} -U {username} -P {password} -d {databasename} -A

How It Works

For DAC, SQL Server reserves a special scheduler which has one thread for processing requests. This is the lifebuoy that SQL Server keeps for you. Whenever a DAC connection request arrives, SQL Server throws this lifebuoy to rescue you. So that you can execute the emergency troubleshot and maintenance queries.

Who is using DAC

At a time, only one user can access DAC. It will throw an error if another person wants to connect it. Execute below query to find out who is using your DAC.

SELECT CASE WHEN es.session_id= @@SPID THEN 'Here I catch you!' ELSE es.host_name END AS Who_is_running_DAC,
es.original_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'

Important Notes

When you are using DAC, remember that:

  • Only one sysadmin user is allowed to access DAC at a time.
  • SQL Server dedicates one thread thread for DAC. There is no parallelism for queries running on the DAC. It is not designed for high performance. Run simple and quick queries using the DAC.
  • Don't run any regular maintenance task using DAC.
  • Use DAC when you really need it and make sure you disconnect it after using.
  • Don't execute BACKUP/RESTORE using DAC as parallel queries or commands are prohibited. It will show error.
  • Don't run resource-intensive queries that can block other queries through DAC. To prevent DAC session getting blocked, lower the isolation level to READ UNCOMMITTED and set the LOCK_TIMEOUT value to a smaller value like 2,000ms.
  • Microsoft recommends to avoid the following commands in DAC session:
    • Dynamic management views like sys.dm_tran_locks, sys.dm_os_memory_cache_counters,sys.dm_exec_requests, sys.dm_exec_sessions for basic diagnostics purpose.
    • Querying catalog views.
    • DBCC commands like DBCC FREEPROCCACHE, DBCC FREESYSTEMCACHE, DBCC DROPCLEANBUFFERS, DBCC SQLPERF, DBCC CHECKDB, DBCC DBREINDEX, DBCC SHRINKDATABASE.
    • KILL command as it might be failed.

Final Words

Dedicated administrator connection works as a lifeline line in a drowning SQL Server. Check you can access DAC from remote machine. DAC is the great option in critical moment. Use it carefully.

References

Going Further

If SQL Server is your thing and you enjoy learning real-world tips, tricks, and performance hacks—you are going to love my training sessions too! 

Need results fast? I am also available for 1-on-1 consultancy to help you troubleshoot and fix your database performance issues.

Let’s make your SQL Server to take your business Challenge!

For any queries, mail to mamehedi.hasan[at]gmail.com.

Tags:

Add comment