Enable the Dedicated Admin Connection (DAC) and How to Use it

If you ever find your SQL Server instance runs into some serious performance issues, you might find it’s so bad, that you can’t even connect to the instance. If only you could connect to run your usual troubleshooting queries to find out what’s going wrong and hopefully fix it!

This is where the Dedicated Admin Connection, aka DAC, comes in. This is the special connection on the SQL Server instance reserved only for sysadmins which will still be usable when standard connections aren’t working. It’s a single thread and will allow you to run your common troubleshooting queries and maybe even fix the problem, but only in one query window and only by one login at a time.

Sounds pretty good right? Well, it’s actually already enabled for local connections to non-clustered SQL Server instances, meaning if you have a standalone SQL Server instance and you can remote desktop to the server, you can already manage to use the DAC. But we want to be able to connect from other servers and connect to the DAC on clusters too. So we want it to be enabled for remote connections.

Enable the DAC for remote connections

To enable DAC for remote connections, simply execute this code:

EXECUTE sp_configure N'remote admin connections', N'0';
GO
RECONFIGURE WITH OVERRIDE;
GO

If you prefer using the SSMS GUI for these sort of changes, you’re in luck. In SSMS object explorer, right click the server name and select Facets. Once the View Facets window opens, from the Facet drop down list, select Server Configuration. Now you can scroll down to find RemoteDACEnabled. Change it to True, then click OK.

Often the firewall port used for the remote DAC is TCP 1434, but it can vary depending on your setup and configuration. You will need to create a new firewall rule to allow this connection if your firewall has closed that port. If you don’t know what port the DAC is using on your server or you want to change it to another port, take a look at What Firewall Port does the DAC require.

How to Connect to the DAC

You can connect to the DAC both using SSMS and sqlcmd.exe. To connect using SSMS, you must open a new query window, then change the connection of that query window. When specifying the Server name, enter “admin:” and then the instance name you want to connect to. e.g. admin:myinstance.

It must be in just one query window. If you try connecting in Object Explorer (the little section on the left which lists all your servers and databases etc), it’ll fail when connecting since Object Explorer uses more than one connection – and DAC can only be one connection.

If you prefer using sqlcmd.exe you can simply add an extra -A flag to the command (case sensitive). e.g.

sqlcmd -S {Server name} -U {user name} -P {password} -d {databasename} -A

Someone Using the DAC?

There is a chance that you might find that connecting to the DAC isn’t working, no matter how many times you try. It could be because somebody else is already using the DAC.

The error you get while connecting when somebody else has the DAC is pretty generic and unhelpful. But the SQL error log does log a more descriptive error message. It’ll read a little like this:

Could not connect because the maximum number of '1' dedicated administrator connections already exists. Before a new connection can be made, the existing dedicated administrator connection must be dropped, either by logging off or ending the process.

This message is much more helpful and may look familiar if you regularly perform penetration testing on your SQL Servers. If you want to find out if somebody is using the DAC, and who that person is, then you can run this nifty little query:

SELECT
	 es.session_id
	,es.login_name
	,es.[host_name]
	,es.original_login_name
	,es.login_time
	,es.[status]
FROM sys.endpoints AS e
	INNER JOIN sys.dm_exec_sessions es ON e.endpoint_id = es.endpoint_id
WHERE e.[name] = N'Dedicated Admin Connection'

The output looks like so: