This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.
In SQL Server, a feature that often stands out for its unique capabilities is the Dedicated Administrator Connection (DAC). This not just another connection to your SQL Server instance, but a special diagnostic connection designed for administrators when standard connections to the server are not possible.
The Power of the DAC
The DAC is like an emergency entrance to your SQL Server instance. It provides a way to connect to SQL Server and run basic queries to investigate and potentially fix issues. This can be particularly useful when the server is under heavy load or experiencing other problems that prevent standard connections.
Imagine a scenario where your SQL Server instance is not responding. Standard connections are failing, and you need to diagnose the problem. This is where the DAC comes into play. With the DAC, you can connect to the server and execute diagnostic queries or other troubleshooting tasks, even when standard connections are not available. Steps to connect via the admin connection are shared later in the post.
The Constraints of DAC
While the DAC is a powerful tool, it’s important to understand its limitations. By default, the DAC is only available from a client application on the same server. This means you cannot use the DAC from a remote computer unless you specifically enable the configuration, you can use this script to determine if remote admin connections are enabled:
SELECT name, value_in_use FROM sys.configurations WHERE name = 'remote admin connections'
Moreover, only one admin connection is allowed per instance of SQL Server. This means if someone is already connected to the server using the DAC, you won’t be able to establish another until the first one is closed.
The DAC also doesn’t support parallel queries or SQL Server Profiler. It’s not intended for routine use, but rather for troubleshooting specific issues.
SQL Server Security Considerations
Enabling remote DAC can have security implications. It’s crucial to use strong access control procedures to protect your server from unauthorized access. Regularly reviewing role membership and explicit permissions can help maintain a secure SQL Server environment. Fortunately, only users with the role of sysadmin can connect via the DAC, so ensuring least privilege in your environment means that should be an extremely short list of logins.
Connecting via the DAC
To test a connection via the DAC in SQL Server Management Studio (SSMS) there are a few simple steps to follow, there are other posts that cover this and for SQLCMD, but I will show just the SSMS method:
- Open SSMS.
- Connect to the instance you would like to test the admin connection on and then open a new query window- navigate to connection and then change connection
- In the ‘Connect to Server’ dialog box, in the ‘Server name’ field, type admin: followed by the name of the server instance you want to connect to. For example, if your server name is MyServer, you would type admin:MyServer.
- Choose the appropriate ‘Authentication’ method and provide the necessary credentials.
- Click ‘Connect’.
As you can imagine, this would not work if connections via SSMS are already failing. Familiarizing yourself with the SQLCMD method would be beneficial.
Wrapping Up
In conclusion, the DAC is a powerful tool for SQL Server administrators. However, it’s crucial to understand its uses and limitations. We encourage you to determine whether enabling remote DAC is right for your SQL Server environment. We think it is a worthwhile tool to have enabled, especially for helping to gain access to locked down instances remotely. Remember, the DAC is not a silver bullet, but a tool in your SQL Server toolbox. Use it wisely and securely.
