In this article you will learn how to obtain emergency access to a SQL Server 2008 R2 database instance by using the built-in Dedicated Administrator Connection (DAC) functionality.

Timothy Warner

Timothy Warner is a Microsoft Cloud and Datacenter Management Most Valuable Professional (MVP) who is based in Nashville, TN. Check out his Azure and Windows Server video training at Pluralsight, and feel free to reach out to Tim via Twitter.

Latest posts by Timothy Warner (see all)

In Microsoft Windows, we have Safe Mode. In SQL Server 2008, we have the Dedicated Administrator Connection, or DAC. We can use DAC to obtain emergency access to a SQL Server 2008 database instance in cases where the instance is blocked up, locked up, or just plain hosed.

SQL Server 2008 keeps a dedicated scheduler available at all times for the DAC. Therefore, you should (almost) always be able to connect to SQL Server by using the DAC.

You can log into the SQL Server instance with a DAC while other users are logged in at the same time. However, only one DAC session is allowed at any one time.

Enabling Dedicated Administrator Connection ^

By default, local connections to a DAC are enabled in SQL Server 2008 R2. Therefore, you can use the DAC during an interactive logon or a quasi-local logon through VNC, Remote Desktop Services, or the like.

To enable remote access to DAC, open an elevated command prompt on the SQL Server, start sqlcmd, and issue the following instance configuration commands:

sp_configure 'remote admin connections', 1;<br />GO<br />RECONFIGURE;<br />GO

In order to use the DAC, your domain or SQL Server user account must be a member of the sysadmin server role.

Accessing DAC from SSMS ^

A common mistake that some DBAs use in trying to use DAC with SQL Server Management Studio (SSMS) is to attempt DAC access to Object Explorer instead of the Database Engine Query window.

That is, SQL Server 2008 will throw an error if you attempt to use DAC with a standard SSMS login.

Instead, fire up SSMS, cancel out of the logon dialog box, and instead click New > Database Engine Query, as shown in Figure 1.

Dedicated Administrator Connection -Starting the DAC from SSMS

Starting the DAC from SSMS

In the Connect to Database Engine dialog box (shown in Figure 2), simply prepend admin: to the server name.

NOTE: The admin: string is case insensitive. Therefore, ADMIN:, admin:, and AdMiN: are equivalent.

Dedicated Administrator Connection - Logging into SQL Server with the DAC

Logging into SQL Server with the DAC

Accessing DAC from sqlcmd ^

To access DAC from an elevated command prompt, simply add the –A parameter to the sqlcmd utility.

If you supply the command string sqlcmd –A, then SQL Server assumes the following regarding the connection attempt:

  • Target server is localhost
  • Login credentials match the currently logged on OS user
  • Target database is the default database of the current user

On the other hand, you can use additional switches to customize precisely how you enter the DAC environment. For instance, the following sample code connects us to a SQL Server machine named sqlsrv1 using the corp\admin Active Directory domain credentials. Additionally, we connect to the master database.

C:\>sqlcmd S sqlsrv1 U corp\admin P Pa$$w0rd d master A<br />1>

What can you do next?

Once you’ve logged into SQL Server by using the Dedicated Administrator Connection, you can issue Transact-SQL statements to perform whatever troubleshooting and/or administrative tasks you need to undertake.

Dedicated Administrator Connection - Accessing the DAC by using sqlcmd

Accessing the DAC by using sqlcmd

For instance, from the DAC you can perform the following actions:

Conclusion ^

At this point you should have a firm understanding of what the Dedicated Administrator Connection (DAC) is in SQL Server 2008. You should also understand how to access the DAC by using the sqlcmd command-line utility as well as SQL Server Management Studio.

Win the monthly 4sysops member prize for IT pros

0
Share
6 Comments
  1. techibee.com 8 years ago

    Is it same as single user mode? Are there differences between single user mode and DAC? or it's just a new name for old stuff?

    0

  2. Tim Warner 8 years ago

    Hi techibee,

    Single-user mode and Dedicated Administrator Connection are different. Single user mode is an instance startup type. You can shutdown a SQL Server instance and start it up in single user mode to, for instance, recover a corrupted master database. Believe it or not, you can connect using the DAC while the instance is started in single user mode! 🙂 Great question--I may put up a blog post on this very subject. -Tim

    0

  3. techibee.com 8 years ago

    Thanks for explanation. Now I am able to distinguish between DAC and single user mode. I did some reading after posting the question. Your comment made it so clear.

    Thanks,
    Sitaram

    0

  4. subahan munthamadugu 7 years ago

    nice explanation

    0

  5. surekha 5 years ago

    Very nice Explanation. thank you very much for your article.

    0

  6. Author
    Timothy Warner 5 years ago

    Thanks for your kind words, Subahan and Surekha! I'm glad you enjoyed the article. All the best, Tim

    0

Leave a reply

Your email address will not be published. Required fields are marked *

*

© 4sysops 2006 - 2019

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account