Dedicated Administrator Connection in SQL Server

As a Database Administrators you may come across a scenario where you are unable to access a SQL Server instance, especially when the CPU or Memory utilization on the server is very high. To allow database administrators to troubleshoot such scenarios, Microsoft introduced the Dedicated Administrator Connection (DAC) in SQL Server 2005; this is a special diagnostic connection for database administrators when standard connections to the SQL Server are not possible. SQL Server will make every possible attempt to successfully connect using DAC feature; however in some extreme conditions it may not be successful. The Dedicated Administrator Connection feature is also available in SQL Server 2008. Database Administrators need to keep it in mind that only one  DAC connection can be established to a SQL Server Instance. Once the connection is established using DAC you can access SQL Server and execute queries to troubleshoot performance issues. In this article you will see how to configure and enable a Remote Dedicated Administrator Connection and you will also see how you can use DAC with SQL Server Management Studio and the SQLCMD command line utility.

Enabling the Remote Dedicated Administrator Connection
By default, the DAC can only be run on the server. A Remote DAC is not possible until it is configured by the database administrator using the sp_configure system stored procedure with the remote admin connections option. To enable a Remote DAC, execute the code below:

USE master
GO
sp_configure 'show advanced options', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/
sp_configure 'remote admin connections', '1'
GO
RECONFIGURE WITH OVERRIDE
GO

In SQL Server 2005 you can also enable remote computes to access a DAC by using the SQL Server Surface Area Configuration Tool. However in SQL Server 2008, the SQL Server Surface Area Configuration Tool is not available.

To access the Area Configuration Tool in SQL Server 2005  select Start | All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Surface Area Configuration



In SQL Server 2005, the Surface Area Configuration screen you need to select is the Surface Area Configuration for Features.  This will open the screen below:



Click on DAC under Database Engine and select the Enable remote DAC option and click OK to save the configuration changes.

TCP/IP Port Used by Dedicated Administrator Connection
The Dedicated Administrator Connection requires a dedicated TCP/IP port which gets assigned dynamically when the Database Engine starts up. By default the DAC listener accepts connections on the local port, i.e., for a default instance of SQL Server, DAC uses TCP/IP port 1434. Once the remote administrator connection is configured then the DAC listener is enabled without requiring a restart of the SQL Server service. You can check the port which was assigned for the DAC in SQL Server error log.