Jeff Moden (11/20/2013)
Tell me how you can stop an SA from getting to the command prompt through SQL Server? You can't.
You can achieve it through the use of WMI. I showed how to detect when a cmd shell is spawned by the sqlsrvr.exe process on another thread here on SSC. If you can detect it, you can intercept it. Beyond WMI, it is quite likely that one could write a program to intercept and prevent use of cmd using Windows process hooks, but that's hocus pocus to me. I am thinking about how Windows can prevent non-interactive users from running programs (the 'are you sure you want to execute this' type popups), or how anti-virus software can decide a given executable should not be allowed to execute.
Who are the only people that can enable xp_CmdShell? Only SAs.
And why would they feel compelled to do that?
If you think you need xp_cmdshell, I say you're doing something wrong. Using it leads to the desire to elevate the service-accounts permissions which only further puts your system and data at risk.
If you only use xp_cmdshell to delete old files from the backup directory, then why not use one of the expended stored procs already built into the system? Give me an example of when you need
to use xp_cmdshell and I'll give you several options on how to avoid its use. It simply is not required.
Allowing the use of xp_cmdshell in effect sanctions the idea that a user can impersonate the service account without providing a password for that account. Do you see where I am going with this? I know you do, and it's ok if you disagree, but the argument is valid. Enabling and using xp_cmdshell makes your system less secure and less auditable.
Tell me the only people that can use xp_CmdShell (unless you messed up with a proxy)? Only SAs.
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?
Can anyone other than an SA turn on xp_CmdShell? No.
I can think of ways a non-SA could be setup so they could enable xp_cmdshell. What is your point?
Can you determine WHO turned on xp_CmdShell through through auditing?
Sure. Through the use of Audit, XE and Trace you have a fighting chance of isolating who made a change should a breach be detected. Could an SA turn these things off? Maybe, if they knew these technologies existed and had the minimum level of knowledge to work around them, but potentially not without causing a gap in the audit trail. The overarching point is the bar to attack the system in question and get away without being caught has been raised. Most breaches are internal. 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.
You can tell what the SPID was but there is no true conviction path to the person.
See earlier comments.
You could even make a self deleting job do your dirty work for you.
You're assuming SQL Agent is in play. Let's just say it is on a hypothetical system, then you're right, and I have said this many times before, securing Agent must be a consideration when locking down a system. There are many places where folks are handed SA inadvertently by gaining membership in an AD Group improperly. Others gain it via poor security practices and the careless storage of configuration information written in plain-text, some from a post-in note or a packet sniffer. At any turn, those internal threats exist.
It would be better if you spent more time telling people how to prevent bad guys from getting in as SA them telling them to turn off xp_Cmdshell because turning it off provides no extra security from internal people or external people that get in as SA.
I work towards all of the above, and so should everyone. Are some of the things I mentioned too much for the general SQL shop to implement, probably, but it does not take away from the fact that categorically speaking, enabling and using xp_cmdshell makes a system less auditable and less secure.
If I may, you would be better off figuring out more secure and auditable alternatives to using xp_cmdshell than continuing to abuse T-SQL and SQL Server in general as if it were some one-size-fits-all application server technology. You have earned your seat at the proverbial table in the SQL community but in my opinion, in this particular context, you're absolutely misusing it and leading people astray.
__________________________________________________________________________________________________There are no special teachers of virtue, because virtue is taught by the whole community. --Plato