Home Library Learn Downloads Support Community
Sign in | United States - English | |
5 out of 9 rated this helpful - Rate this topic

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

SQL Server 2005

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.

  1. In SQL Server Management Studio, with no other DACs open, on the toolbar, click Database Engine Query.

  2. 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.

  3. 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.

Did you find this helpful?
(1500 characters remaining)
Community Content Add
Annotations FAQ
MISSING STEPS for SQL 2005 Cluster Named Instance with a Non-Default Port
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.

      History