How To Enable Dedicated Administrator Connection in SQL Server : Demo

There are 2 ways to enable DAC ( Dedicated Administrator Connection ) in SQL server

1. Using T-SQL
2. Using SSMS

1.  sp_configure will enable DAC for you and the option is remote admin connections which need to be enable

/* Checking status of remote admin connections*/

Use master

GO

sp_configure 'remote admin connections'

GO

/* 0 = Allow Local Connection, 1 = Allow Remote Connections*/

sp_configure 'remote admin connections', 1

GO

RECONFIGURE with Override

GO

sp_configure 'remote admin connections'

2. Enable Dedicated Administrator Connection Using SSMS is the easiest option for GUI lovers

1. Right clicking the SQL Server Instance and selecting the Facets option from the drop down list.

2. You need to select Surface Area Configuration facet as highlighted and then select the option as "True" for RemoteDacEnabled.

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