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

Check the following : http://msdn2.microsoft.com/en-us/library/ms189595.aspx

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook