Tags


According to BOL “DAC 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.” It is available through the sqlcmd utility as well as SSMS (SQL Server Management Studio). This feature is available from SQL Server 2005 onwards.  This diagnostic connection allows an administrator to access SQL Server to execute diagnostic queries and troubleshoot problems even when SQL Server is not responding to standard connection requests. The connection is only allowed from a client running on the server. No network connections are permitted.

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.

Important Note: Object Explorer cannot connect using the DAC.

When we try to connect to Object Explorer using DAC, we will get the following error message:

Many people make the mistake of connecting Object Explorer using DAC.

I will show the steps to connect to a server using DAC through SSMS:

  1. Open SQL Server Management Studio (SSMS).
  2. Make sure there are no active DAC connection for the particular instance else new request to connect through the DAC is denied with error 17810.
  3. Click on “File” Menu, expand “New” and select “Database Engine Query”.

Type your ServerName along with Admin: as shown below
Ex: Admin:SUBHROServer

Give the credentials which have SYSADMIN privileges and then click Connect.

Using SQLCMD:  Open run and type the below command:

sqlcmd -A -d AdventureWorks -E -S  SUBHROServer

-A represents DAC or Admin
-d represents Database Name
-E represents Integrated Security
-S represents Server Name

You need to use an account which has SysAdmin privilege.

Keep Learning and Keep Coding !!

Cheers,

Subhro Saha

Advertisements