Enabling and using dedicated remote administrator connection (DAC) in SQL Server

Views: 1.432
Reading Time: 4 minutes

Hello people,
Good Morning!

In this post I will demonstrate how to enable / disable the remote Dedicated Administrator Connection (DAC) feature of the SQL Server instance, verify that the feature is active, and how to use it.

Introduction - What is DAC?

Imagine in the following situation: All workers in the instance are in use and no longer allowed to connect to the bank, as you would to analyze what is happening and take action to solve this problem, since you cannot even to connect?

SQL Server - Connection Error

With that in mind, Microsoft has created DAC, a very useful feature for DBA as it allows connection to the instance using a unique and dedicated feature for Database Administrators.

Its operation is quite simple: Once activated, the instance creates the DAC connection at startup and leaves it in standby, waiting for the DBA connection. Even if the instance is overloaded and no workers are available, this connection is already connected to the database and can be used by the DBA.

Remember that this feature can only be used by users with sysadmin permission and only one user can use this connection at a time.

How to tell if remote DAC is active?

To identify if this feature is enabled on the instance, simply use sp sp_configure:

SQL Server - sp_configure remote admin connections

Viewing the DAC Connection through DMV sys.endpoints
SQL Server - DAC DMV Endpoints

How to enable DAC?

I will demonstrate two ways: Using T-SQL and SQL Server Management Studio.

Using Transact-SQL

Using SQL Server Management Studio

Right-click on your instance and select the "Facets" option, as shown in the image below:

SQL Server - Enable 1 DAC

This will open the “View Facets” screen. In the Facets combo, select “Surface Area Configuration” and set the TRUE value to the “RemoteDacEnabled” parameter.
SQL Server - Enable 2 DAC

SQL Server Express and the DAC Connection

By default, SQL Server Express does not have the DAC connection feature by default. However, this can be enabled using a trace flag. This is the third way to enable the DAC connection, which is by using the 7806 trace flag in SQL Server Express initialization parameters. This third option is especially useful for scenarios where you have a problem preventing user login (eg Login Trigger), the DAC connection is not enabled and you are unable to login to the instance.

To enable it, simply open SQL Server Configuration Manager, and view the properties of the instance that you want to enable DAC:

In the instance configuration screen, go to the “Startup Parameters” tab and add the parameter “-T7806”:

After committing the changes, you must restart the SQL Server Express service for the changes to take effect. After this you will see that you can now connect to the instance using the DAC connection.

Connecting on instance using DAC

Using the SQLCMD
To connect to the instance through SQLCMD using DAC, simply use the -A parameter, as shown below:
SQL Server - DAC SQLCMD

Want to know more about SQLCMD, the SQL Server command line utility? Visit this link.

Using SQL Server Management Studio
To connect to the instance through SQL Server Management Studio using DAC, simply use the prefix ADMIN: before the instance name at connection time, as shown below:
SQL Server - DAC Management Studio

SQL Server - DAC Management Studio 2

To identify who is using the DAC connection on the instance:

SQL Server - DAC Remote Connections Who Is Using 2

Important: Because only one DAC connection is allowed at a time, you cannot use it in Object Explorer, only in the New Query screen.

If you are having difficulty using the DAC connection in an environment where the SQL Browser service is disabled, please read this article: SQL Server - How to Connect Using Dedicated Admin Connection (DAC) Connection Without SQL Browser.

Thanks for stopping by and see you next time!

sql, sql server, dac, Dedicated Administrator Connection, Remote Admin Connections, can't connect, dba connection, connection error, login timeout, connection timeout

sql, sql server, dac, Dedicated Administrator Connection, Remote Admin Connections, can't connect, dba connection, connection error, login timeout, connection timeout