jfogel (3/30/2013)
Both of you make very good points but I'm still going to keep using cmd shell if I need to and not if I don't. The position to not sounds to me like those who think nobody should own weapons. What if... Risk vs. Reward is what is important.
I'm of the same ilk and I definitely agree with that. To wit, since I'm still "stuck" with SQL Server 2005, I use xp_CmdShell to call PowerShell.
But I also want to understand why some people are so against using xp_CmdShell. My take is that turning it off still provides no additional security (ok... maybe a thin veil but it only keeps the honest man honest, IMHO). With that thought in mind, I say "why not use it" and opc.three is trying to explain some of his reasons as well as some alternatives.
Without some form of tamper-proof OS level auditing, PowerShell doesn't look any more secure than xp_CmdShell when it comes to logging actions such as deleting files, but that may be the difference between good security and great security. I personally know of no one that's taken such a thoughtful step, though.
And that's the real nature of what I've been trying to stress. Having xp_CmdShell turned on or off isn't going to help security one bit because a first year Junior DBA or a casual user that has "SA" privs, not to mention an attacker that gets in with "SA" privs, can get to the command line from SQL Server without going anywhere near xp_CmdShell and they can do it totally anonymous fashion. I could certainly be wrong but it currently appears that unless someone has taken the extra step of OS level auditing or some other step that I don't know about the likes of PowerShell, then xp_CmdShell doesn't appear to be any more dangerous than letting someone use PowerShell.
--Jeff Moden
Change is inevitable... Change for the better is not.
As a production DBA, I certainly have a responsibility but those who control the layers above do, too. I have layers to protect weapons while keeping them at close reach when needed. Same for all else. I'm more concerned about other methods of a breach because I've seen them first hand.
Cheers
Jeff Moden (3/30/2013)
Then I have to admit, points in favor of PowerShell but only if such auditing software is actually inplace, active, and that, you as an administrator, can't defeat the software or modify the logs. Lord help the company if the administrator is in cahoots with the person that can.
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.
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.
The argument "well any sysadmin can just enable it and takeover the instance so why bother disabling it" is completely missing the point.
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 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.
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.
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.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 16 through 30 (of 77 total)
You must be logged in to reply to this topic. Login to reply