Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

6 Answers 6

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

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

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

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

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

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.