Take the 2-minute tour ×
Information Security Stack Exchange is a question and answer site for Information security professionals. It's 100% free, no registration required.

Can 'xp_cmdshell' ever be used safely within a stored proc and are there any situations for which there really is no other option? In other words, should it's useage within a stored proc always be flagged as a security issues (as is advised by a well know source code analyzer)?

Put differently, would you agree with the following statement (direct quote)?

"The function xp_cmdshell cannot be used safely. It should not be used."

share|improve this question
3  
For those unfamiliar "xp_cmdshell", it is "an extended stored procedure provided by Microsoft and stored in the master database. This procedure allows you to issue operating system commands directly to the Windows command shell via T-SQL code." Yikes!! –  nealmcb Mar 26 '11 at 21:47
2  
No, I don't agree. It absolutely can be used safely using at least 2 different methods that I know of and one of them is pretty darned easy to setup. The problem is that BOL doesn't actually tell you how to do it. –  Jeff Moden Jun 25 '12 at 2:07

6 Answers 6

up vote 7 down vote accepted

It is always a risk. It should always be reviewed. It can be properly mitigated.

There are legitimate uses, sometimes necessities, but watch your input closely!

share|improve this answer
    
What of the problem that there is no way to limit a user to a predefined group of operations and so therefore any privilege grant allows the user to execute any command string, i.e. it doesn't allow granularity of privilage or stated different any control becomes complete control? –  TobyS Mar 25 '11 at 16:08
1  
Now, SQL Server is a secondary for me, so tell me if I'm wrong: Granting a user execute permissions on the stored procedure allows that procedure to be run by them. It does not allow them to modify it, and it doesn't entail granting permissions for everything used by that stored procedure. If they must be granted xp_cmdshell permissions to run a stored procedure that contains it, then you've definitely got an issue. I'm reasonably confident that is not required -- the procedure should not be owned by the regular user. –  Jeff Ferland Mar 25 '11 at 16:23
2  
@TobyS... thats the problem with those sorts of statements. Short answer: xp_cmdshell is evil. Long answer: it depends. ;) –  Steve Mar 25 '11 at 20:32
5  
@SteveS - current version of MSSQL allow you to set a different OS account to run xp_cmdshell commands under. –  AviD Mar 26 '11 at 21:08
2  
@JeffFerland... you wrote "I'm reasonably confident that is not required -- the procedure should not be owned by the regular user." That's 100% correct. There's no need for the user to even know if a table is involved never mind xp_CmdShell. And yes, it's quite easy to setup. –  Jeff Moden Jun 25 '12 at 2:05

Turning off xp_CmdShell is a bit like putting a veil over rotting meat. It brings a false sense of security to the table and the flies can still get at the meat. Allow me to explain.

Who can use xp_CmdShell? That's right. Only people/app logins with "SA" privs or people that you made the horrible mistake of granting a proxy to can use it.

Next question. If you have xp_CmdShell turned off, who are the only people that can turn it back on? Correct again! Only people/apps with "SA" privs can turn it back on.

So, what's the real issue with xp_CmdShell being a security risk? The answer is xp_CmdShell is NOT a security risk. Poor security is the only security risk. If a hacker or an malicious internal user get's into the system with "SA" privs, then they can turn xp_CmdShell on in momements. Yeah, that action gets logged but that only provides documented testimony that security was grossly lacking to begin with.

Turning xp_CmdShell does nothing for security except to provide a chance for that part of a hackers code to turn it back on to run.

I'll say it again. xp_CmdShell is not a security risk. Only bad security is a security risk. Fix your security and then turn on xp_CmdShell. It's a wonderful tool and you're missing out on it because of bad security practices and myth.

share|improve this answer
    
I completely agree with you, Jeff. An attacker who gained syadmin privileges could create a SQL Agent job owned by sa with a CmdExec jobstep and achieve the same as xp_cmdshell. He could also create a CLR stored procedure that starts a process. How would that be different from xp_cmdshell? Securing the server properly is the only way to prevent security risks. –  spaghettidba Feb 11 at 9:45

To emphasize jl01's answer (which I gave a +1 to)...

Oddly enough, a good measure of a properly secured and safe SQL Server is to actually have xp_CmdShell enabled. What I mean by that is that if your system is secure enough, you shouldn't have to worry about trivial matters like xp_CmdShell being enabled AND USED.

Especially as of SQL Server 2005, there is virtually no reason why any users other than DBA's should have privs greater than PUBLIC privs and EXECUTE privs on stored procedures in a properly locked down system. In fact, implemented correctly, users with PUBLIC privs should be able to execute a stored procedure that contains calls to xp_CmdShell without being able to run xp_CmdShell directly themselves.

I think it ironic that MS created the command shell proxy to allow low priv users to run xp_CmdShell directly when they shouldn't even be able to see a table.

share|improve this answer

What kind of security is being used in your SQL Server environment, Mixed or Integrated (Windows)? How many are in the sysadmin SQL Server role? MS best practices call for Integrated authentication (no sa login, no SQL logins) and only two in the sysadmin SQL Server role. I submit that following these best practices greatly mitigates one's exposure. Further, xp_cmdshell (pre sqlcmd mode and pre-Powershell) gives the ability to copy transaction log files from the production server to the DR server hundreds of miles away from within a scheduled SQL Agent job. No evil here but as the one poster put, "it depends".

share|improve this answer

"With great power comes great responsibility." That being said, I think xp_cmdshell is one of the worst security train wreaks to make it out of Redmond.

share|improve this answer
    
I know it's an old question, but I really have to say it: I'm surprised 3 people took your trolling seriously enough to upvote your answer. –  spaghettidba Feb 11 at 9:38
    
@spaghettidba I am dead serious, as a pentester xp_cmdshell() is a god send. What makes it so bad is that no one at Microsoft even thinks to fix this issue. I'm very happy to encounter Microsoft products and applications built on the Microsoft platform because they are weak. Fun fact, my first 0-day was in a Microsoft security product when I was 16, which was more than a decade ago. –  Rook Feb 14 at 15:28

I think "it should not be used" is probably pretty good advice. That's not a categorical "It's always insecure", but rather a recognition that xp_cmdshell is dangerous and any use of it is grounds for concern and careful scrutiny.

And even if you think you know how to avoid the security risks, xp_cmdshell is still probably not the best tool to use. Odds are that there is a better solution (one which also, fortuitously happens to be less risky).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.