Dedicated Administrator Connection

Dedicated Admin Connection(DAC) feature is available in sql 2005 and later. It let’s the administrator connect to the sql server and is very handy, if things are going crazy with the server and nobody could connect to the sql instance. DAC listens on a different port than SQL Server. So, the firewall should not block the DAC port. You can know on which port DAC is listening by looking into the SQL Error Log.

We can also use DAC for other purposes, such restore master database etc. To use DAC, we need to first enable DAC using sp_configure.

sp_configure 'remote Admin Connection',1

So, to use DAC, the server has to be in single user mode. To do this, go to configuration manager and in the properties on the start up parameters, add -m to it and restart the sql server service. Do not start the sql server agent and Full Text Services. Now, in the Management Studio, do not click on the Connect in the Object Explorer. Click on the New query and

Enter Admin:ServerName\InstanceName and hit Connect.(make sure your windows authentication has sysadmin privileges on the server.) This should get you through. Sometimes, you will get an error saying,
Server is in single user mode and only one administrator is allowed to connect to the server .
Double check to make sure sql server agent and any other service(s) are not running and additionally, you can also go through SQL ERROR Log to see which login is connected. If you are using express edition, you have to set trace flag 7806 to use DAC.

The keyword ‘Admin:’ is to tell SQL server to make the connection as DAC Connection. If sql browser service is not running and If we try to make DAC connection as Admin:ServerName\InstanceName, it will not work.In such case, check the error log to find on which port DAC is runnin and call it as ServerName\InstanceName,DACPortNumber(do not add “Admin”).

If SQL browser is running, you can simply call it as Admin:SqlserverName\InstanceName