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.
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
sp_configure ‘remote admin connections’, 1
RECONFIGURE WITH OVERRIDE
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.
- You don’t belong in the SysAdmin group
- A SysAdmin is already logged onto the server
As always, I hope this helps.