Jeff Moden (3/30/2013)
opc.three (3/30/2013)
Once again you're focusing in too narrow a field and only considering SQL Server auditing, and only a piece of that to boot. What about the folks that kick off an SSIS package, or a bcp command for those that abhor SSIS, using xp_cmdshell and that command connects to another server? How do we know who ran it?Since we are, in fact, discussing the use of xp_CmdShell from SQL Server, I'm not sure why you think the discussion isn't on target. But my response would be that, considering that most apps don't pass the actual user that's logged in, the same would be true of an app that kicked off an SSIS job or if it kicked of an SQL Server job or even if it did some deletes. There's no difference there. Once again, we're talking about bad security.
I am saying you are not considering multiple other aspects of "good security" and are instead only focusing on the way you may have personally, successfully leveraged xp_cmdshell in your organization to produce a solution, or at least it seems that way to me. If you have a core set of DBAs and developers and all are trusted and no one has ever breached that trust that you know of, great. What I am saying is that you are exposed security and auditing-wise much more than if you went a different route, and that you are far more limited in terms of auditing, troubleshooting as well as in programming options than if you went a different route.
Also consider that there are auditing tools outside SQL Server that can track cmd shell activity. If everything appears as if it is occurring under one service account then where is the auditability?
And, just like auditing in SQL Server, they have to be enabled and maintained. They do nothing by themselves. Once again, we're talking about bad security.
My point is that using xp_cmdshell as a mainstream development and admin tool compromises the effectiveness of those types of tools. It has a negative net effect on security.
I am the first to admit that enabling and using SQLCLR is also a security risk, and pretty much most any feature of SQL Server to be honest. I would like for Microsoft to take the same approach with SQLCLR (and OLE Automation, and some other things too) as I mentioned with xp_cmdshell, no, not deprecate and eventually remove it from the product, but make adding it to the product an explicit installation requirement. In the case of SQLCLR I would take it a step further and force the installation of the various permission sets as well so we could choose only to install the option to promote assemblies marked SAFE, EXTERNAL_ACCESS or UNSAFE, or any combination. Now, if you must access the file system use SQLCLR for goodness sake. At least then you can have the credentials of the user making the call from T-SQL...
Heh... you should have a chat with Adam Machanic about SQLCLR. Also, someone with even the smallest bit of knowledge can easily make a self deleting job in SQL Server to call a file system related SQLCLR using the server login and there will be no trace of malicious use of the SQLCLRs. Once again, we talking about bad security.
I have never met Adam Machanic but have read a lot of his contributions to the community. In fact, I credit his book Expert SQL Server 2005 Development for teaching me a few things about the SQLCLR that I ran across when researching how to best develop a SQLCLR Aggregate Function that would bring similar functionality available in MySQL to SQL Server, namely the GROUP_CONCAT string aggregate for SQL Server[/url].
I am aware of the security options available within SQLCLR, and I credit a lot of that to Jonathan Kehayias. Here is one article Trading in xp_cmdshell for SQLCLR (Part 1) - List Directory Contents[/url] that has a title most relevant to this conversation. There is another article, can't find it right now, where Jonathan shows how to ensure that accessing resources outside the SQL Server is done using the callers own credentials, and that if the SQLCLR object is called by someone logged in with a SQL Server Login, and not a Windows Login, it throws an error.
The SQLCLR simply outshines what is available through xp_cmdshell and I mentioned some of those points above. Namely that if you are going to access the file system from a SQLCLR object you can program it in such a way that it will pass the callers credentials through to the OS, i.e. people maintain their identities all the way through the stack. If they do not have permissions to reach a file share directly using their own permissions then they won't gain access just because they went through T-SQL to try to access that same resource that they are restricted from seeing. This differs from xp_cmdshell where the credential used is the SQL Server service accounts. I am not condoning the use of the SQLCLR this way. I actually try to guide folks into only marking their Assemblies with the SAFE permission set, but if you're going to access resources outside the domain of the database engine then at minimum you could do it in such a way as to maintain the person's identity all the way through the stack to avoid permissions elevation and obfuscation of their identity.
Now let's consider the case where a SQL session initiates the use of xp_cmdshell and that process becomes hung up in the OS.
..{snip}...
See any problems with this?
It happens, quite often at one shop where I did some work, and is yet another reason xp_cmdshell should be avoided.
Yes, I see the same problem with any programming language. Someone doesn't know how to use it correctly and they blow stuff up. For example, a large accidental cross join (usually a Many-to-Many join because someone doesn't know the data they're working with and left out some criteria) in SQL Server can take hours and, sometimes, days to rollback and it will have a much larger effect on SQL Server than someone who incorrectly ran a ping that never ends. This isn't a failure of xp_CmdShell. It's because someone who didn't understand how to use it did something wrong with it... just like in any other language.
In this case, we're talking about training. Someone using Powershell or SSIS could do things just as wrong and there wouldn't necessarily be any logging substantial enough to identify who did it there, either.
Agreed. Any language can be abused. The bar is just too low in the case of xp_cmdshell and it is too blunt a tool for me to endorse its use. We could go around and around on this point endlessly and still never agree.
To be fair you could substitute anything in place of xp_cmdshell in your sentence and it would ring true, so it's just empty rhetoric.
To be honest, although this is a highly controversial and emotional subject, I am trying to keep it professional and on the subject. I consider your calling things that I've said "empty rhetoric" as personal attack. I've very much supported my position and even though I am dead-set against the position you've take on this, I respect your position and I've not stooped to the level you just have. Knock it off, Orlando. You know better especially with me.
The generalization is what I meant to attack, not you Jeff. My sincerest apologies that you took it personally.
The real issue is: how well does xp_cmdshell facilitate the enacting and maintaining of good security? ...not very well. I would argue that in most cases, even when it is implemented well, the bar is so low in terms of how sysadmins and folks that know how to make themselves sysadmins can take advantage of xp_cmdshell that it is in fact a security risk.
Ok. Agreed. But let me ask, where is the bar when someone is allowed to run Powershell (for example) starting from either the command line, SSIS, or an SQL Server job?
When running PowerShell or SSIS from the command line or from a development IDE it is running in the context of the persons own login. Those people that are standing up SQL Server Agent jobs for purposes of circumventing security and auditing and then having them delete themselves are subject to termination, whereas you're proposing a sanctioned tool be stood up that would allow them to do the same type of circumvention as part of normal operating procedure...I have a problem with that. If they follow protocol 99.9% of the time, great, but the option is right there for them to run with if they ever decide to run with it.
We know SQL Agent is another security issue that needs to be addressed. Ideally all job steps will run under a proxy account that belongs to the job being run so the account can only be granted permission to do the actions it needs and those actions are carried out under an account that can be traced back to just that specific process. We can have multiple proxy accounts servicing SQL Agent whereas we can only have one service account and one proxy for the entire instance servicing xp_cmdshell.
Once again, it doesn't take much skill on the part of an administrator to hide their actions by creating diversion logins and self-deleting jobs/packages. Once again, we're talking about bad security and trust in admins. It has nothing to do with what you can or cannot do with xp_CmdShell or Powershell or any of the other methods for malicious activities.
xp_cmdshell just makes it that much easier to go on undetected and there are superior options out there for getting the same, and more, done. The question becomes not why are you still using xp_cmdshell, it becomes why are you not using SQLCLR, SSIS or PowerShell (without the help of xp_cmdshell)? If it's the argument about not wanting to learn a new language, well what do you call Windows Batch? The stuff you throw at xp_cmdshell? Guiding newbies or other public forum goers towards xp_cmdshell seems akin to guiding them towards VB6 to build a new Windows Forms App or Classic ASP to build a new website.
You did bring up a great point, though. Along with good security, there must also be some skill and knowledge of what will happen when you do something in code. I'll also add that those points are absolutely true with any code or programming vehicle you chose to use and not just with xp_CmdShell.
No doubt. We can definitely agree on that point. xp_cmdshell is just too limiting a vehicle in my opinion when it comes to securing a SQL Server. It gives trusted admins that want to do untrustworthy things too much cover. In my opinion the bar is higher with some of the other tools mentioned, not to mention they are far more stable, robust and offer more in the way of programming flexibility when it comes to troubleshooting and maintaining auditability.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato