A dedicated administrator connection (DAC) is a special connection that can be used by administrators to connect to a SQL Server instance, even when the database engine is not running. This can be useful when troubleshooting startup issues, for example.
DAC can be enabled in SQL Server in a few different ways: using the server configuration option, using sp_configure, or using trace flag 7806.
In this article, we’ll take a look at all three methods.
Table of Contents
Method 1: Enable DAC Using Server Configuration Option
The easiest way to enable DAC is to use the server configuration option. To do this, open up SQL Server Management Studio and connect to your SQL Server instance. Then, click on the “server objects” folder and double-click on the “Linked Servers” option.
In the “Linked Servers” dialog box, click on the “New Linked Server…” button.
In the “New Linked Server” dialog box, enter the following information:
Name: Enter a name for your linked server. This can be anything you want.
Provider: Select “SQL Server” from the dropdown list.
Product name: Enter the name of your SQL Server instance.
Data source: Enter the name of your SQL Server instance.
Location: Enter the location of your SQL Server instance. This can be either an IP address or a hostname.
Click on the “Security” tab.
Under the “Security” tab, select the “Be made using this security context” option and enter the following information:
User name: Enter the username of a SQL Server administrator.
Password: Enter the password of the SQL Server administrator.
Click on the “OK” button to save your changes and close the dialog box.
Method 2: Enable DAC Using sp_configure
Alternatively, you can enable DAC by using the sp_configure stored procedure. To do this, open up a query window in SQL Server Management Studio and connect to your SQL Server instance. Then, run the following T-SQL code:
EXEC sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE;
GO
This will enable the “show advanced options” and “remote admin connections” options. Next, run the following T-SQL code to enable DAC:
EXEC sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
Method 3: Enable DAC Using Trace Flag 7806
Another way to enable DAC is to use trace flag 7806. This can be done by starting SQL Server with the -T7806 startup parameter. To do this, open up the SQL Server Configuration Manager and select the “SQL Server Services” option.
In the “SQL Server Services” dialog box, right-click on the “SQL Server” service and select the “Properties” option.
In the “SQL Server Properties” dialog box, click on the “Startup Parameters” tab.
Under the “Startup Parameters” tab, enter -T7806 in the text box and click on the “Add” button.
After you have added the trace flag, click on the “OK” button to save your changes and close the dialog box.
Restart your SQL Server instance for the changes to take effect.
When DAC is enabled, you can connect to your SQL Server instance using the following command:
sqlcmd -S <server_name> -E
Be sure to replace <server_name> with the name of your SQL Server instance.
You will be prompted for the username and password of a SQL Server administrator. Once you have entered the credentials, you will be connected to your SQL Server instance using DAC.
There are two ways to enable DAC in SQL Server: using the sp_configure procedure or using the Server Configuration Manager.
Using sp_configure:
- From the Start menu, open SSMS and connect to the instance of SQL Server that you want to configure.
- In Object Explorer, expand the Management folder, right-click on Services and choose Configure from the context menu. The Service and Application Configuration page opens up.
- In the left pane, click on SQL Server Services. In the right pane, select the instance of SQL Server that you want to configure.
- From the Action menu, choose Properties. The Properties dialog box for the selected service
Conclusion:
A dedicated administrator connection can be enabled in SQL Server in a few different ways: using the server configuration option, using sp_configure, or using trace flag 7806. In this article, we’ve taken a look at all three methods.
