The DAC (Dedicated Admin Connection) is a diagnostic connection meant to be used when regular connections no longer work. This means that you do not want your first attempt to connect to the DAC to be when you actually need it. Much like a Disaster Recovery plan you should practice ahead of time.
Members of the SYSADMIN group can connect to the DAC one of two ways. You can either use SQLCMD or an SSMS query window.
With SQLCMD you open a command window (DOS to older people) and run one of the following commands.
sqlcmd -S <ServerName> -A -E
sqlcmd -S admin:<ServerName> -E
- -E means a trusted connection
- -S is the server to connect to
- -A or admin:<ServerName> mean connect to the DAC.
With an SSMS query window you must use the admin:<ServerName> to connect.
Unfortunately in 2012 here appears to be a bug in the connection.
But once you hit OK you are connected.
One of the reasons to practice connecting to the DAC ahead of time is that there are a number of things that can go wrong.
- There can only be a single connection to the DAC. If you try to connect more than once you will see the following.
Solution: If you still have any access to your instance you can find who has the DAC open by using sys.sessions.
- If you are using the Express edition of SQL Server you might see the following error
Solution: SQL Express is the only version of SQL Server that I know of that does not have the DAC turned on automatically. This is done in order to conserve resources. But if you think you may need the DAC you can turn it on using trace flag 7806. Note this trace flag can only be turned on at startup. You do this by adding the line –T7806 to the startup parameters of the instance.
- Remote connections
Solution: By default the DAC only accepts local connections. Ie Only if the connection is coming from the machine where the instance is located. In order to enable remote connections you must turn on the 'remote admin connections' setting.
sp_configure 'remote admin connections', 1; GO RECONFIGURE; GO
If the 'allow updates' setting is set to 1 then you will get an error after running the RECONFIGURE statement.
Msg 5808, Level 16, State 1, Line 1
Ad hoc update to system catalogs is not supported.
However it still updates the value.