Jeff Moden (6/12/2013)
Ok, Orlando... I'd really be interested in how that bit of fine code could be modified to reject usage of cmd.exe
It cannot. I was only showing that it is trivial to start listening to Windows events. Intercepting the process creation is a little more involved, but possible. Think anti-virus software. Every file added or modified and every process created on a system running anti-virus software goes through some heuristic checks before the anti-virus software decides whether that data or action is going to be allowed to continue unfettered. Same principle here.
Now I am no Windows programmer, don't want to become one, but I know enough about WMI to be a little dangerous here. From some light reading online it may take a little more than WMI even. Like I said, I am not a Windows programmer.
The point is, it is possible to block processes from being created. My requirements if I was asking a qualified Windows programmer to do this would be to disallow process creation if the parent process were sqlsrvr.exe and the child process were cmd.exe, or something similar that would be analogous to running something in a cmd shell. That might require some research. All I know is that it is possible to do, and that there are some third-party tools out there that do it. I do not know which third-party tools, but know that I get calls when I stand up a new server and start running xp_cmdshell (as part of legacy apps I support, not new development) because it trips the monitoring software. I end up having to file for a "security exception" for each server so the apps can run xp_cmdshell.
by a given user because I just saw a video of a hacker making his way to the registry and undoing some supposedly safe methods.
http://www.securitytube.net/video/653
He used VBA but he could have just as easily been in SQL Server as an "SA" to sp_regwrite to do the same thing. That means his attack software would take 3ms to try turning xp_CmdShell on and going to the command prompt and maybe another 4ms for his attack software to recognize the failed attempt and make a trip to the registry to correct the "problem" so that he could get to the cmd.exe program using xp_CmdShell.
This assumes the SQL Server service account has permissions to write to the registry outside of the normal SQL Server keys that it should have access too, and that should not be the case if the SQL Server was locked down and was using a low-privileged service account.
It also turns out that this supposedly safe method has some pretty nasty caveats for us users...
As I understand it "interactive command prompt" has special meaning and leans towards an interactive user, i.e. someone logged into the console or a RDP session, trying to get to a command prompt. As far as I know these types of settings would not apply to services like SQL Server.
Still, it seems that a determined hacker that can get to the registry through SQL Server can find and undo just about anything.
Again, only if the SQL Server service account allows it.
If you want to prevent someone from using the command line from SQL Server, merely turning off xp_CmdShell seems like pissing on a forest fire. The real key is to keep people from getting in as "SA".
That's not good enough. It's obviously uber important to limit the list of sysadmin Members, and to do your very best to weed out the untrusted, but it's not the end-all be-all. It can't be. We're dealing with humans here Jeff. Whatever roadblocks can be stood up, including working towards disallowing xp_cmdshell use via whatever means we have available to us, and to whatever extent you need to take it in your environment. At the end of the day though no one really needs xp_cmdshell. There are other much better tools that overlap 100% of it's functionality in more secure and robust ways, and so it's too big of a risk to have it just lying around waiting to be abused.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato