Remote Dedicated Admin Connection
Blitz Result: Remote DAC Disabled
WHAT IS the DAC?
DAC is an acronym for Dedicated Admin Connection: a tool for connecting to SQL Server so you can run basic troubleshooting queries in cases of serious performance problems.
WHAT’s THE DIFFERENCE BETWEEN the DAC AND REMOTE DAC?
DAC and Remote DAC are the same feature — the difference is in where you connect to it from. The DAC is already enabled for local connections. In order to use that from a remote machine, however, we need to enable remote connections.
In either case, only sysadmins can use it, and only one connection can be made at a time.
SOUNDS GOOD. HOW DO I ENABLE THE DAC FOR REMOTE CONNECTIONS?
It’s easy- you just change one server option using the script below. This doesn’t require an outage.
How to Enable the Remote DAC
Enabling the DAC for remote connections is simple, but there’s no checkbox or GUI for it — you have to enable it using a script:
/* Check for any pending configurations before you start. */
/* Reconfigure applies to EVERYTHING pending. */
WHERE value <> value_in_use;
EXEC sp_configure 'remote admin connections', 1;
What about Firewall Ports?
There’s always a catch. You many need to get firewall ports opened as well, depending on your environment and where you intend to connect from. This will probably be port 1434, but that will vary depending on your configuration. (Books Online has got your back: read more in the “DAC Port” section here.)
NOW THAT REMOTE DAC IS ENABLED, HOW DO I USE IT?
You can connect to the DAC using the command line. Use the “-A” option with SQLCMD.exe.
You can also connect in Management Studio itself. You do this by prefixing the instance name you are connecting to with “Admin:”.
Note: Object Explorer can’t connect to the DAC. If you open SSMS and have Object Explorer connecting by default, the first connection prompt you see with be for that. If you try to tell that to connect to the DAC, it’ll fail.
Learn more about how to use the Remote DAC: How to Use SQL Server’s Dedicated Admin Connection (DAC) – Video