SQL Back Door – DAC (Dedicated Administrator Connection)

There will be a time or more in your life where SQL Server becomes unresponsive and stops working. You can try to remote desktop into the server with no luck– or take the Rambo route and hard restart the server or have IT do it. This is where DAC (Dedicated Administrator Connection) comes into play. DAC allows one back door SA connection into the SQL Server. To use DAC, you first have to turn it on and here’s how you do it.

GUI Method

First open up SQL Management Studio

Step 1. Right click on your server

Step 2. Click on Facets


Once the Facets windows show up do the following

Step 1. Under the Facet drop-down-list, pick Surface Area Configuration

Step 2. Select RemoteDacEnabled and from the drop-down-list pick True

Step 3. Click OK

That’s all to it

If you prefer the T-SQL method

USE master

GO

sp_configure ‘remote admin connections’, 1

GO

RECONFIGURE WITH OVERRIDE

GO

How to log onto the server using DAC

Step 1: Open up SQL Server Management Studio (SSMS)

Step 2: On the tool bar, click on Database Engine Query


Step 3: In the Server Name box, type in ADMIN: before your server name.

Step 4: Click Connect

Once connected, you can then troubleshoot your SQL Services. If you can’t log in, it means two thing.

  1. You don’t belong in the SysAdmin group
  2. A SysAdmin is already logged onto the server

As always, I hope this helps.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s