Dedicated Administrator Connection (DAC) in SQL Server 2008 R2

• Author
• Recent Posts

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)

• Azure PowerShell vs. Azure CLI - Wed, Mar 15 2023
• Use Azure Storage for backup - Thu, Feb 23 2023
• Azure Data Studio vs. SQL Server Management (SSMS) - Wed, Feb 8 2023

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;

GO

RECONFIGURE;

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.

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.

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

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.

Accessing the DAC by using sqlcmd

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

• Run T-SQL scripts
• Run DBCC utility commands
• Access Dynamic Management Views (DMVs)
• Access catalog views

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.