Executive Summary: Many companies are worried that enabling xp_cmdshell is a security hole. In fact, xp_cmdshell is limited to SQL Server administrators by default. In order for a non-admin user to use xp_cmdshell, an administrator must specifically grant permissions to them. (Note that SQL Server administrators have the power to turn on and use xp_cmdshell anyway!) In short: xp_cmdshell is not a security hole.
Let’s begin.
I’ve been hearing it more and more the past year.
XP_cmdshell should always be turned off.
Whatever you do, don’t turn on XP_cmdshell!
We can’t do that, it requires XP_cmdshell!
You’ll fail your audit if XP_cmdshell is turned on.
And all the other variations.
And I suppose I’ve been hearing it more and more lately because Minion Reindex and Minion Backup require it.
However, I’ll tell you I’m getting pretty tired of hearing it so true to my blog I’m going to rant.
xp_cmdshell was a problem – in the 1990s
Xp_cmdshell has been around forever. And way back in the day, like 15-20 years ago, it was installed wide open to the public. This is where the problem started. This was back in the day when SQL’s GUI allowed way too many people who had no idea what they were doing to create and manage DBs. That ease of use was a huge part of SQL Server taking hold in the industry. However, with the product being that easy to use, a lot of these untrained DBAs had no idea xp_cmdshell was even there, so their instance was completely vulnerable and they didn’t even know it. Honestly, this was Microsoft’s fault. They should never have packaged up something that dangerous completely open to the public. But you know what, back then they were also installing SA with a NULL password by default too. And Oracle had their scott\tiger username\password combo, so MS wasn’t the only one doing dumb security back then.
However, now xp_cmdshell comes turned off and when you enable it, it’s not open to public anymore. So seriously, what are you still afraid of? I understand that you used to be scared of it because there was no way to lock it down back then. In fact, Microsoft didn’t provide a way to lock down xp_cmdshell until somewhere in the neighborhood of version 4.2. So back when it was open to public I can see how writing a DENY statement would be really taxing to you as a DBA.
But these days you don’t have any excuses. You have to go out of your way to open it up to public. Xp_cmdshell is still really useful and I’m personally able to create many excellent solutions using it¦ things that would be much more difficult otherwise. And do you know what I tell people who tell me how dangerous it is? I ask them why they don’t lock it down.
“Dangerous” SQL features
Think about it: there are many dangerous features in SQL. And they’re all kept in check by controlling permissions to them. You don’t see anyone screaming that those other features should be allowed on the box because they just say, we use it but we keep its usage controlled pretty tightly. So why doesn’t that apply to xp_cmdshell? Do you think that SQL all of a sudden forgets how to deny execute perms when that gets called? Do you think that SQL honors all security except that one? Do you think xp_cmdshell is powerful enough to override SQL security and just do what it wants anyway?
Of course not. So what are you afraid of?
The truth is that xp_cmdshell can do a lot, and in the wrong hands it can make a royal mess of things. Then again so can DELETE and UPDATE. So can SHUTDOWN. So can CLR. So can DROP DATABASE. So can Dynamic SQL. And you don’t see anyone saying that all of those should never be allowed on any server for any reason. And I would honestly venture to say that Dynamic SQL has been the cause of far more security breaches than xp_cmdshell ever has. I don’t have any numbers to back me up, but I bet if you look at the number of security issues caused by xp_cmdshell, they’re far outweighed by other features.
Control SQL Server permissions
It’s not like people have no way to get that functionality just because xp_cmdshell is disabled. There are still cmdline job steps and cmdline SSIS tasks. And of course, you’ve got CLR. All of which can be just as dangerous as xp_cmdshell yet they run on systems all the time. And I know what you’re thinking:
But Sean, we control those through permissions so they can’t do anything really bad. Yeah, so you’re making my point for me. But do you think that if an SSIS guy wanted to do something bad to your box that he couldn’t find a way if he weren’t locked down? Of course he could.
The cool thing about the cmdline task in Agent jobs is that they can be run via proxy. You can setup a proxy user to run that step under so that its Windows perms are limited and it can’t run haywire. You wanna hear a secret? There’s a built-in proxy mechanism for xp_cmdshell hell too. I could tell you how to do it, but DatabaseJournal has already done such a fine job. So here’s the link to setting up the credential.
Like most features: secure xp_cmdshell and use
I don’t want you to just turn on xp_cmdshell on all of your systems for no reason. But I don’t want you to completely rule it out as a solution just because you’re afraid of it. Tell your Windows admins who are afraid of it to mind their own business and stick to what they know. You’re a DBA and it’s time for you to take back your SQL instances. Lock them down. Don’t be afraid to use cool functionality because so many people refused to read the documentation 20 years ago. You know better now. So go out there and do the right thing. Lockdown xp_cmdshell, but use it.