opc.three (9/4/2012)
Jeff Moden (9/4/2012)
opc.three (9/4/2012)
All sysadmins can execute any commands they wish and those commands will run in the context of the SQL Server service account, i.e. you lose the ability to audit who did what.Consider that all sysadmins can turn on xp_CmdShell at the drop of a hat. 😉
True, but there is an audit trail associated with that, namely an entry into the SQL Server Error Log is made noting that a system configuration was changed. You can also block this through Policy Management, which can be circumvented as well, but it adds an additional barrier. You're assuming all sysadmins are trusted, which is risky. Any barriers that can be placed in the way of a malicious user the better. Having xp_cmdshell enabled is just one more exposure that is better left disabled (IMHO).
While I agree with all of that, I'll also state that someone with "SA" privs has many other unaudited methods for running command-line-level functionality. There's even a simple hack for doing it through OPENQUERY not to mention at least one task in SQL Jobs and SSIS that can do it without necessarily being traced. Besides, having the ability to trace such things is handy but definitely falls into the category of sifting through the manure after the horse has already left the barn.
I think it ironic that if your system is properly locked down, that users can run stored procedures that execute well protected predefined xp_CmdShell functionality without the users being able to see what's in the proc nor even any of the tables never mind being able to run xp_CmdShell directly.
On the "SA" not being trusted thing... the same holds true with any system. Windows, SharePoint, you name it. It's a real shame that it's come to this but if you have computers, someone needs to have deity privs to keep it working or to give someone else temporary privs to keep it working. If trust is really a problem, then you absolutely have to setup the "2 man rule" for all "SA" access where each of the two people only know half the "SA" password. Heh... but then there's those Windows guys... just as surly and untrustworthy.
To be sure, disabling xp_CmdShell and protecting it with another hack like Policy Managemment won't even slow someone with "SA" privs down in an attack.
Shifting gears a bit, I will absolutely agree that having a 3rd party application that requires "SA" privs is just silly and a totally unnecessary risk.
--Jeff Moden
Change is inevitable... Change for the better is not.