Different Ways to Enable Dedicated Administrator Connection in SQL Server


Sept 18, 2013

Introduction

Microsoft introduced Dedicated Administrator Connection (DAC) feature in SQL Server 2005. Using DAC feature a database administrator can connect to an SQL Server Instance when SQL Server stops responding to normal connections. During such scenarios using DAC feature a DBA can connect to the instance to troubleshoot and kill processes which are causing issues.

By default, Dedicated Administrator Connection feature is disabled in SQL Server. It is always a best practice to enable DAC on all SQL Server Instance so that during emergencies you can use this feature to troubleshoot the SQL Server Instance. At any given time only one DAC connection is allowed. Learn How to Enable Remote Admin Connections or DAC in SQL Server.

Enable Dedicated Administrator Connection in SQL Server Using TSQL

/*How to Enable Dedicated Administrative Connection in SQL Server Using TSQL*/
Use master
GO

sp_configure 'remote admin connections', 1
GO

RECONFIGURE WITH OVERRIDE
GO

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

DBA can also enable DAC feature using SQL Server Management Studio. Connect to SQL Server Instance using SSMS and then right click the Server and select FACETS from the drop down menu as shown in the snippet below.

Enable Dedicated Administrator Connection in SQL Server Using SSMS or Using Policy Based Management

In View Facets window you need choose the Facet as “Surface Area Configuration” as shown in the below snippet. Then, under Facet Properties choose value as “True” for RemoteDacEnabled as highlighted. Click OK to save the changes.

Remote DAC Enabled Facets in SQL Server

Conclusion

In this article you have seen different ways by which you can enable dedicated administrator connection in SQL Server.

Share this Article



Geeks who read this article also read…




"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."






Follow @MyTechMantra on Twitter