Error when trying to use DAC connection in SQL Server


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.

DAC SSMS connection error 1

 

The trouble begins when you see it’s taking longer than normal to connect and you are eventually rewarded with the following error message:

 

DAC SSMS connection error 2

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…

DAC SSMS connection error 3

 

“Make sure that SQL Browser is running…”

Ohhhhhh…

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).

 

DAC SSMS connection error 4

And find SQL Server Browser. Whoops! Looks like someone was a bit ambitious in disabling services in an effort to save every last little bit of memory and processing power.
DAC SSMS connection error 5

 

Right click on SQL Server Browser and start that thing back up.

Now let’s try this one more time…

DAC SSMS connection error 1

 

And…

DAC SSMS connection error 6

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?

DAC SSMS connection error 7

 

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'

Hooray!

And that my friends was how I spent my morning!

 

 


Leave a Reply

Your email address will not be published.