Logging in with DAC in SSMS

Have you heard of SQL Server’s dedicated administrator connection? The dedicated administrator connection (DAC) can come in handy in an emergency scenario so you should have it enabled and know how to use it…just in case. I haven’t needed it too often in my career but it was helpful in instances when SQL Server wasn’t being very responsive. If there are problems connecting to a server, the DAC can be used to connect and troubleshoot issues.

Enabling DAC for Remote Connections

You’ll want to make sure DAC is enabled for remote connections so that it’s easier to get to your SQL Server in case of an issue. There are a few ways to enable DAC for remote connections. You can either enable DAC by running the following script:

sp_configure 'remote admin connections', 1
GO
RECONFIGURE
GO

Or you can enable in the SSMS GUI. In SSMS, right click your server connection and select Facets:

Click into the Facet dropdown and select Surface Area Configuration at the bottom of the list:

Facet properties will include RemoteDacEnabled to set as True/False:

Logging in with DAC

Now that we have the DAC enabled, let’s take a look at how to take advantage of this connection. You can login with SSMS (which we’ll focus on in this post) or SQLCMD. To login with the DAC in SSMS, you’ll want to add ADMIN to your server name. For my CALLIHAN server that will be:

There’s a little more to the login than just adding ADMIN, though. You can expect to see the error below if you open up SSMS and try logging with ADMIN in your server name:

The trick here is to open SSMS, select Cancel on the initial “Connect to Server” window, select “New Query” to bring up the “Connect to Server” screen, and then login with ADMIN in your server name. If a query window opens and you see ADMIN in your connection, you’ll know you’ve logged in successfully:

In Case of Emergency, Use DAC

If you’re blowing out candles, you don’t break the glass to pull out a fire extinguisher. In a similar way, the DAC is not an appropriate tool outside of emergency situations. It only has the resources for your basic emergency troubleshooting. One example of this is that the DAC won’t run any queries parallel. If the SQL Server is already under pressure, the DAC will do its best to not make your problems any worse.

Thanks for reading!