DAC -- Dedicated Administrator Connection

 

SQL Server introduced DAC on SQL Server 2005 which is a special type of connection where database administrators can connect to SQL Server when regular connections are not possible. In other words it is a backdoor to access to SQL Server resources to troubleshoot some serious issues. 

 

There is one reserved scheduler which allows us to login into SQL Server using DAC. There is one specific endpoint reserved for DAC that is DAC Endpoint and it is #1.

 

The default behavior of SQL Server DAC is it wont allow other clients to login into servers using DAC, to do so we need to configure 'remote admin connections' to 1 from 0.

 

Note : Only one connection made using DAC, also only Sysadmin has right to connect to DAC

 

To connect using DAC

Open SSMS -- New Query (On ServerName Type ADMIN:ServerName)
Server Name  -- ADMIN:ServerName (or)
Server Name  -- ADMIN:ServerName\NamedInstance (or)
Server Name  -- ADMIN:ServerName[\NamedInstace], PortNumber

 

(OR)

 

cmd > sqlcmd -SServerName[\NamedInstance] -E -A

 

-S  -- ServerName
-A -- Enable DAC
-E -- Windows Autentication
-U -- UserName
-P -- Password

 

Run the command to enable DAC to client connections

sp_configure 'remote admin connections',1
go

Reconfigure

 

(OR)

 

Right click on server name -- Facets -- Select Surface Area Configuration from Facet dropdown
Remote DAC Enabled  -- True

 

-- Query will return 1 row, if you are connected using DAC.

SELECT ec.login_name, ep.name, ec.session_id, ep.endpoint_id
FROM sys.dm_exec_sessions ec, sys.endpoints ep
WHERE ec.endpoint_id = ep.endpoint_id AND session_id=@@spid