So, one of my local databases got stuck in single-user mode. Easy solution? Connect to my instance using SQL Server's Dedicated Admin Connection (aka DAC) and flip the database back to multi-user. Easy! Wait. What?
TITLE: Connect to Database Engine ------------------------------
Cannot connect to ADMIN:localhost\SQL2012.
------------------------------ ADDITIONAL INFORMATION:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 43 - An error occurred while obtaining the dedicated administrator connection (DAC) port. Make sure that SQL Browser is running, or check the error log for the port number) (Microsoft SQL Server, Error: -1)
What is this? More importantly why is this happening? (and how do I fix it?) No fear! The solution is at hand!
If you want to connect to SQL Server via SSMS (SQL Server Management Studio) all you need to do is add the prefix "ADMIN:" at the beginning of the server name.
The trouble begins when you see it's taking longer than normal to connect and you are eventually rewarded with the following error message:
After repeatedly, glancing at the message and closing it, and then trying over and over again and getting the same error (wait, what's that definition of insanity?) I decided to read the error message and luckily one little item caught my eye...
"Make sure that SQL Browser is running..."
To check that SQL Browser is alive and well go into your SQL Server Configuration Manager (or straight to your services if that's how you roll).
Right click on SQL Server Browser and start that thing back up.
Now let's try this one more time...
Failed to connect to server ADMIN: localhost\SQL2012?!? What?
But wait! Don't panic! It turns out there is something amiss with these error messages. Once you click "OK" you'll be greeted with a new query window which upon examination appears to be using the DAC?
Never one to believe what is right in front of my eyes I executed the little script below and found I was indeed connected!
SELECT e.name, s.session_id , s.host_name , s.login_name FROM sys.tcp_endpoints as e JOIN sys.dm_exec_sessions as s ON e.endpoint_id = s.endpoint_id WHERE e.name='Dedicated Admin Connection'
And that my friends was how I spent my morning!