Click here to Skip to main content
15,867,594 members
Articles / Database Development / SQL Server
Tip/Trick

How to Establish Dedicated Admin Connection (DAC) to SQL Server

Rate me:
Please Sign up or sign in to vote.
4.56/5 (6 votes)
4 Oct 2016CPOL3 min read 25.4K   2  
Few tips for connecting to an SQL Server instance using DAC

Introduction

Sometimes when making changes to SQL Server, you may end up in a situation where you cannot connect to the server anymore. For example, if you create an invalid logon trigger, all connections may fail. In such a situation, you receive an error:

Login failed for login … due to trigger execution

Image 1

So how to make corrections if you cannot connect to the database in the first place. SQL Server has a concept called DAC, Dedicated Admin Connection. This is a special type of connection which for example bypasses logon triggers. You can have only one active admin connection at a time. 

Making an admin connection is basically straight-forward but you may still find yourself in a situation where the connection isn’t accepted. In such a case, use the following check list.

Connecting using SSMS

Using SSMS (SQL Server Management Studio), do not try to connect using the standard connection from object explorer. With this connection type, SSMS uses several simultaneous connections which isn’t possible when using DAC.

Initiate the connection for example from File menu using New / Database Engine Query. This ensures that you take only single connection.

When prompted for connection details, in the server name, use format:

  • ADMIN:[ServerName], for example ADMIN:MyDbServer

or with named instances:

  • ADMIN:[ServerName]\[InstanceName], for example ADMIN:MyDbServer\MyInstance

Image 2

SQL Browser is Not Running

If you receive an error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. 
The server was not found ….

Image 3

Ensure that SQL Browser is running. For example, in case of Express Edition, the SQL Browser is disabled by default.

  1. Open the Computer Management.
  2. Expand Services section.
  3. Locate SQL Server Browser.
  4. Both enable the service and start it and try the connection again.

DAC Port Not Active

By default, SQL Server Express Edition does not listen on DAC port so even if SQL Server Browser is up and running, you may still end up in connection failing. The fix is to use trace flag 7806.

Open SQL Server Configuration Manager. In Windows 8 and 10, this may be hard to find since you should locate it from Windows folders. Much easier way is to press Windows key + R to bring up the Run window and then type for example SQLServerManager13.msc to it and press ok. This opens configuration manager for SQL Server 2016.

For more detailed information, refer to SQL Server Configuration Manager.

Once the configuration manager is open, select the SQL Server instance and open Properties window. Go to Startup Parameters tab and add –T7806 to the parameter list.

Image 4

Now restart the SQL Server instance and try the connection again.

Still Not Working, Use IP Address and Port

If you still cannot connect to the server, let’s try using IP address along with the port. To know where you need to connect to, open SQL Server ERRORLOG–file from the Log folder. The log folder is located in the instance installation folder, for example something like:

C:\Program Files\Microsoft SQL Server\MSSQL13.InstanceName\MSSQL\Log

The ERRORLOG is a plain text file so you can open it using Notepad. Now find the rows containing text “Server is listening on”. You should be able to spot both the IP address and the port the server is listening for DAC connections. For example:

...
2016-10-04 23:03:49.88 Server  Server is listening on [ 127.0.0.1 <ipv4> 54153].
2016-10-04 23:03:49.88 Server  Dedicated admin connection support was established for listening 
                               locally on port 54153.
2016-10-04 23:03:49.97 spid13s SQL Server is now ready for client connections. This is an 
                               informational message; no user action is required.
...

Now using this information, try connecting with SSMS to server using the following server name syntax

ADMIN:127.0.0.1,54153

Of course, change the address and port based on the information in your ERRORLOG.

The Last Resort, Minimal Configuration

If you’re still unable to connect to the SQL Server, the last trick is to use minimal configuration. In order to do this, go back to the SQL Server Configuration Manager and the startup options. You can remove the trace flag –T7806 but add –f parameter and restart the SQL Server instance. Now the instance is running in minimal configuration and for example logon triggers are not executed. Hopefully, you can establish the connection at least now.

When All Is Done

When you have fixed the problem, remember to undo modifications. Especially don’t leave trace flags or minimal configuration options into the startup parameters.

References

History

  • 5th October, 2016: Created

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Europe Europe
Biography provided

Comments and Discussions

 
-- There are no messages in this forum --