SQL Server Remote DAC (Dedicated Administrator Connection)

SQL Server reserves a single thread that can be used when the server is unresponsive or you cant remote desktop into the SQL Server machine. It is intended for EMERGENCY USE ONLY so that the DBA can run troubleshooting queries. This is called the Dedicated Administrator Connection or Remote DAC.

You can enable remote DAC using SSMS or the sp_configure procedure.

sp_configure
Login as the SA user and run the command:

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

Verify that the setting was changed:

exec sp_configure 'remote admin connections';

SSMS
Or you can set the value through SSMS:
Login as the SA user => right-click the instance connection => Facets => select Surface Area Configuration from the Facet drop down=> set RemoteDacEnabled to True => click OK


Most tutorials will tell you to simply append the word “admin” to your connection in SSMS and you will be connected to the DAC. Job done. So let’s try that.

However, when we do that, we get the following “network-related or instance-specific error occurred while establishing a connection to SQL Server” message:

There are still a number of configurations left that are necessary to make the Remote DAC actually work.

The Remote DAC uses a separate port other than the default 1433 used for regular SQL Server connections. This is usually a dynamic TCP port which changes when the SQL Server service is restarted. The quickest way to find the port assigned is to go to the error log which is generated when the SQL Server service is started. You can find file called ERRORLOG at:
Your_instance_location\MSSQL\Log\ERRORLOG

For my SQL Server 2019 installation, it was located at:
D:\SQLSVR2019\MSSQL15.DBACOURSE_DB\MSSQL\Log\ERRORLOG

Open the ERRORLOG file with Notepad and search for ‘Dedicated admin connection’ and you will find a line like the one below:

The log says that the Dedicated Admin Connection is listening remotely on port 49721.
We can test this by using the SQLCMD utility and connecting with port 49721:

We want to change this port number because restarting the SQL Server instance can change the dynamic port number. To change this from using the dynamic port 49721 and use the recommended port 1434, set the value in the Registry. Go to:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.DBACOURSE_DB\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp
(Note: MSSQL15.DBACOURSE_DB is the name of my instance).

Restart the SQL Server service and you will be able to connect with SQLCMD using port 1434:

To connect to the DAC from another PC (remotely), port 1434 must be opened. Add an inbound rule allowing connections to the TCP port 1434.

Now we can connect to the DAC remotely from another machine. We can use either SSMS or a SQLCMD client. For SSMS, Once again, type in the word “admin” in front of the server name and connect. And after making all those configurations, we are still left with the same error message -_-

This is where things get weird.

To not see this particular “network-related” error, select a different method of authentication. So, if you want to connect via SQL Server Authentication, switch to Windows Authentication and vice versa. Try to connect with that authentication method (doesn’t matter if it fails). Next switch back to the authentication method that you actually intend to connect (in my case SQL Server Authentication).

Re-enter the SA username and password and click connect. You should see the following:

Now we have a different error message. Yay… I guess. We do have progress though. The error itself is self-explanatory. SSMS uses more than one thread while the DAC is single threaded. There is a way around this. Next to the New Query button, there is a Database Engine Query window button.

Click it and enter the SA credentials again with the word ADMIN in front of the server name and hit enter. At the bottom of the SSMS window, you will see confirmation that you are connected to the DAC.

If the SQLCMD utility is installed on the client, using the DAC is much more straightforward. Simply connect to the named instance with the -A switch. For example:
Sqlcmd -S PROSVR\DBACOURSE_DB -U sa -P test1234! -A