opc.three (11/20/2013)
I can think of ways a non-SA could be setup so they could enable xp_cmdshell without the use of a proxy. Your point?I can think of ways a non-SA could be setup so they could enable xp_cmdshell. What is your point?
I don't believe either of the above is true but if it is, then you've made my point. Turning it off is a futile security effort. I'd also like to know how you think either of the above can actually be accomplished.
Also, you should post you WMI intercept code on this thread, as well. I'm sure other people would be interested in it... especially since you don't actually have anything that will stop the use. I don't mean that sarcastically, either. It may be that someone could help setup a block (although I believe that might have some adverse affects on the operation of things like backups, etc).
And no... the XP that deletes files is good only for backup and certain types of report files. If you know of another XP that can delete text files, I'd sure like to know about it.
You and I started this argument several years ago and you said that you had a "Visceral Fear" about the use of xp_CmdShell. I agree that you do. 😉
In the meantime, I'm going to continue to advocate the use of xp_CmdShell and you're going to continue to advocate against it. I am not, however, going to get into any more long discussions with you about it. I'm simply going to state the you have a visceral fear about it and that it's up to the reader if they want to take on a like fear.
Trust is important, but so are checks, balances and verifications. Anyone who says "you can't stop SA so let's not bother trying to protect our systems" has become apathetic about their system's security.
I can assure you that I'm not apathetic about system security and I strongly resent the implication. Turning off xp_CmdShell does absolutely nothing to increase system security. Nothing. On the other hand, I can very carefully control what the server can see through xp_CmdShell and the SQL Agent. And, there's just as much risk that someone will gain extraordinary privs through an AD mistake that will allow them to use Powershell, SSIS, or even Word/Excel to do extremely grave damage. If you don't have control over AD, then turning off xp_CmdShell isn't going to do you any good anyway.
As far as creating xp_CmdShell use habits go, that's what code reviews by the DBA are for. As a DBA, at least I have control over those. I don't have control over people that use Powershell or SSIS.
--Jeff Moden
Change is inevitable... Change for the better is not.