up vote 1 down vote favorite
share [g+] share [fb]

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?

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

5 Answers

up vote 2 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.

link|improve this answer
feedback
up vote 1 down vote

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.

link|improve this answer
feedback
up vote 0 down vote

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.

link|improve this answer
feedback
up vote 0 down vote

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.

link|improve this answer
feedback
up vote 0 down vote

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

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

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