What is DAC?
DAC is short for Dedicated Administrator connection to SQL Server – it is used for troubleshoooting by those who are SAs in for exceptional situations where a regular login will not meet the needs.

When is DAC typically needed?
In situations that require serious troubleshooting by an administrator.

How do I connect to SQL Server using DAC?
I decided to figure this out the hard way instead of googling for solutions. I only knew that i had to use admin:mylogin on ssms (I picked the ssms option over SQLCMD which is also another way to try) and decided to try it. I clicked on object explorer, prefixed my login with ‘Admin:’ and connected. After all, all connections go via Object Explorer right? No…I was faced with error as below –

I was using the wrong method to connect – the right way is to use ‘Database Engine’ query icon, the one to the right of ‘New Query’ on SSMS toolbar. I tried again using that method (it gives you the same login window as object explorer did). Below was the next error.

Now I realized that I had RDP-ed into the machine and did not have remote admin connections enabled. So the next step  was enabling those connections, as below. This configuration is  apparently needed on clustered servers also – and may not be needed if you are on a local machine.

EXEC sp_configure ‘remote admin connections’, 1;
GO
RECONFIGURE
GO

Now I was done and connected fine.

What can I do with DAC?
I was able to do a sp_who2, kill a runaway process, and run some dmv queries. I was not able to do a backup/restore.  This link from MSDN has a list of diagnostic and troubleshooting queries that can be run (various DBCC commands and querying DMVs). It is important to remember that it not the best option to run regular maintenence – only DBCC commands that relate to troubleshooting specific and rare issues.

What is the database I connect to with DAC?
It is the default database associated with the login. You can access any database after connecting with the use statement. To connect directly to the master you can use the sqlcmd line sqlcmd –A –d master (it assumes your windows login has access to master as SA).

What are some other miscellaneous facts to know regarding DAC?
It has limited resources, so should always be used for lightweight operations.
It cannot make use of parellelism.
The port number that DAC listens on is written to the error log during start up and is 1434 by default.
You can have only one DAC connection open at a time.(Even if the
other admin is a different person).

There is a great post by my friend and SQL Server MVP Kendra Little’s blog on the same subject – which  is also a worthy read. She also gives us an interesting DMV based query to find out if there are other DAC users if you are not allowed to get in.

Knowing about DAC might save your bacon in unexpected situations.I consider enabling it to be added to my list of standards for configuring a server.