By David Postlethwaite
At my presentation on SQL Server Management Studio at SQL Saturday in Exeter I promised to write some articles on Gethyn’s blog about the bits I didn’t manage to cover.
If a SQL instance isn’t responding or you can’t login for some reason then SSMS has a back door called the Dedicated Administrator Connection or DAC.
The DAC uses a special reserved scheduler which has one thread for processing requests.
This essentially means that SQL Server is keeping a backdoor open just for this purpose.
To login using the DAC you must be a member of the sysadmin server role.
By default, for security, you can only use this from the local server, not from a remote connection.
You can enable remote connections using the command
sp_configure 'remote admin connections', 1
To use the DAC, in the SSMS connection window prefix the name of the server with “admin:”
You can’t connect using Object Explorer because it only allows one thread so you must use the Query Window Only
Only one use one admin connection at a time can use the DAC so if another DBA tries to connect using the admin option they will get an error
Also, you should only run simple, quick queries using the DAC.
If you are connecting remotely you many need to get firewall ports opened as well, depending on your environment.
This will probably be port 1434, but that will vary depending on your configuration.
SQLcmd also supports DAC
Sqlcmd – A
Check that you can use the DAC before you need to use it for real and that you have handy whatever admin scripts you require because, remember, you can’t use object explorer to help you diagnose your problem instance.
