I recently came cross another great reason to enable the remote admin connections server configuration on SQL server.

If you don’t know what the DAC is I’m not going to go into detail here so check out these posts here, here and here.

The DAC is great tool and a light for any DBA in dark places. By default it is enabled from the host that is running the SQL service. To enable remote access to the DAC you need to enable remote admin connections.

exec sp_configure 'remote admin connections',1
go
reconfigure
go

There’s a bunch of compelling reasons to enable this setting not least of which is the fact that if a server is in such bad way that you need to access it using the DAC – there’s a good chance you either wont be able to or wont want to access the box using an RDP session.

But the best reason of all is that – if you have SQL server clustered instances the DAC will not work locally! Not from the virtual IP, not from the host node, not using IP addresses and specific ports. No how, no way.

Enabling the remote admin connections setting is dynamic, no service restart is required, and a dynamic TCP port is assigned as soon as the reconfigure is run. If you do have any security or firewall restrictions it is possible to set a static port for the DAC but this requires a registry edit and a service restart.

Edit this key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\SuperSocketNetLib\AdminConnection\Tcp

Note The “X” in “MSSQL.X” is a number that indicates the directory where the instance is installed for SQL Server 2005 or the instance name for SQL Server 2008. (from http://support.microsoft.com/kb/823938/en-us)

And restart the service.

Often when you use the DAC you’ll use a single threaded command line tool like SQLCMD but it is possible to use management studio.

You can’t connect using the object explorer because that takes more than one connection. Remember the DAC is a single dedicated scheduler – it is only possible to have one DAC connection at a time. But you can open a query window in SSMS, right click and choose connect (or change connection if the new query window has already connected). Connect to ‘admin:[servername]\[instance name]’.

Management studio will throw this error:

But you can see at the bottom that the connection has actually worked. I suspect that the error is probably SSMS trying to open another background connection for something.