xp_cmdshell is an extended SQL stored proc that allows users to run Windows command prompt commands from within SQL. Sound scary? It might, but is xp_cmdshell really a security risk?
Well a lot of people think so, many DBAs and IT departments will insist that it’s always disabled and many auditors and pen testers will raise it a significant vulnerability if they see it enabled on any of your SQL Servers.
But is it really that much of a security risk?
Before I do go any further, I would like to say that I’m a firm believer of not switching on things unless you’re going to be using them. If you’re not using SSIS, then don’t install it, if you’re not using SSRS then don’t install it, similarly, if you’re not going to be using xp_cmdshell then leave it switched off. But it does add some great functionality to SQL, I use it extensively for managing backups and backup files as well as some other maintenance and monitoring tasks.
Can xp_cmdshell be Misused?
cp_cmdshell allows you to do anything that you could do from a Windows command prompt, from SQL even if they don’t have permissions on the server itself. That is very powerful, just think of the possibilities.
- Perform file system actions, including creating, copying, modifying and deleting files.
- Alter permissions on files and folders.
- Create Windows users and assign them permissions
- Run PowerShell scripts
- and many, many more things.
Now, that’s pretty scary and reading that list I wouldn’t blame you if you now go running off and disable xp_cmdshell. But before you take any kneejerk reactions, lets just think about things for a moment.
xp_cmdshell Run Under the Context and Permissions of the SQL Service Account
That list above is very scary but xp_cmdshell runs under context of the SQL Service account and can only do things that the service account has permissions to do.
This brings us to the first important question and that’s how secure is your service account?
What sort of permissions should your service account have in the first place? Is your service account a local admin or even worse, a domain admin? Yes, I’ve seen that done a load of times and it’s bad news.
You should always be tying the service account down to the minimum permissions possible. You should have a single service account per SQL Server, don’t share accounts across multiple servers. Look at file permissions, the account should only have access to the files that it needs, that’s going to be data files, backups and possibly one or two other locations as required.
You should be locking that service account (and all accounts for that matter) down to the minimum permissions set. If you do that, you’re going to greatly reduce the things that xp_cmdshell can do.
Look at that scary list again and think about how many of those your SQL Service account should be able to do. I’m going to bet it should be a no across the board (expect for the top one and even that should only be on backup locations and possibly in very specific cases, one or two more).
If you’ve got the security around your service account tight, then it’s very unlikely that someone is going to be about to use xp_cmdshell for naughty purposes.
xp_cmdshell is Restricted to Members of sysadmins
The next important point is that by default, only sysadmin accounts are permitted to run xp_cmdshell.
Sysadmins are the gods of your SQL instances and you should be keeping them to an absolute minimum, probably restricted to the DBAs only.
Keep tabs on your sysadmins, keep those in check and enabled or not, xp_cmdshell is already going to be executable only by a very small group of your most trusted users.
Also, don’t forget that sysadmins can switch on xp_cmdshell at any time so what does disabling it really prevent?
But Can’t Agent Jobs Run xp_cmdshell?
This one’s an argument that I’ve seen crop up on the forums all the time when discussions of xp_cmdshell security comes up. SQL Agent jobs can run xp_cmdshell, that sort of bypasses the point above about needing at be a sysadmin.
Well that’s not exactly true, agent jobs will run under the context of the job’s owner which for the most part will the the user that created them. That means, a non sysadmin user can’t create a job to do something that they themselves don’t have permissions to do. This includes running xp_cmdshell.
There is however a way around that. It turns out that you can update the msdb jobs tables directly. That means that it would be simple for a non sysadmin user with write permissions on msdb to alter a job that they’d created to run under higher privileges or insert malicious code into an existing job.
It’s not something that I’d ever really thought about until recently and I think it’s probably something the many people don’t think about. But just like sysadmin accounts, you want to make sure that permissions on MSDB are tied down.
If you don’t then you’ve got a whole new vulnerability and not just one around the misuse of xp_cmdshell.
Is xp_cmdshell Really a Risk or is it Your Security That’s the Problem?
So what do we think now? A quick round up of the facts as I see them.
- xp_cmdshell runs under the context of the SQL service account. So… to mitigate, secure the service account.
- xp_cmdshell can only be run by sysadmin accounts. So… to mitigate, control who has that level of access.
- SQL Agent jobs can potentially be tampered with to run xp_cmdshell. So… to mitigate, control access to MSDB.
All those mitigations are things that you should be doing anyway, so do we still think there’s a problem with xp_cmdshell or is it really your wider security that’s the problem?
If you’re still on the side of the fence that says xp_cmdshell is evil, I’ll just to leave you with one final thought…
I’m a hacker, I’ve managed to get myself sysadmin access and I want to do something nasty with xp_cmdshell but I find that it’s been disabled, what’s the first thing that I’m going to do?
EXECUTE sp_configure 'xp_cmdshell', 1
RECONFIGURE
Thanks for reading and agree or disagree, I hope you found it useful.