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.
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.
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.