Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - The Cloud won't fix that (click for more info)
 

Troubleshooting the SQL Server Dedicated Administrator Connection


By:   |   Read Comments   |   Related Tips: More > Dedicated Administrator Connection

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.

Adding a startup trace flag

(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:

Enabling network protocols in SQL Server Configuration Manager

Next, you can check if the remote admin connections instance-level configuration setting is enabled:

SELECT name,value_in_use 
  FROM sys.configurations
  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;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sys.sp_configure N'remote admin connections', 1;
GO
RECONFIGURE WITH OVERRIDE;
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:

Adding ADMIN: prefix to instance name in connection dialog

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.

Error message when connectivity is not configured correctly

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:

database engine

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
FROM sys.dm_exec_sessions AS s
WHERE s.endpoint_id = 1;
-- SELECT name FROM sys.tcp_endpoints WHERE endpoint_id = 1:
-- 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:

This error messsage *actually* means success

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:

It

Or by simply running a query you wouldn’t be able to run otherwise:

SELECT TOP 1 * FROM sys.syscolpars;
			
Something you normally can

If SSMS isn’t your boat and you’d rather connect through SQLCMD, you can just add the -A argument:

DAC from SQLCMD

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:



Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools