opc.three (6/12/2013)
In my estimation, and from seeing what developers typically produce and have left behind when left to their own devices when using it, xp_cmdshell guides developers into, practically promotes, very poor implementations. This is primarily due to the intrinsic properties of it as a piece of software. For these reasons, I could never, ever, recommend using it, ever, for anything, ever.
My turn to say that's a fair point. I'll also say the same thing of many developers that use any tool included but certainly not limited to PowerShell. Look at some of the articles that fall into the category of "Look What I Can Do with PowerShell" and I believe you'll agree. It's not just xp_CmdShell that provides the lure to the side of idiocy.
As you might guess, I'll continue to disagree about it being a "shitty" tool from a security perspective. You can do just as much damage with SSIS that goes out to scripts or uses WMI blocks.
No you can't Jeff. You can lock an SSIS Package execution into running under a service account that is not comingled with the SQL Server service account. You cannot do that with xp_cmdshell. It's a blunt tool that paints you into a corner, namely a corner of running everything under one account where a sysadmin member gets to potentially elevate and definitely obfuscate their identity. For goodness sake man, you have to see this by now. Just accept it already.
Fine. Show me an absolutely guaranteed way to keep an "SA" attacker from getting to the command line in SQL Server (not just through xp_CmdShell) and perhaps I'll reconsider it. I say "perhaps" because I actually do like xp_CmdShell and the fact that I don't have to go anywhere near an SSIS installation to do ETL or my job as a DBA.
You've apparently had as many bad experiences with crappy developers that use xp_CmdShell as I've had with crappy developers that write scripts for SSIS and the like. You and I have had identical problems, just with different products. I guess that's why that even though I totally disagree with your stance on xp_CmdShell and you totally disagree with my stance on other tools, we haven't actually tried to remove each other's heads... yet. 😀
Security-wise it's awful. Design-wise it's awful. Stability-wise it's awful. It's shitty man, and it ain't that serious to go after your head...unless you meant figuratively to try to win over your mind on the matter. That my friend has become a passive hobby of sorts for me at this point 😉
And I disagree with all of that especially when I've seen some of the alternatives that people have come up with for what should be very simple tasks.
I guess we'll have to continue to continue to disagree.
Getting back to the subject at hand, I haven't actually written any code on this thread that uses xp_CmdShell because it's not needed.
--Jeff Moden
Change is inevitable... Change for the better is not.