SQLStarters

Dedicated Admin Connection is one of the easy-to-forget features in SQL Server that can really save your day. DAC (no relation to Data-Tier Applications, just shares the acronym), as it’s often called, is the way you can try to access a SQL Server instance that is in such a bad shape, that no normal connection is available. This can be due to resource exhaustion or if you happened to create a slightly wonky logon trigger. In this post we’ll look at how you enable Dedicated Admin Connection (for remote users) and how you connect to SQL Server using the DAC.

About Dedicated Admin Connection

DAC is essentially only useful for troubleshooting SQL Server instances gone bad. By default DAC is only available if you use it locally, f.ex. by remoting to the box and using SQLCMD utility. Naturally this is bit limiting, so you should really enable the remote access first thing after setting up a new SQL Server box. As for the internal bits, to do it’s best to guarantee connectivity, DAC is actually running under its own scheduler to provide dedicated resources for it. In fact, if you run the following query:

SELECT DOS.scheduler_id
      ,DOS.status
      ,DOS.is_online
FROM sys.dm_os_schedulers AS DOS;

You should get the similar looking list of available schedulers. The schedulers with the ID above 1048576 are always reserved for internal use of SQL Server, such as the DAC, in this case. Everything below that is for running the normal user queries.

Enabling and using DAC

As mentioned already, to use DAC remotely you need to enable that. Easiest and quickest way is usually to do it with sp_configure system Stored Procedure. Run the following query to enable it.

sp_configure 'remote admin connections', 1;
GO
RECONFIGURE
GO

After the remote connections have been enabled, there are few ways to connect through it.

Connecting with Management Studio

While it is possible to connect using Management Studio, but there is one gotcha involved in the process. But before going there, let’s look at how to connect first. When you want to open the Admin Connection with Management Studio, you’ll do it by  defining admin: prefix for the server name.

And now it’s time for that gotcha. The issue with Management Studio comes from the limitation of DAC, where you can only have single connection to SQL Server instance through it. By default Management Studio connects to Object Browser, so you’re very likely to get a following error.

The workaround is actually quite simple, you just need to connect somewhere (maybe a local instance, if you have one) and open up a New Query, and then change the connection to where ever you want to connect through DAC. If you didn’t have Object Browser connection open when you start a New Query, you’ll be prompted for the credentials and server name. The way you can change the existing connection, is by right clicking on the Query window and selecting Connection and Change Connection.

 Connecting with SQLCMD

Using SQLCMD tends to be bit tedious, especially if you need to run lot of queries to analyze server issues. However there can be times when it is all you got, so it’s good to know how you can use SQLCMD utility to connect into instance using DAC. To use DAC with SQLCMD, simply use -A parameter like this:

And there you go!

Limitations when using DAC

While the idea of having your own set of dedicated resources sounds great, there are some restrictions and limitations on Dedicated Admin Connections that make it only usable for troubleshooting. Some of these limitations include the following:

• Only single Dedicated Admin Connection available
• Only members of sysadmin role can connect
• No parallelism for running queries
• Only limited resources are guaranteed for the DAC queries
• It is highly recommended not to run heavy DBCC commands (CHECKDB, REINDEX) when connecting with DAC

A more complete list of limitations and restrictions can be found from Microsoft Documentation.

Finally

While Microsoft has done it’s best with Dedicated Admin Connection to provide you a way to connect in almost all circumstances, there are times when it can still fail. However if nothing else works, it still your best (and usually only) option.

Thank you for reading the post, and enjoy the remaining summer!