Jeff Moden (8/9/2013)
L' Eomot Inversé (8/9/2013)
Eureka moment (I'm getting on topic after having responded to far too many off topic messages above).Back in 2002 (I think; it may have been later) I discovered that I didn't have to write JobSteps in T-SQL; I could use ActiveScript and CMDEXEC as well as T-SQL (there were a pile of other sorts of step I could use too, but those were of far less interest). I had been resigned to making XP_CMDSHELL available so that (a) we could execute a text string which invoked cscript and (b) we could do things like deleting files. Suddenly I realised we could run ActiveScript without using XP_CMDSHELL, and use CMDEXEC for trivia like file deletion. This turned the world upside down - now many things became possible that were not before (or were not without screwing up security, because proxy account operation in SQL 2000 at whatever SP was then available was very flawed).
So now we could use the really excellent scheduling capability of SQL Agent to handle the automation of maintenance, problem diagnosis, and error recovery on all our customers' servers instead of trying to do things with the awful scheduling provided by Windows/DOS, and let SQL agent tie job steps together for us rather than writing C++ to do the scheduling.
That's another article I'm currently shaking out. Disabling xp_CmdShell doesn't really do a thing to help security. Worse yet, having it disabled may make people lazy about the very security issues that would allow people to turn it on. And, as you've seen, if someone get's in as SA that isn't supposed to, your server is their oyster. They can use xp_CmdShell, create self deleting attack jobs, etc, etc, etc.
The big problem in SQL Server 2000 back in 2002 or thereabouts was that specifying a non-privileged proxy account for xp_cmdshell didn't work (or at least I never managed to get it to work, and MS told me it wasn't supposed to work yet; a bit later - sp3 - they claimed it now worked, but it still didn't: calling xp_sqlagent_proxy_account now changed SqlServerAgent's login, but not the xp_cmdshell login for non-SA users). That meant that if you allowed users who were not system administrators to use it they could run code using all the privileges that the account running SQL Server had; if the proxy account had been workable, such users would have had access only to whatever privileges the proxy account had, but it wsn't. So making xp_cmdshell available to everyone, instead of just to those with SA privileges, meant that everybody could now do as much damage to the platform (Windows 2000 Server or, a year oo two later, Windows 2003Windows 2003 Server) as people with SA privileges could. The security problem with xp_cmdshell wasn't anything to do with the specification, only with the bug that meant you couldn't use it as it was intended to be used because it didn't work correctly. Disabling it for non-SA users did deliver improved security. To make matters worse - impossible to give xp_cmdshell safely to ordinary users whatever you did - (by design, which must be one of the nuttiest design decisions ever made) impossible to make it available to non-SA users in the case when SQL Server was using an NT account that wasn't a member of the local administrators group, so you couldn't get round the problem and give non-SA users safe access to xp_cmdshell by running under a non-admin account.
Yes, if people can get SA access they can do damage; but in SQL Server 2000 if people without SA access had access to xp_cmdshell they could do anything with it that someone with SA privilege could do.
Tom