K. Brian Kelley (4/2/2014)
Disabling xp_cmdshell is not a false sense of securityThere are several good reasons to do this.
1) They prevent against a mistake by someone with privileges who just doesn't think. For instance, if you shouldn't be running a command shell query from a production server but it's okay from dev, having it turned off in prod will remind them they are on the prod box. We've all been guilty of running a query against the wrong server if we've been a DBA for any length of time.
In that case, you should also make it so that DBAs only have read privs. 😉 There's no difference here between running a bad xp_CmdShell command and a bad TRUNCATE or a DELETE with no WHERE clause.
2) It stops script kiddies and those who don't know much about SQL Server. To get xp_cmdshell back on involves knowing sp_configure and RECONFIGURE. If an attacker only knows the rudiments of how to attack a SQL Server, then this stops their attempt. Yes, they can then go search on the Internet for the answer, but it gives you something they don't want to give up: time.
No it doesn't. Only "SAs" can run or turn xp_CmdShell on unless one has made the horrible mistake of giving casual user privs to run it directly. Ostensibly, only DBAs have "SA" privs and they supposedly aren't "kiddies". An attacker that get's into your server as "SA" is going to hit paydirt whether they use xp_CmdShell or not. An attacker that successfully gets in as "SA" is also very likely using attack software to do such a thing and all of the attack software that I've seen turns on xp_CmdShell as soon as it makes a connection. You're statement is kind of proof that turning it off brings a false sense of security.
3) It can cause a trip against a functioning IDS/IPS. A lot of IDS/IPS will flag the string xp_cmdshell. In order to turn on xp_cmdshell when it has been turned off requires an attacker to execute sp_configure 'xp_cmdshell', 1. Guess what? That triggers the hit. So does the initial xp_cmdshell use, which will fail. It may only give you a few seconds notice, but if the attacker has to search for the answer, your IDS/IPS just tripped, your security folks should get alerted, and you know you're under attack. If it's the case like in #2, that may give you just enough time to break up the attack.
You and I both know that's probably not going to do a thing for anyone on most systems except provide a log that will be written testament that the security team failed to prevent an attack. An attacker doesn't need xp_CmdShell to make a self deleting job that uses an EXEC task. The attack vector the OP described is proof of that (didn't use xp_CmdShell). Having xp_CmdShell turned off will give the minor advantage that you say but I doubt it'll give most DBAs the time to do anything, never mind get an alert. The key is to keep attackers out and, if they do get in, to prevent them from attaining "SA" privs. Anything else is likely to provide a false sense of security that leads people into more lax methods.
It doesn't do a lot.
I'll say. An attacker that is actually smart enough to get in with "SA" privs is absolutely going to know how to turn on xp_CmdShell. But, again, he doesn't even need xp_CmdShell if he can get in with "SA" privs. If he decides to use xp_CmdShell, it'll take his software less than 3ms to turn it on. By the time people react, he'll already have his payload and be gone.
However, to say it provides a false sense of security is incorrect. It's just another way to lay a tripwire to try and detect an attacker. Of course, for #2 and #3, if your organization doesn't have an IDS/IPS set up properly, it's all a moot point.
I guess we'll have to agree to disagree here, ol' friend. There are more cottage and accidental DBAs that think that turning off xp_CmdShell will provide something more than the 3ms "tripwire" you're talking about than there ever was.
--Jeff Moden
Change is inevitable... Change for the better is not.