Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

A SQL developer on a project I am working on has asked whether it would be possible to enable xp_cmdshell on the production database as it is easier to export CSV files using xp_cmdshell than to write an SSIS package to do the same.

Enabling xp_cmdshell sounds like a security nightmare, and something that should definitely not be done.

What are the recommendations / best practices around this?

share|improve this question
1  
I'm going to close this as there's some useful comment, but one thing I would like to understand is when a best practice becomes dogma, rather than common sense. Yes, enabling xp_cmdshell is clearly a security issue, but in our scenario, we have the following as standard: multiple firewalls between public internet and database, managed IPS / IDS systems, regular security audits, dedicated AD account used to run the procedure that would use xp_cmdshell, with access to just that procedure and the relevant ACLs. We store no financial data. Is it still that bad? Just asking ;-) – Hugo Rodger-Brown Sep 15 '10 at 21:29
up vote 3 down vote accepted

I wouldn't do it. Especially if you plan to get any branding from Microsoft as a developer partner. We have our products certified by Microsoft, and their application checking tools will check to see if xp_cmdshell is enabled or not.

share|improve this answer

As long as you've properly sanitized any areas where code comes into SQL Server and nobody has any permissions they don't need your risk should be minimal. There's still a risk of course so I'd only enable it if it was necessary or will vastly improve the current process. If it's not too much of a headache to write the SSIS packages you're better off going without xp_cmdshell.

share|improve this answer

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

Just because it's "easier" does not mean it's the right thing to do.

I would push back and get the developer to use a SSIS package.

However, examine how secure your system needs to be. Are you a bank? Then xp_cmdshell is a no no.

If your production system is an in-house product and your users are generally trustworthy then you can enable this.

share|improve this answer

I would recommend not enabling this, but if it becomes a business critical issue and gets thrown upon you I would set the process up so that you only enable xp_cmdshell for the brief period that you need it for the import. This can be scripted out using sp_configure and and turned on and off in job steps or a T-SQL procedure.

share|improve this answer

Don't do it. xp_cmdshell is the hacker's delight tool of choice to elevate an attack from SQL Injection to 'I own ur network and I can read ur CEO's mail'. Once enabled, developers will keep abusing it to use it for all sorts of weird cases when there are better alternatives.

And, besides, to export CSV there are plenty of alternatives that are more safe, more secure and easier to implement:

  • SSIS
  • External dedicated process launched by SQL Agent
  • External dedicated service running on its own
  • CLR procedures with External access

And last but not least, how the heck is easier to export CSV using xp_cmdshell than using SSIS??

share|improve this answer

I just asked my DBA about this because he disabled an extremely useful function I use for troubleshooting. After doing a lot of research and reviewing answers from people significantly more experienced than me, I tend to lean toward the middle on the issue of disabling xp_cmdshell.

One one hand it's ridiculous to be absolute on the idea of disabling command shell as if it's a lit stick of dynamite. However on the flip side I think it would be completely reckless not to respect the security risks of using xp_cmdshell.

That being said I look at xp_cmdshell like a loaded weapon. If you know the danger proceed with caution and if you don't, then get educated BEFORE you use it in a production environment, particularly if it's part of an application. For the most part, using xp_cmdshell in an application "should" probably be a last resort option.

However for me personally, using it to troubleshoot production jobs (on an adHoc basis) in a secure development environment, I just don't see the "security risk". The code isn't being stored. My account is a personal account and it does not get used in any applications public or private. That's my two cents...

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.