Jeff Moden (1/13/2013)
Hmmm. I can see your point. I mean about not doing file handling in SQL Server and that those types of things should be done well outside of SQL Server. Of course, then you have an additional system to secure and privs to control and learn and document and audit and maintain.
Thank you for acknowledging there is some value in moving file system manipulation work outside of SQL Server. That might be the first time I have heard you say anything close to that. For the record I do not view the app server that would handle this work as an "additional system" to lock down. It's part of the same system and the security is layered and orchestrated across all parts of the system. File handling systems are quite simple to lock down and audit, at least compared to locking down a SQL Server that has xp_cmdshell enabled or file handling routines implemented in SQLCLR that are not allowing the callers identity to pass through to the file system.
And, yes, you're correct. Then extension of my argument is and always has been that if someone can get in with SA privs, then there is no way to stop the attacker before the attacker can do extremely grave damage at any level even if xp_CmdShell has been deleted. It won't even slow an attacker down long enough for anyone to realize they're being attacked before they've done what they setout to do. It'll simply be too late and your auditing will simply show that someone got in as "little Bobby" and threw a world of hurt on the server. Disabling xp_CmdShell as "another roadblock" is a total waste of time. Someone attacking as an "SA" can turn it back on in the first few miliseconds they're "in". All having it turned off does is give a very dangerous false sense of security. It's a bit like holding a dancing veil up to shield against a nuclear blast. Looks real pretty and is associated with some great moves but does no real good.
I like the analogy but all of that can be blocked at the OS level. It was monitored at one shop I was in and I received phone calls about it regularly asking if we could "stop using xp_cmdshell from SQL Server." Which unfortunately we could not because the legacy application that used it was designed as if the original architects were being paid a bonus for every call they made to it in their stored procs.
I'll also say that unless someone can get in as "SA", they won't be able to use xp_CmdShell even if it's turned on and all greased up with a proxy. I will absolutely agree that allowing any non-SA prived user to directly use xp_CmdShell through a proxy is one of the most incredibly stupid things that anyone could do. I can't actually believe that MS allowed for such a thing especially since it's just not needed.
To be absolutely clear, I also believe that only the DBAs themselves should have any privs other than PUBLIC and exec privs on a handful of stored procedures. Users/logins/apps shouldn't even need even db_datareader or db_datawriter.
On these two points we agree 100%. I would take it a step further and say that allowing one to get to the file system from within T-SQL at all was a major mistake.
I can also get my job done much more quickly by automating jobs in the environment I know best. Shoot... I even use xp_CmdShell to call PowerShell.
I see. So if someone were to prevent you from using xp_cmdshell then it would be bad for you. Is it that having xp_cmdshell makes you feel more productive than any other set of tools you have acquainted yourself with? As a litmus test, if someone offered you a job making 50% more than you make now with the opportunity to gain a very high level of job satisfaction based on the job description but the pre-condition was that "corporate" has banned xp_cmdshell, would you not entertain the offer?
Just to be clear, I never give individuals or apps the privs to run xp_CmdShell directly.
You're missing the giant risk right under your nose. A DBA is an individual and the fact that they have the title of DBA makes them no less likely to want to steal data or become disgruntled and put the company at risk.
And, no... I'm not saying that you shouldn't put obstacles up in front of would-be attackers. I'm just saying that turning off xp_CmdShell is no obstacle to an attacker what so ever because they're expecting it to be turned off and have already included different attack vectors in their code to easily get around it.
There are plenty of obstacles within and outside SQL Server including having something running in Windows that can intercept and block cmd shell requests emanating out of SQL Server.
The bottom line is that you and I agree to disagree on this subject. That's a very good thing, actually. That means that when you and I have such a conversation (does seem to be a lot), people are really going to benefit because they get to see both sides of the story to make an intelligent decision based on their particular needs.
It does seem to occur quite a lot but it's not a bad dialogue to reprise once in a while 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato