A useful feature introduced in SQL Server 2005 was the Dedicated Administrator Connection (DAC). It allows an administrator to be able to connect to SQL Server even when normal connections (even their own) are hanging. There are certain restrictions, but at least you can connect and run T-SQL commands such as sp_who, sp_who2, sp_lock and most importantly KILL. Killing a connection will rollback the transaction, free up locked resources and may prevent the need for a restart which is always appreciated, especially in production.
The normal syntax for accessing the DAC on the default instance is: SQLCMD –A from the command-line. You can also use SSMS by entering admin: before the server name e.g. admin:VANCOUVER . You are only allowed one DAC connection at a time and by default, it has to be a local connection. Luckily, a Remote Desktop (RDP) connection counts as local. The DAC on the default instance uses TCP port 1434 by default. Therefore another valid command, using the loopback IP is: SQLCMD –S127.0.0.1,1434 Notice that the –A switch is not necessary in this format.
What if we need to access the DAC on a named instance running on the same machine? That’s simple, as long as the SQL Browser service is running. This service performs the name resolution for SQL Server which is needed because named instances use dynamic port allocation by default. So the command would be, for the named instance SQL2 on the same machine: SQLCMD –SVANCOUVER\SQL2 -A
But what if the SQL Browser service is disabled for security reasons? After all, SQL Browser uses UDP port 1434, the same port that was used by the SQL Slammer worm back in 2003. If SQL Browser is not running, we need to find out which dynamic port is being used by the DAC, since name resolution is not available. This can be done by looking at the ERRORLOG for SQL Server in the named instance’s LOG folder. Don’t worry; even if the SQL Server instance is hanging you should be able to look at this file using the file system. On my named instance, the following message was present at startup “Dedicated admin connection support was established for listening locally on port 1063.”.
So the alternative command in this case would be: SQLCMD –S127.0.0.1,1063
OK, so what if you wanted to change the port number for the named instance DAC to a static port? Well, we know that we can use SQL Configuration Manager to establish a static port for the SQL Server service of a named instance but this is not available for the DAC port. So we have to go directly to the registry and change a key value: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp
Note The “X” in “MSSQL.X” is a number that indicates the directory where the instance is installed for SQL Server 2005 or the instance name for SQL Server 2008.
For more detail see: