SQL Homework – May 2019 – SQLCMD and the DAC
5May 1, 2019 by Kenneth Fisher
Yes, it’s that time again. Time to do your homework. This month your homework is to set up the DAC (dedicated admin connection) for remote access and practice with it using SQLCMD.
For those of you that don’t know the DAC is a single user connection that has a dedicated set of resources. What this means is that when your instance is sluggish or even unresponsive the DAC may very well be able to connect and you can run diagnostic queries and possibly even fix things. However, in order to do that you need practice!
- Turn on remote DAC connections.
- Learn why SSMS can be somewhat problematic when using the DAC.
- Use SQLCMD to connect using the DAC.
- Run a few diagnostic queries.
- What’s trying to run right now?
- What’s the memory usage on the instance?
- What queries are using the most memory?
- Same with CPU.
- Anything else you can think of.
I want to point out that this one is particularly important. You won’t use the DAC often but if you need it your instance probably has some pretty significant issues. Given that it’s a single connection you won’t be able to use a lot of the tools you are used to. For most of us, this is going to require practice. Possibly a lot of it. And you really don’t want to take time to practice while your users are screaming about an outage.
SQL Studies 
Great article, Kenneth. I was surprised to see Microsoft recommending to disable DAC access from the network. Seems to be the entire point of having it on is so you can access SWL Server when the server itself is unresponsive! I wrote a post about it at https://www.sqlserverscience.com/security/vulnerability-assessments-recommends-disabling-remote-admin-connections/
*SQL obvs, not SWL. Damn mobile.
Great post. I wasn’t aware of that. I think I’m with you on this one. The primary benefit of the DAC is that it has dedicated resources. I can’t think of how someone would connect using the DAC and cause more problems than they would just connecting normally. Not to mention the fact that if my SQL Instance is unresponsive and I need the DAC, I’m probably not going to be able to remote to the server easily anyway.
Cheers… it seems weird that they’d recommend having the remote DAC disabled. Aside from looking at the odd undocumented feature, literally the only times I’ve relied on it is when the server is 100% CPU-bound, and it’s the *only* thing that will respond!
Exactly! And to the best of my knowledge the only thing the DAC can do that a regular account can’t is look at the system tables. Which you can’t edit anyway.