Dedicated Administrator Connection (DAC) in SQL Server

The DAC Dedicated Admin Connection allows an administrator to access a running instance of SQL Server Database Engine to troubleshoot problems on the server—even when the server is unresponsive to other client connections. The DAC is available through the sqlcmd utility and SQL Server Management Studio. The connection is only allowed from a client running on the server. No network connections are permitted. This is a feature available in SQL 2005 onwards.

To use SQL Server Management Studio with the DAC, connect to an instance of the SQL Server Database Engine with Query Editor by typing ADMIN: before the server name.

Note: Object Explorer cannot connect using the DAC.

If you are trying to connect to object explore using DAC you will receive an error message as shown below.

Connecting to a server using DAC (Management Studio):

1.       Open SQL Server management Studio.

2.       Make sure no other DAC are open. If open, close them.

3.       Click on “File” Menu, expand “New” and select “Database Engine Query”

 .      Now type your ServerName along with Admin: as shown below
     Ex: Admin:SansLaptop

1.       Use an account which has sysadmin privilege and click Connect.

For SQL Server 2005, we need to enable the DAC by using sp_configure or Surface area configuration for features.

Exec sp_configure 'remote admin connections',1

Go

Reconfigure

GO

OR

Using SQLCMD:

1.       Open run and type the below command
       sqlcmd -A -d Test -E -S  SansLaptop

-A represents DAC or Admin

-d represents Database Name

-E represents Integrated Security

-S represents Server Name.
Here also you need to use an account which has SysAdmin privilege.

This article is also available in pdf format for downloading.

Please Click here to get your copy.