Firewalls and what that can mean for your DAC

Firewalls and what that can mean for your DAC

We all know (and in some cases love) the Dedicated Admin Connection (DAC). In summary, a DAC allows you to access a SQLServer that has become unresponsive to all other connections for whatever reason. It also lets you investigate the problem while it persists, so you don\’t lose all that lovely diagnostic information when you restart the service.

Which is nice.

I like to enable the DAC on all the SQL servers I\’ve got (and also use Mixed Mode Authentication, but that\’s just me, I guess) so I\’ve got a backdoor into the server in case of major emergency. But in some cases, having a DAC that\’s remotely accessable will do you no good at all. And that\’s thanks to those lovely securiity guys and their best friends, the network guys.

You see, in some companies that have an Internet-facing presence, the security guys don\’t want any nasty stuff getting onto the internal network, so they ask the network guys to lock down the firewalls between the business and the DMZ to allow as few ports as possible. If you\’ve ever put SQL Server in the DMZ, you\’ll know what this means – change the TCP port that SQL Server is listening on from the default of 1433 to one of your own choice, and you\’re good to go. The network guys then open that port over the filewall, and everything\’s rosy.

Except in the case of your DAC.

On a DEFAULT instance of SQLServer, the DAC is ALWAYS on TCP 1434 (not to be confused with UDP 1434, of course). But your best practices (and probably the security guys) tell you never to install a default instance, and use named instances exclusively.

In the case of a named instance, the TCP port for the DAC is dynamically assigned on first use of the DAC. This means that it hops about – one instance its on 1568, the next instance it\’s on 53226 (note: I don\’t know if these are reserved ports for anything else, I\’m just using numbers that illustrate the point.)

There\’s a KB article on this on MSDN, along with some other useful goodness: http://msdn.microsoft.com/en-us/library/ms189595.aspx

On an internal network, this is not a problem. An attempt to connect to a DAC will try to use TCP 1434, and if unsuccessful, will ask the SQL Browser service where it is. OK, so it\’s a bit more complex than that, but you get the idea.

But up in the DMZ, your connections are dictated by the firewall rules and the ports that are open to you. The network guys won\’t thank you for asking them to open a new port every time SQL restarts just so you can have your backdoor open. And that effectively means you can\’t have a remote DAC in the DMZ 😦

If you can get onto the server that\’s having a problem up there, you can always use the local loopback address to get to your DAC (if your network is the problem) once you\’ve found which port the DAC is on (which is in the SQL Error Log as one of the entries on start-up). However, if your problem is authentication, you can…. Hang on… Something\’s wrong.

You\’re using Windows Authentication, right ? And your problem is authentication ? OK, no problem, there\’s a few hacks about restarting SQL so you can get in using BUILTIN\\Administrators, so…. Hang on…. Something\’s wrong….

You want to get into the DAC to look at all your diagnostic information before you restart the service….

And THAT\’s why I always use Mixed Mode Authentication – it\’s a cat-flap in the backdoor, so to speak 🙂

Just a thought.

Back soon…