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

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

Feedback: