Using the Dedicated Administrator Connection in SQL SERVER 2005

SQL SERVER 2005 introduces the Dedicated Administrator Connection (DAC) which is a special diagnostic connection for administrators to be used when the server is slow or not responding. Using this connection, admins can access SQL server to perform diagnostic tasks; such as killing the Sql server process causing problems or running T-SQL queries.

DAC can only be run by members of the 'sysadmin' role.

There are two ways of using the DAC :

1. Through the sqlcmd utility using a special administrator switch (-A)

Eg:

sqlcmd -S servername -U username -P yourpassword -A

or

sqlcmd -A -d Northwind -E -S SUPROTIM"MYSERVER (where -E specifies Windows Authentication and -S specifies server"instance)

2. By prefixing 'admin:' to the instance name

Eg: sqlcmd -Sadmin:SUPROTIM"MYSERVER

You can also use SQL Server Management Studio by connecting to 'admin:SUPROTIM"MYSERVER'

By default, the DAC does not allow network connections. To enable remote connections, either use the Sql Server Surface Area Configuration Tool and enabling DAC. Alternatively you can use the 'sp_configure' procedure in the following manner:

sp_configure 'remote admin connections', 1;

GO

RECONFIGURE;

GO