Remote SQL Security Concern
I have had a few discussions over the years about whether a Configuration Manager installation should include SQL “on box” or “remote”. The answer is generally “it depends”. This blog post is not going to dig into all of the reasons why you would choose either local or remote SQL…it is designed to highlight one particular security concern with the remote SQL option. Let’s think through several of the underlying components that are necessary for remote SQL to take place along with a few very common scenarios when this is the case.
- Generally a company will choose remote SQL because they want to have a beefy SQL box that is managed by their DBA team. This SQL box will commonly house several SQL databases…not just the Configuration Manager DB. Which means that any disruption on that SQL server has an impact on much more than just Configuration Manager.
- A requirement for remote SQL with Configuration Manager is that the Configuration Manager server’s computer account must be in the local admin group on the SQL server.
- Commonly there will be a number of Configuration Manager administrators that have admin rights on the Configuration Manager server.
- Commonly there will be a number of those same Configuration Manager administrators that do NOT have admin rights on the remote SQL server.
THAT is where the problem rears it’s head. Let’s connect all of the dots…
- Joe Admin is an admin on the Configuration Manager server…but is not an admin on the SQL server.
- The Configuration Manager server’s computer account is an admin on the SQL server.
- Joe Admin has read my article on how to run a command prompt as local system. Uh oh.
- Joe Admin uses psexec to run a command prompt (or SQL Management Studio…or regedit…or services.msc…or disk management…or whatever else) as local system on the Configuration Manager server.
- Joe Admin then connects that app (running in the “user” context of the Configuration Manager server’s computer account) to the SQL server.
- Joe Admin is now able to do anything that the Configuration Manager server’s computer account has rights to do…which is full Administrator rights…ON THE SQL SERVER!!!
- That security you thought you had…well it didn’t work so well.
Is there anything to keep Joe Admin from (either accidentally or maliciously):
- Stopping services?
- Deleting files?
- Rebooting the server?
- Jacking with the registry?
- Installing (either good or bad) software?
- Copying data off of the SQL server?
- etc (I think you get the picture.)
Now…for some people that doesn’t matter. In many smaller installations the same team is managing Configuration Manager and SQL. However…if you are that small…why take on the extra complexity of the remote SQL scenario?
For others it matters big time! I’ve had conversations with customers who cringe at the very idea that some random Configuration Manager admin could possibly gain full rights to the SQL server that other business critical databases are stored on.
4 Comments »
Leave a comment
Subscribe
Recent Comments
-
The Realm of the Verbal Processor 
I think a viable solution to the ConfigMgr admin having permissions via psexec can be somewhat alleviated by selectively granting admin permissions to the ConfigMgr server. It’s even possible to limit permissions completely (theoretically) by granting RDP access, permissions to log folders, and within the hierarchy itself (even full admin of ConfigMgr) without granting them admin rights to that server.
I agree that the concern can be minimized to a great degree but not completely eliminated for most companies. Many companies who are implementing a remote sql option for Configuration Manager have a specific SQL team responsible for SQL servers. In most instances the team that should have full admin rights on the Configuration Manager servers (and therefore be able to log in to them with admin rights) are not all members of the SQL team. Maybe some of them are…but not all.
I’m not saying that remote sql is the wrong answer for all companies. I’m simply saying that this is a security concern that must be identified and addressed on a case by case basis.
BTW…looking forward to seeing you when you get back from being deployed Tim! Thanks for your service to our country!
I know what you’re saying – I’m just suggesting less people need to be admins of the ConfigMgr server than most people think. By the way, I am now back in MN as of last Friday. :) I have about a month or so off, where I’ll be catching back up with the family and redoing the entire exterior of my house.
Welcome back!!! And I totally agree with less people NEEDING to be admins on the server…totally true.