SQL DAC


When you start SQL Server (2005+) it creates a separate “Dedicated Administrator Connection” or DAC using a special TCP port. One sysadmin at a time can connect with this DAC connection by specifying Admin:ServerName\Instance. From SQLCMD you can either prefix the server name with Admin: or you can use the /A switch. From SSMS you can use Admin: to make a “Query Editor” connection but you cannot use it in Object Explorer. DAC should only be used when other connection methods fail and you must collect information or think you might be able to kill some SPIDs to improve the situation. On a local connection you can always use DAC (as long as you are a sysadmin and no one else is using it) but for remote connections (and all connections to a cluster are considered remote) you must have enabled remote connections for DAC:


EXEC sp_configure ‘remote admin connection’, 1


RECONFIGURE


 


If you want to see who if anyone is connected using DAC, try this query:


SELECT dec.local_tcp_port AS DAC_Port, des.login_name AS LoginName, des.nt_domain AS NTDomain,


      des.nt_user_name AS NTUserName, dec.session_id AS SPID,


      dec.connect_time AS ConnectTime, dec.last_read AS LastRead, dec.last_write AS LastWrite,


      des.host_name AS HostName, dec.client_net_address AS ClientIP, des.program_name AS AppName,


      e.state AS EndpointState, e.is_admin_endpoint AS IsAdminEndpoint


      FROM sys.dm_exec_connections dec


      JOIN sys.endpoints e ON e.endpoint_id = dec.endpoint_id


      JOIN sys.dm_exec_sessions des ON des.session_id = dec.session_id


      WHERE e.name = ‘Dedicated Admin Connection’


 


If you have any Express Editions, you have to use trace flag 7806 to enable DAC for Express.


 


Using a Dedicated Administrator Connection


http://msdn.microsoft.com/en-us/library/ms189595.aspx


 


How to: Use the Dedicated Administrator Connection with SQL Server Management Studio


http://msdn.microsoft.com/en-us/library/ms178068.aspx

Comments (0)