Playing around today, I discovered that I couldn't connect to my local named instance using the dedicated administrator connection (otherwise known as the DAC, but not that DAC):
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)
This is local, so of course the following server-level setting to allow remote admin connections has no effect in this specific scenario:
EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;
GO
I found that I could connect if I enabled trace flag 7806, even though that trace flag is meant for SQL Server Express (as documented here). But I knew the problem had to be deeper than this – Microsoft couldn't have totally broken this feature, right?
It turns out that this symptom only affects *named* instances. I was talking about this with Jonathan Kehayias, who had a default instance, and could connect fine. However he couldn't connect if he explicitly specified the port number, which led him to discover that TCP/IP was disabled.
While this affects named instances of Developer Edition specifically because the TCP/IP protocol is disabled by default, there are other scenarios where this can hurt you if you have named instances and…
- you've [accidentally] deployed Developer Edition to production (naughty naughty);
- you've disabled TCP/IP on any edition; or,
- the SQL Server Browser service isn't running.
To resolve this, make sure that TCP/IP is enabled via the SQL Server Configuration Manager > Network Protocols for <named instance> and make sure that the SQL Server Browser Service is running. You will need to restart SQL Server.
Now, when you are able to connect via the DAC, if you try to connect within Management Studio, you will get this error message:
This error message is benign (and I believe comes from the background IntelliSense connection). You can see from your status bar that you are connected, and you can verify your connection is the DAC connection by dismissing this error message and running a query.
In any case, confirming that you are able to connect via the DAC is an absolutely essential step in your disaster recovery plan. If you can't connect to the DAC, you should plan for one or both of the following actions during your next maintenance window (or earlier, if you can afford a service restart):
- enable TCP/IP
- enable trace flag 7806
In either case, ensure the SQL Server Browser Service is running. Also be sure the server setting to enable remote connections is enabled, since you never know where you might be when you need to access an unresponsive server.
Kendra Little wrote a great blog post about the DAC last year. It's fun to root around and see what you can do with the DAC, and it's really nice to know it's there, but it's also important to know how it might not be able to help you in the event of actual server hardship.
Hi Aaron,
I've had my own issues with this that I wrote about here – http://martincatherall.com/2012/02/01/setting-the-dac-port/
It'd be nice if we could see the port that the DAC was running on easily – as allowing connection on that port through the firewall will also be required for remote connections.
Good article and thanks for introducing me to a new trace flag.
cheers
Martin.
Thanks for the link to the blog post! I have found the DAC to be incredibly useful when things go terribly wrong– and so many people don't know about it. This is really helpful to know for named instances, thanks for posting.