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

 

Dedicated Administrator Connection in SQL Server 2005


By:   |   Read Comments (8)   |   Related Tips: More > Disaster Recovery

Attend these FREE SQL Server 2017 webcasts >> click to register


Problem
In years and versions of SQL Server gone by, at times it was impossible to gain access to SQL Server when the server had a spinloop, maxed out resources or severe corruption.  No matter how you tried to gain access to the server (i.e. Enterprise Manager, Query Analyzer, OSQL, ISQL, a third party IDE, etc.) it was not possible.  With pressure mounting to get SQL Server back online a last resort was to reboot the server.  At times, SQL Server was able to checkpoint the databases and shutdown gracefully other times your were not so lucky.  No matter how patient you were with SQL Server on the shutdown and subsequent startup, the roll back process was long and at times yielded database corruption or access violations.

Solution
With SQL Server 2005, Microsoft offers a new feature, the dedicated administrator connection.  This configuration is intended to serve as a last means of defense to troubleshoot and kill the offending SQL Server processes as opposed to rebooting the server possibly causing database corruption and/or access violations. The DAC allows you to connect to the server and issue T-SQL commands to troubleshoot and correct the problem.

To use the DAC you can either access it using SQLCMD from a command prompt

  • sqlcmd -A -d master (the -A uses the DAC and the -d connects to the master database)
  • sqlcmd -A -d master -E -S EDGESERV1\TEST1 (the -E uses Windows authentication and the -S connects the server and instance)

or by using SQL Server Management Studio with the ADMIN: option when connecting to the server.  To use this option:

  • launch SQL Server Management Studio (don't connect to an instance of SQL Server yet)
  • select Database Engine Query (icon right next to "New Query")
  • put "ADMIN:" in front of the server\instance name
  • use your regular Authentication procedures to connect
  • and select Connect

 

Once you have connected to the database engine you can now issue T-SQL commands to identify the problem and fix the problem. Note: there are no GUI tools so you need to be familiar with the T-SQL commands to use to troubleshoot the problem such as:

  • sp_who2
  • dbcc inputbuffer
  • kill
  • etc...

Note:
By default the DAC is only available from a client on the server.  To enable remote clients to utilize the DAC an option needs to be set which can be done using sp_configure

  • sp_configure 'remote admin connections', 1;
     

or by using the SQL Server Surface Area Configuration.  To do this:

  • launch the SQL Server Surface Area Configuration.tool which can be found under SQL Server 2005 Configuration Tools in the SQL Server 2005 menus
  • select "Surface Configuration Area for Features".
  • check the box for "Enable remote DAC"
  • select "Apply"

 

Next Steps

  • Take the time to determine if the remote administrator only configuration setting should be enabled in your environment to troubleshoot a SQL Server issue if it arises. 
  • Be sure to deploy this configuration and verify it is configured as expected on each of the SQL Servers.
  • If an issue arises, keep this configuration in the back of your mind as a possible means to correct the issue without gracefully shutting down SQL Server.
  • Be sure you are up to speed on the T-SQL administrative commands to troubleshoot and rectify any issues

 



Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

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    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, August 26, 2014 - 2:34:16 PM - Dinesh DBA Back To Top

Jeremy,

EXcellent ...Thanks...


Regards,

Dinesh


Tuesday, August 26, 2014 - 9:41:59 AM - Jeremy Kadlec Back To Top

Dinesh,

If you have SQL Server SA rights, you can run sp_configure then parse the results for 'remote admin connections' or you can use this command to set it:

sp_configure 'remote admin connections', 1;

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

 


Tuesday, August 26, 2014 - 8:00:45 AM - Dinesh DBA Back To Top

Hi,

How to check DAc is enabled using Query ?


Tuesday, July 29, 2014 - 1:12:50 PM - kiran N Back To Top

Good Article it is. Thanks for sharing and nice tip on sql cluster


Friday, February 15, 2013 - 2:54:56 PM - Jeremy Kadlec Back To Top

Parminder,

Have you checked out this tip - http://www.mssqltips.com/sqlservertip/2851/locked-out-of-sysdmin-regain-sysadmin-on-a-sql-server-2012-cluster/?

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader


Friday, February 15, 2013 - 1:41:25 PM - Parminder Back To Top

Hi Ray,

Accidently I disabled my Sql Server account and forgets the sa password Is there any way to enable the account as that was the only account I had. Now I can't access the SQL Server.

Please help I am at the edge of losing a lot

Thank you

PArminder


Monday, August 16, 2010 - 7:34:48 PM - Admin Back To Top
Ray,

Thank you for the information on clustering.  That was not an angle we covered in the tip.

I think it is a good item to point out.

Thank you,
The MSSQLTips Team


Monday, August 16, 2010 - 6:22:40 PM - Ray Giacalone Back To Top

On clustered instances, a special startup parameter is required to get a DAC connection with sqlcmd:

sqlcmd -S<serveraddress>, <DacPort>

 

Here is an example using the Loopback Adapter Address:

sqlcmd -S127.0.0.1,49492

 

The DacPort is the dynamically assigned port number that the SQL instance is using to listen for DAC connections and can be found in the logs of the file system.


Learn more about SQL Server tools