Unable to access SQL Server Management Studio using the DAC (Dedicated Administrator Connection)

• bowchung

Problem

Unable to access SQL Server Management Studio using the DAC (Dedicated Administrator Connection)

Firstly, why would you need it?

TechNet states that the DAC allows an administrator to access a running instance of SQL Server Database Engine to troubleshoot problems on the server—even when the server is unresponsive to other client connections.

It basically is a ‘secret’ back door connection. SQL Server keeps a single thread processor resource available just in case the situation should occur where you’re locked out. Read more about it here.

I have literally heard at least 3 DBAs mention that at some point in their careers, this account has saved their butt.

So I thought I would investigate…

Key Points to using DAC

Key points to remember if you want to be prepared in the case of disasters that would require you to use this account.

  • DAC must have been enabled on the server beforehand. You also need this if you are remote connecting to the Database Engine. mssqltips recommends you do this by using:
Use master
 GO
 /* 0 = Allow Local Connection, 1 = Allow Remote Connections*/
 sp_configure 'remote admin connections', 1
 GO
 RECONFIGURE
 GO
  • If you think that this is a bit of a security risk, there is a way to check exactly who has been using the DAC account. Kendra Little covers it here.

Potential Errors

So I tried the steps advised by TechNet, I attempted to access SSMS using the following credential:

Admin:[SERVERNAME_HERE]

The error I got was:

Dedicated administrator connections are not supported via SSMS as it establishes multiple connections by design. (Microsoft.SqlServer.Management.SqlStudio.Explorer)

I soon learnt that perhaps it was because it may be connecting Object Explorer which is not supported…i haven’t actually found any further relevant documentation on this.

However, I ditched that idea and instead found other articles suggesting to use sqlcmd. I opened an Admin cmd terminal and tried accessing the Database Engine using the DAC account via the following code, as suggested here:

sqlcmd –S [SERVERNAME_HERE] –U [USERNAME_HERE] -P [PASSWORD_HERE] –A

After several attempts, I kept getting the following error:

Password: Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login failed for user '[USERNAME_HERE'..

Resolution

This is what I finally did after some frustration.

I gave the sa account a more complex password and using that instead via the sqlcmd.

Leave a comment