Today’s post is part of the series that I am running under the title "Under Appreciated Features of SQL Server". The series has been inspired by Andy Warren’s editorial on SQLServerCentral.com.

How often have you faced a situation wherein while the host operating system is perfectly operational, but the SQL Server simply fails to respond to your connection requests? Starting SQL Server 2005, Microsoft provides a “reserve” dedicated administrator connection (DAC) – which is a special diagnostic connection for administrators when standard connections to the server are not possible.

Today, I will share with you some of the important points that I had captured when I first learnt about it in SQL Server 2005.

Connecting to Microsoft SQL Server using DAC

There can be only one connection to the SQL Server under dedicated administration – this is primarily to conserve the minimal resources which have been kept aside for these troubleshooting exercises. This means that it is always recommended to use DAC using the sqlcmd mode rather than the SQL Server Management Studio (where one may open up other connections accidentally – refer my post The multiple connections of SSMS to learn how).

Connecting via SQL Server Management Studio

In order to connect to a Microsoft SQL Server under the dedicated admin connection using SQL Server Management Studio, all that needs to be done is:

1. In the SQL Server Management Studio, click on “Database Engine Query”
2. Add the “ADMIN:” before the server name
3. Use the credentials of a user who is a member of the sysadmin fixed server role, and then click “Connect”

Connecting via SQLCMD

As I mentioned earlier, connecting using DAC is slightly tricky when using SQL Server Management Studio because the management studio can open multiple connections to the SQL Server if not used with care, and that would mean that our one DAC connection might be used up somewhere else accidentally.

A better alternate is to connect using DAC via the SQLCMD. To do so, the SQLCMD statement would look like:

sqlcmd  -S VPCW2K8DENALI -E -A -d master

The –A parameter indicates our intention to use the dedicated administrator connection.

What port does DAC use?

The reason an administrator would want to use DAC is because the SQL Server doesn’t respond to regular queries. Which also means that SQL Server is somehow not responding to the regular port that is used for communicating to SQL Server (typically, 1433). Therefore, DAC should have been implemented to use a different port. And that is exactly what has been done – with the additional security feature of the port changing every time SQL Server restarts.

Whenever Microsoft SQL Server restarts, an entry is added to the SQL Server error log indicating the port# in use by DAC until the next restart of Microsoft SQL Server.

Can I connect using DAC from anywhere?

Remote administration is a great thing, and mandatory in the days of virtualization and data centers. However, this also makes us all inclined to reject a feature if it cannot be used remotely. Similar is the case with DAC.

By default, DAC can only be used on the machine running the SQL Server instance. To run DAC from a remote machine, run the following advanced configuration option against your server:

/****************************************************
             !!!WARNING!!!
This script will modify the security configuration
of your SQL Server. Please inform your administrator
before use.

THIS SCRIPT IS SUPPLIED "AS-IS" AND WITHOUT
WARRANTY FOR DEMONSTRATION PURPOSES ONLY.
****************************************************/
--Enable display of advanced options
sp_configure 'show advanced options',1
RECONFIGURE
GO
--Turn ON Remote Admin Connections (DAC)
sp_configure 'remote admin connections',1
RECONFIGURE
GO
--Disable display of advanced options
sp_configure 'show advanced options',0
RECONFIGURE
GO

DAC and SQL Server Express Editions

To the best of my knowledge, DAC is not available by default on SQL Server Express Editions. Per Books On-Line (http://msdn.microsoft.com/en-us/library/ms188396.aspx) one can start SQL Server using trace flag 7806 (add –T7806 to the startup parameters) to enable DAC on express editions, but I have not given it a try myself.

Words of caution:

Dedicated Admin Connection, is a diagnostic and troubleshooting tool. It is not a security feature or replacement for administrative privileges, and hence must not be accessible to all. At the end of the day, it is a backdoor into the SQL Server instance, and therefore, must be used with extreme caution.

Also, it is to be kept in mind that while DAC allows you to recover from most scenarios, it may not work every time, especially, when the SQL Server is heavily constrained on the resources that can be spared for the DAC.

A good reason to have master as the default database

By the way, we may have a practice of assigning a user database as the default database for a login. If for some reason, this database is offline or not available, we will land up in an error – 4060. Because master is guaranteed to be available if the SQL Server instance is running, it is always recommended to have master as the default database for users belonging to the sysadmin role.

Until we meet next time,

Be courteous. Drive responsibly.