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?
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.
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.
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.
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.
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.
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:
And last but not least, how the heck is easier to export CSV using xp_cmdshell than using SSIS??
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...
