opc.three (3/30/2013)
That's just the thing. The "only if" is specific to ones environment and is irrelevant to the point. With xp_cmdshell, you do not even have the option to properly audit what is happening in your environment. General advice that xp_cmdshell is safe is irresponsible in my opinion, especially on a public forum. Someone of your notoriety should expect that a SQL-mortal might just haul off and run with back to their developer meeting to justify its use.
BWAAA-HAAA!!!! I can only hope that they do especially when they get to the last couple of paragraphs of the following which is where the real message is (and I've said it before). Whether or not they decide to use xp_CmdShell or not, those paragraphs contain the real message I've been trying to get out that may have been missed in our mutual disagreement over whether is should be used or not.
I can tell we're never going to agree on any of this and that's ok because there's nothing like a good strong discussion to get people to look into things.
As you once stated, you have a "visceral fear" of xp_CmdShell. While I'm sensitive to such fears, I don't intend to give up a great tool nor stop recommending the tool because of someone else's fear. In the absence of any special OS Level Auditing Software and according to what you said, PowerShell is not audited anymore than using xp_CmdShell so there's no real difference for those installations that don't have such tools. Like many other things, it comes down to good programming and property system security.
The idea that xp_cmdshell is harmless came back to bite more than one of the shops I have contributed in. Building up processes around xp_cmdshell, anything from db admin scripts to an entire ETL framework that provided data movement options from soup to nuts all leveraging xp_cmdshell, can eventually become a very expensive refactor job because it paints you into a corner with security and auditing.
You say that but even with all that I've done in the area (and I have stretched the limits there), I've never had such a problem. I've never had it paint me into the corner anymore than deciding how to audit deletes in a table when done from an application. It's a simple matter of writing code to do what you need.
The argument "well any sysadmin can just enable it and takeover the instance so why bother disabling it" is completely missing the point.
Not quite. In fact and as previously stated, it's really the whole point. As with anything else, if security isn't properly managed, then security will suffer whether you're using xp_CmdShell or not. The ONLY things that turning xp_CmdShell off will do is it will successfully log an entry when an someone turns it on and it deprives DBAs of a great tool. If security is bad, then it may be an attacker that turns it on. If the attack is to simply steal data, then the attacker won't even bother with that because they don't want to raise a flag to someone that they've been hacked. Instead, they'll use any of a dozen other non-logged tools to copy company sensitive data especially since most of the data they're after is actually in the database and not at the OS Level. Turning off xp_CmdShell does nothing for security and, speaking of social responsibilities, is misleading if anyone says it does for all the reasons I've previously stated. Only "SA"s and appropriately built procs can use it unless someone is stupid enough to grant proxy privs to individuals. If security is good, then it stays that way. If security is bad, it's not gonna matter if it's turned on or off. Someone will get you.
Also consider the version of DBA that is a member of sysadmin but actually does not have the service account password, nor the ability to Remote Desktop or otherwise reach a command prompt on the host OS. You say you could reach a command line without xp_cmdshell. Mind showing how to do that because I think I know what you mean, but the loophole looks to have been closed in SQL 2008 and above.
I'm not sure why (especially if sysops are available) someone that's a member of sysadmin would even need the service account password any more than why they'd need the password for the actual [SA] login. In many cases, I'm not sure why they'd ever need to RDC into the server, either.
I've already told you of one way to get to command line functionality. Just write a job that uses the "Operating System" task. You could also, write a PowerShell task. If you make it a self deleting job, the evidence goes away just like the job. If all the easy avenues are somehow blocked, you can also get there with a OPENQUERY hack that requires a very simple poke in the registry using xp_regwrite or xp_instance_regwrite to enable a CMD processor. I won't post that hack though.
I am curious, now. Do you personally run PowerShell from SQL Server Jobs at all? If not, where do you personally run it from?
With the understanding that I'm "stuck" in the world of SQL Server 2005 for the time being, if you run PowerShell using a PowerShell Task in an SQL Server job, what does it run as?
I personally run scripts from PowerShell ISE, in the security context of my own Windows Account.
As for automated jobs, the current shop is mostly 2008 R2, some 2005 still around, and some 2012 coming online. Many of the PowerShell and VBScript scripts are run from Windows Task Scheduler where each job can have its own account running it. They operate on the databse as a client app would and interact with file shares but never touch the file system of the OS the SQL Server is running on, and that is the preferred way to go in my opinion.
Now there's something we both agree on, if I understand what you mean. The files we work with are never on the SQL Server. They're always in some staging area on another box and the "vault" where the final archives are stored are also on another box.
Some scripts are also run from SQL Agent on the 2008 R2 instances but not using the PowerShell Step Type, using powershell.exe in a CmdExec Step Type. I would like to use proxies for all those CmdExec step types but that's an effort that takes a lot of explaining and justifying to management on why we would should change the way things are currently done...very similar conversation to what we're having here, but I am making progress.
That brings up a point that I've been meaning to personally verify but haven't had the time to do so, yet. Rumor has it that the SQLPS executed by the PowerShell Step Type isn't nearly as powerful as using PowerShell.exe directly or by the "Operating System" task. Rumor also has it that it's slower than calling PowerShell.exe directly. I also agree with the idea of having a different proxy for each job. More on that in a minute.
{EDIT} And I see that you've actually confirmed the "mini-shell" problem below. Thanks for that.
You could run your PowerShell from SQL Agent in your 2005 instances as well and is how I would recommend it in 2008/R2 anyway given some shortcomings in the PowerShell Step Type (reference mini-shell). I have not played around with PowerShell Step Types in SQL Agent 2012 to see if they went away from the mini-shell but think I remember thinking that it was supposed to provide a better experience now.
In a previous shop all the ETL processes were kicked off from an Enterprise Job Scheduler, i.e. SQL Agent was not used at all. Each job had its own Windows Account that it ran under and that account only had permissions to the resources it required: access to specific file shares, exec perms on procs, etc.
Ideally you should run each of your jobs under a distinct security context so they can adhere to least privilege. We tell the front-end developers their application service accounts cannot have db_owner privileges, let alone sysadmin privileges, when they ask for access to the database to run their apps yet most DBAs are content to run all the backend ETL and admin jobs under the same service account, which also by the way happens to exist in the sysadmin Role and have access to instances and file shares all over the environment. Implementing xp_cmdshell limits our ability to segment these things out so I say it is a bad option and should not be a recommended tool.
It's funny that you mention "distinct security context" for each job. First, I absolutely agree with that. In previous companies, users/logins were all in the Windows context and each job had it's own SQL Server login. Yeah, it seems complex but it had a whole lot of benefit including being able to log which rows were modifed by any given job.
Anyway, even though I actually do think that xp_CmdShell is safe (certainly as safe as PowerShell Tasks) when created by the right person in a secure system, just like anything else, in an unsecure system, nothing is safe even if that something is turned off. Even if someone is fearful of xp_CmdShell and has the company policy of not using it, my biggest goal in all of this really has nothing to do with xp_CmdShell usage. My biggest goal is to make people absolutely understand that turning xp_CmdShell off does nothing for security. Call it a "layer" if you want but it's a layer so thin that virtually anyone who has access to the server with "SA" privs, internal or attacker, can either turn it on in a heartbeat or do a whole lot of damage/theft without ever touching it.
That's what I really want people to get out of all this.
{EDIT} And, as a side bar, I think it would be real handy if MS would make some file handling tools for T-SQL that actually did do some proper logging without actually having to write it into the code.
--Jeff Moden
Change is inevitable... Change for the better is not.