Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















Enable SQL Server 2008 Dedicated Administrator Connection

MSSQLTips author Ashish Kumar Mehta By:   |   Read Comments (4)   |   Related Tips: More > Database Administration
Problem

One of the junior SQL Server Database Administrators in my company approached me yesterday with a dilemma. He want to know how to enabled the Dedicated Administrator Connection in SQL Server 2008. At first I started to tell him, but figured it would be smarter to document the options and share the information.  This tips shows you how you can enable this feature in SQL 2008.

Solution

In SQL Server 2005, Microsoft introduced a new feature called Dedicated Administrator Connection (DAC). Using this feature a SQL Server Database Administrator can connect to a SQL Server Instance when the database engine is not responding to regular connections. During such a scenario a DBA can connect to the SQL Server Instance to troubleshoot and to kill any of the SQL Server Processes which are causing the issues.

The DAC allows database administrators to connect to a SQL Server Instance and to execute T-SQL commands to troubleshoot and fix issues rather than rebooting the SQL Server which could lead to database corruption or other problems. By default, the remote Dedicated Administrator Connection feature is disabled in SQL Server 2005 and later versions. It's a good practice to enable the DAC feature once the SQL Server 2005 or SQL Server 2008 is installed on every instance as this will help you troubleshoot issues when regular connections are not responding. However, only one dedicated administrator connection is allowed at a time on SQL Server 2005 and later versions. 


Enable Dedicated Administrator Connection in SQL Server 2008 Using TSQL

Execute the below T-SQL to enable remote clients to utilize the Dedicated Administrator Connection.

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


Enable Dedicated Administrator Connection in SQL Server 2008 Using SQL Server 2008 Management Studio

Database Administrators can also enable Dedicated Administrator Connection Feature using SQL Server 2008 Management Studio. This can be done by right clicking the SQL Server Instance and selecting the Facets option from the drop down list as shown in the snippet below.

DAC tree

This will open up View Facets window as shown in the snippet below. Here you need to select Surface Area Configuration facet as highlighted and then select the option as "True" for RemoteDacEnabled.

DAC settings

Finally, click OK to save the configuration changes in the View Facets window.

You can also enable other database engine features like AsHocRemoteQueriesEnabled, ClrIntegrationEnabled, DatabaseMailEnabled, OleAutomationEnabled, ServiceBrokerEndpointActive, SoapEndpointsEnabled, SQLMailEnabled, WebAssistanceEnabled, XPCmdShellEnabled etc when required using the Surface Server Configuration Facet which is available in SQL Server 2008 Management Studio.

Once the Dedicated Administrator Connection is enabled you can connect to SQL Server 2008 using either SQL Server Management Studio or using SQLCMD.


Using DAC with SQLCMD

You need to be a member of the sysadmin fixed server role in order to use this feature. The "-A" switch is used to specify that the user is trying to connect to SQL Server using the Dedicated Administrator Connection.

The syntax to use DAC is mentioned below.

SQLCMD -S [SQL Server Name] -U [User Name] -P [Password] -A 

Using DAC with SQL Server Management Studio

You need to specify "ADMIN:" before the SQL Server Instance name when trying to connect to an SQL Server Instance to using DAC feature as shown in the snippet below.

DAC login

Once you are connected to SQL Server Instance using DAC, then you can execute code such as the code below to check the SQL Server health.

-- Locking Information
SELECT * FROM sys.dm_tran_locks
GO
-- Cache Status
SELECT * FROM sys.dm_os_memory_cache_counters 
GO
-- Active Sessions
SELECT * FROM sys.dm_exec_sessions 
GO
-- Requests Status
SELECT * FROM sys.dm_exec_requests
GO

DAC query result

 

Next Steps
  • Make sure you have enabled Dedicated Administrator Connection Feature on all the SQL Server 2005 and on SQL Server 2008 servers.
  • If at any point database engine is not allowing regular connections, then use this feature to troubleshoot issues without shutting down SQL Server.
  • DAC for SQL Server 2005


Last Update: 7/24/2009


About the author
MSSQLTips author Ashish Kumar Mehta
Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Tuesday, January 11, 2011 - 11:54:19 AM - Jeremy Kadlec Read The Tip

Ashish,

Great tip.

I have one comment for SQL Server 2008 R2.  In order to start the DAC in SSMS, I believe you need to navigate to File | New | Database Engine Query, then specify 'ADMIN:' prior to the server name.  I believe as long as you have sa rights you can connect to the DAC either with a SQL Server standard login or Windows Authentication. 

I do not think you can use the 'Connect' option from Object Explorer in order to start a DAC session.

Finally, I think you will know if the DAC connection has been established when you look at the bottom status bar in SSMS and see 'ADMIN:SQLServerName'.

I hope this helps.

Thank you,
Jeremy Kadlec


Friday, August 26, 2011 - 8:21:32 AM - Leo Smulders Read The Tip

Hi,

I Observe that 'remote admin connections' is not an advanced option. So setting 'show advanced options' to 1 is not necessary, neither to show, nor to change 'remote admin connections'.

Leo Smulders
Lucy Software B.V.


Friday, August 26, 2011 - 8:54:33 AM - Jeremy Kadlec Read The Tip

Leo,

Thank you for the post.  We will update the tip.  I see the same behavior in SQL Server 2008 R2.

Thank you,
Jeremy Kadlec


Wednesday, July 31, 2013 - 12:53:30 AM - SQLDBA Read The Tip

Starting the DAC from SSMS

In the Connect to Database Engine dialog box (shown in Figure 2), simply prepend admin: to the server name.

NOTE: The admin: string is case insensitive. Therefore, ADMIN:, admin:, and AdMiN: are equivalent.

server name:- admin:servername
Authentication:- Window authecation



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.