How to: Use the Dedicated Administrator Connection with SQL Server Management Studio

Switch View

How to: Use the Dedicated Administr...

Community Content

In this section

Statistics Annotations (1)

Click here to expand

Collapse All

SQL Server 2005 Books Online (November 2008)

How to: Use the Dedicated Administrator Connection with SQL Server Management Studio

Microsoft SQL Server 2005 provides a dedicated administrator connection (DAC). The 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. 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.

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. Object Explorer cannot connect using the DAC.

To connect to a server using the DAC

In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query.
In the Connect to Database Engine dialog box, in the Server name box, type ADMIN: followed by the name of the server instance. For example, to connect to a server instance named ACCT\PAYABLE, type ADMIN:ACCT\PAYABLE.
Complete the Authentication section, providing credentials for a member of the sysadmin group, and then click Connect.

The connection is made.

If the DAC is already in use, the connection will fail with an error indicating it cannot connect.

Concepts

Using a Dedicated Administrator Connection

Help and Information

Getting SQL Server 2005 Assistance

Tags

: Add a tag

Community Content

Add new content

Annotations

MISSING STEPS for SQL 2005 Cluster Named Instance with a Non-Default Port

Drama1908 | Edit | Show History

I found that these steps do not work if you are running a cluster on a non-default port. In my case, I have a cluster and named instance that is not on the default port.

Start your instance with the following parameters:
- NET START MSSQL$instancename /f /T3608
Determine which port the DAC is on.
- Open the ERRORLOG, and find the entry containing “Dedicated admin connection support was established for listening remotely on port xxxx.” Where XXXX is the port number.
Open SQL Server 2005 Surface Area Configuration
- Click Surface Area Configuration for Features
- Click on DAC
- Enable remote DAC
- Click OK

Open SQL Server Configuration Manager, and create a new alias for the DAC.

Right-click and select New Alias
I used the alias name DAC, but this value can be anything you want. Just note it for later.
Port number is the port number you got from step two.
Protocol is TCP/IP
Server is IPAddress\INSTANCENAME
Click OK

Open SQL Server Management Studio, click cancel on the logon screen. I found a blog that really helped me with this step. Shoutout to http://chopeen.blogspot.com/2006/08/dedicated-administrator-connections-are.html.
- Click New Query
- Type your ALIAS name from the previous step in the Server Name field. I found that if you use ADMIN:ALIAS then you will receive an error message.
- You can logon either using SQL or Windows accounts, it depends on how you have your authentication setup.
- Click OK.

*NOTE: Make sure you turn off “ENABLE REMOTE DAC” when you are done.