How to connect using the Dedicated Administrator Connection

The SQL dedicated administrator connection(DAC) has been around since SQL 2005 and is a diagnostic connection that is designed to allow an administrator to troubleshoot SQL Server when the instance is having problems.  Only members of the sysadmin role can connect using the DAC.  You can connect using the DAC both through SQLCMD (SQLCMD –A –d master) and in SQL Server Management Studio (ADMIN:<instance_name>).

If you don’t have remote admin connections enabled in the instance, you will only be able to connect to DAC while logged into the server.   It’s pretty simple to enable the setting.  Just run this:

EXEC sp_configure ‘remote Admin Connections’, 1

GO

RECONFIGURE

GO

While you can run many T-SQL statements while connected using DAC, there are limitations.  For instance, you can’t run backup or restore commands while connected using DAC.  You should stick to queries that check DMV’s such as sys.dm_tran_locks , sys.dm_os_scheduler, sys.dm_exec_requests, sys.dm_exec_sessions as well as some DBCC statements such as DBCC FREEPROCCACHE and DBCC SQLPERF.  More on the DAC and its limitations here:  https://msdn.microsoft.com/en-us/library/ms189595.aspx

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. We love to talk tech with anyone in our SQL family!