Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Spackle: Making sure you can connect to the DAC

By Kenneth Fisher,

The DAC (Dedicated Admin Connection) is a diagnostic connection meant to be used when regular connections no longer work.  This means that you do not want your first attempt to connect to the DAC to be when you actually need it.  Much like a Disaster Recovery plan you should practice ahead of time.

Members of the SYSADMIN group can connect to the DAC one of two ways.  You can either use SQLCMD or an SSMS query window.

With SQLCMD you open a command window (DOS to older people) and run one of the following commands.

sqlcmd -S <ServerName> -A -E

OR

sqlcmd -S admin:<ServerName> -E
  • -E means a trusted connection
  • -S is the server to connect to
  • -A or admin:<ServerName> mean connect to the DAC.

With an SSMS query window you must use the admin:<ServerName> to connect.

Unfortunately in 2012 here appears to be a bug in the connection.

But once you hit OK you are connected.

One of the reasons to practice connecting to the DAC ahead of time is that there are a number of things that can go wrong.

  • There can only be a single connection to the DAC.  If you try to connect more than once you will see the following.

Solution: If you still have any access to your instance you can find who has the DAC open by using sys.sessions.

  • If you are using the Express edition of SQL Server you might see the following error

Solution: SQL Express is the only version of SQL Server that I know of that does not have the DAC turned on automatically.  This is done in order to conserve resources.  But if you think you may need the DAC you can turn it on using trace flag 7806.  Note this trace flag can only be turned on at startup.  You do this by adding the line –T7806 to the startup parameters of the instance.

  • Remote connections

Solution: By default the DAC only accepts local connections.  Ie Only if the connection is coming from the machine where the instance is located.  In order to enable remote connections you must turn on the 'remote admin connections' setting.

sp_configure 'remote admin connections', 1; 
GO 
RECONFIGURE; 
GO

If the 'allow updates' setting is set to 1 then you will get an error after running the RECONFIGURE statement.

  Msg 5808, Level 16, State 1, Line 1
  Ad hoc update to system catalogs is not supported.

However it still updates the value.

Total article views: 2898 | Views in the last 30 days: 71
 
Related Articles
FORUM

Passing ServerName as parameter to SQLCMD

Passing ServerName as parameter to SQLCMD

FORUM

remote connections

remote connections

FORUM

Remote Connections

Remote Connections

FORUM

SQLCMD -S servername\instancename Error

SQL Network Interfaces: The target principal name is incorrect. Sqlcmd: Error: Microsoft SQL Native ...

FORUM

Reporting Services Remote Connections

Reporting Services Remote Connections

Tags
administration    
dac    
security    
sql spackle    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones