DAC - Dedicated Administrator Connection in SQL Server

DAC [Dedicated Administrator Connection] is a feature added in SQL Server 2005 version. The DAC allows a Database Administrator to access a running SQL instance to troubleshoot problems on the SQL server even when the instance is unresponsive to regular client connections. The database administrator can use this feature to connect to the instance to solve the problem instead of rebooting the SQL Server.

The DAC is available through the SQL Server Management Studio (SSMS) and sqlcmd utility. The connection is only allowed from a client running on the server.

By default this feature is disabled in all SQL Server versions starting from 2005 to the latest version 2014.

Note: SQL Server Browser service must be in running state.

SQL Server listens for the DAC on TCP port 1434 if available or a TCP port dynamically assigned upon Database Engine startup. The error log contains the port number the DAC is listening on as given below.

Dedicated admin connection support was established for listening locally on port 63330.

Enable the DAC using T-SQL command

Use master

GO

sp_configure 'show advanced option' , 1

GO

sp_configure 'remote admin connections', 1

GO

RECONFIGURE

GO

Enable the DAC using SSMS

Right click on SQL Server Properties àFacets àSurface Area Configuration àRemoteDacEnabled -

Change it to True

Connecting to server using DAC through SSMS

Only members of the SQL Server sysadmin role can connect using the DAC.

• In SQL Server Management Studio, with no other DACs open, Go to File à New à click Database Engine Query

   

• In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named TELECOM\SQL2KR2, type ADMIN:TELECOM\SQL2KR2.

• Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect. The connection is made.

Connecting to server using DAC through sqlcmd

The DAC is available and supported through the sqlcmd command-prompt utility using a special administrator switch (-A). You can also connect prefixing admin: to the instance name in the format sqlcmd -Sadmin:<instance_name>. 

Restrictions

Because the DAC available solely for diagnosing SQL server problems in rare situations, there are some restrictions on the connection:

• To guarantee that there are resources available for the connection, only one DAC is allowed per instance of SQL Server. If a DAC connection is already active, any new request to connect through the DAC is denied with the following error.

 A connection was successfully established with the server, but then an error occurred during the login process. 

• The DAC initially attempts to connect to the default database associated with the login. After it is successfully connected, you can connect to the master database. If the default database is offline or otherwise not available, the connection will return error 4060. However, it will succeed if you override the default database to connect to the master database instead using the following command: sqlcmd –A –d master
• If you try to make DAC connection through Object explorer, you’ll get the following error.

Dedicated administrator connections are not supported. 

•          Only limited resources will be available with the DAC. These resources are typically only enough for simple diagnostic and troubleshooting functions. Do not use the DAC to run resource-intensive queries.