admin: prefix before the servername while connecting to DB Engine Query in MS SQL Server

Question

I am using SSMS to connect to DB Engine Query for DAC. I've seen some video tutorials where people use 'admin:' prefix before server name while connecting

When trying to connect with that prefix I get an error shown in the picture above.

I've checked that SQL Server Browser is running. Also I am running SSMS as an administrator and while Named Pipes protocol is disabled I've changed options to accept TCP/IP as a default protocol.

What is the use of that prefix and how to avoid this error?

Thanks for your time!

Answer 1

From your Server name I gathered that this is a SQL Serve Express version you are trying to connect to.

The issue is, by default SQL Server express does not listen on DAC port, to fix the issue you will need to use the Trace Flag 7806.

You can enable TF7806 by two ways:

1. Go to SQL Server Configuration Manager --> SQL Server instance --> Properties window --> Startup Parameters --> add –T7806 to the parameter list

2. Or from SSMS execute DBCC TRACEON(7806, -1);

Then try what you are doing and it should work.

Answer 2

By using the admin: prefix you are defining that you want to connect to SQL Server instance by using Dedicate Admin Connection (DAC). The main (and usually only) reason you want to do this is to troubleshoot/diagnose SQL Server that is rejecting all other, normal connections due to resource exhaustion or some other issue.

In SQL Server DAC has it's own scheduler (and dedicated resources) to guarantee connectivity under most circumstances. However there are also some restrictions when connecting with DAC, like no parallelism for queries, etc. making it unsuitable for "normal" use.

You can find the full list of restrictions from here.

As for that error message, if you have confirmed that SQL Browser service is running check the SQL Server errorlog to find which port the DAC is listening to. Once you know the port, verify that it is not being blocked by Firewall.

Answer 3

Did you enable DAC in the server? Please check the DAC is enabled in the server.

For enabling DAC, log in with dbadmin privileges

Use master
GO
/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/ 
sp_configure 'remote admin connections', 1 
GO
RECONFIGURE
GO

Copied from the below link You can find more detailed information from here

Then try with the admin: in the connection string.

Edit 2

I missed on a thing you are using express edition. For enabling DAC in express edition I think you have to do the way told in this MSDN forum here

Steps to enable DAC in Express:

1.) Include flag T7608 in sqlexpress startup parameter

2.) Restart Sqlexpress services

3.) Run "Exec master..xp_readerrorlog" and check the TCP\IP port assigned for DAC. You can search in the errorlog for "admin" keyword.

4.) Try connecting as sqlcmd -A -S.\sqlexpress,DAC-portnumber -dmaster

I don't have an express edition to test this. Just try from your side.