SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

Different Ways to Enable Dedicated Administrator Connection in SQL Server

Microsoft introduced Dedicated Administrator Connection (DAC) feature in SQL Server 2005 and higher versions. 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.

Dedicated Administrator Connection [DAC] SQL Server

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.



How to Enable Dedicated Administrator Connection (DAC) in SQL Server

There are two different methods by which a DBA can enable DAC feature in SQL Server. First method is to use the TSQL Script. Second method by using SQL Server Management Studio.

How to Enable Dedicated Administrator Connection in SQL Server Using sp_configure

/*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

It is always a Best Practice to enable Dedicated Administrator Connection (DAC) on all SQL Server Instance so that during emergencies you can use this feature to troubleshoot the SQL Server Instance.

Only members of the SQL Server sysadmin role can connect using the DAC



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

DBA can also enable DAC feature using SQL Server Management Studio.

Step 1: 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.

How to Enable Dedicated Administrator Connection in SQL Server Using SSMS


Step 2: 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 RemoteDacEnabledas 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.

Next Steps

Ashish Mehta

Ashish Kumar Mehta is a database manager, trainer and technical author. He has more than a decade of IT experience in database administration, performance tuning, database development and technical training on Microsoft SQL Server from SQL Server 2000 to SQL Server 2014. Ashish has authored more than 325 technical articles on SQL Server across leading SQL Server technology portals. Over the last few years, he has also developed and delivered many successful projects in database infrastructure; data warehouse and business intelligence; database migration; and upgrade projects for companies such as Hewlett-Packard, Microsoft, Cognizant and Centrica PLC, UK. He holds an engineering degree in computer science and industry standard certifications from Microsoft including MCITP Database Administrator 2005/2008, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement