Problem
The Dedicated Administrator Connection (DAC) is a handy tool for a variety of tasks, ranging from snooping around in the definition of system objects, all the way to recovering an unresponsive SQL Server. But what if you can’t connect to the DAC? There are a number of reasons why the connection may fail, and this tip walks through the problems and helps you get it working again.
Solution
First and foremost, let’s make sure you don’t have a basic connectivity issue. Microsoft has a good troubleshooting starting point here and I have listed many of the causes for 18456 errors here. Also make sure you aren’t being restricted by your firewall, which should affect both DAC and normal connections; look beyond port 1433 because that’s not necessarily the port where your instance is running.
If you are sure this is not a connectivity issue, read on.
Settings
There are a number of things that must be enabled in order for the DAC to work, and you shouldn’t wait for an emergency to get these things in line and adequately tested.
First, certain versions of SQL Server Express Edition require a trace flag just to turn on the feature. If you’re managing SQL Server Express, enable -T7806 as a startup trace flag. Open SQL Server Configuration Manager (Start > Run > SQLServerManagerXX.msc, where XX is the major version number, like 14). Then, in the SQL Server Services node, you will see instances listed in the right-hand pane. Right-click the instance, select Properties, and move to the Startup Parameters tab. There you want to add -T7806, click Apply, and then restart the service. Ashish Kumar Mehta talked about this in a previous tip. While you’re on that screen, make sure the SQL Server Browser service is running and set to start automatically. If it isn’t running, this can prevent you from connecting to named instances using any kind of connection.
(Note that DAC is disabled by default in Express since the connection pre-allocates resources, and Express Edition is already artificially resource constrained. Make sure you test that this doesn’t cause a significant performance degradation to your typical Express workloads.)
In the event of an unresponsive machine, you will likely need to connect to SQL Server over TCP/IP. First, Enable TCP/IP (which is disabled by default on certain editions, for example, Developer Edition). Open SQL Server Configuration Manager (SQLServerManagerXX.msc, where XX is the major version number), open SQL Server Network Configuration, and for each instance you want to manage, navigate to Protocols for <ThatInstanceName>. In the right-hand pane, you will see TCP/IP listed; if it is Disabled, you need to right-click and choose Enable. Note that a service restart will be required:
Next, you can check if the remote admin connections instance-level configuration setting is enabled:
SELECT name,value_in_use <br /> FROM sys.configurations<br /> WHERE name = N'remote admin connections';
If the result is 0, you’ll need to turn this setting on:
EXEC sys.sp_configure N'advanced options', 1;<br />GO<br />RECONFIGURE WITH OVERRIDE;<br />GO<br />EXEC sys.sp_configure N'remote admin connections', 1;<br />GO<br />RECONFIGURE WITH OVERRIDE;<br />GO
Now, to connect using the DAC via Management Studio, you can do so with a new or an existing query window. There is no point in trying to connect using Object Explorer or Object Explorer Details; since only one DAC connection is allowed, and Object Explorer requires several, you wouldn’t be able to get anything done. In an existing query window you can just right-click, choose Connection > Change Connection…, and prefix the server name with ADMIN:, as follows:
If you do this and you get the dreaded “A network-related or instance-specific…” message, then something above has not been configured correctly, and you should head back up to troubleshoot general connectivity.
If you get a message regarding “The network name is no longer available,” then it is probably because you’ve restarted SQL Server since the query window was opened, or some other connectivity issue has come up. In this case you should just be able to open a new query window and try again, unless there is another DAC connection already open somewhere:
Before you panic and restart the SQL Server service or reboot the box, see if this error is happening because you already have a DAC connection open, which is possible:
SELECT s.session_id, s.[host_name], s.[program_name], s.login_name<br />FROM sys.dm_exec_sessions AS s<br />WHERE s.endpoint_id = 1;<br />-- SELECT name FROM sys.tcp_endpoints WHERE endpoint_id = 1:<br />-- Dedicated Admin Connection
If this returns a row, this should give you enough information to either go have a conversation with someone, or just issue KILL <SPID>; to boot them out. If it doesn’t return a row, you may want to first try opening a new query window, next try starting a new instance of SSMS, and – as a last resort – try restarting the service (or waiting for the next maintenance window, if this isn’t a critical issue).
False Alarm
One thing I’ve seen come up many times in the forums is when the user connects via the DAC, but SSMS still gives them an error message:
Believe it or not, this error message actually means success. Remember that only one DAC connection is allowed; this error message comes from one of the additional threads your query window spawns when opening (typically IntelliSense). You can verify this in the status bar, which should show the ADMIN: prefix:
Or by simply running a query you wouldn’t be able to run otherwise:
SELECT TOP 1 * FROM sys.syscolpars;
If SSMS isn’t your boat and you’d rather connect through SQLCMD, you can just add the -A argument:
And if you come up against any of the errors listed above, you should be able to resolve them by stepping through the same set of actions.
Next Steps
DAC is a very handy tool, again, either for snooping or for disaster mitigation. Don’t wait for the latter to happen before you are sure it is working correctly on all of the instances you care about. Read on for related tips and other resources:
- Dedicated Administrator Connection in SQL Server 2005
- Enable SQL Server Dedicated Administrator Connection
- Enabling Dedicated Administrator Connection in SQL Server 2008 Express Edition
- Solving Connectivity errors to SQL Server
- The Dedicated Admin Connection: Why You Want It, When You Need It, and How To Tell Who’s Using It
