April 1, 2013 at 7:38 am
I apologize for being late to the party. Typically I'd quietly read but I'd like to post my thoughts even though I'm not at the level of prior contributors to this thread.
Everyone has valid points and of course it all boils down to the environment you're in. In one extreme, xp_cmdshell isn't so bad while in others it's just a horrible idea. Regardless, you have to decide if you trust the people that can execute this AND trust the code being executed. If a sysadmin "tests" code in production, the deserve what they get up to and including getting fired.
I use xp_cmdshell only for my own "admin" functionality but I [do] try to force the little bit of auditing I'm able to: it's off by default and my jobs (precious few that need xp_cmdshell) explicitly turn it on, do their thing then turn it off again (error on "off" = alert) along with logging the exact statement executed. The errorlog contains the enable and disable. Poor man's auditing? Sure. But it's the best I can do with the tools I have.
I give props to any DBA that has the time to learn .NET. I'd love to but if it's not T-SQL or PowerShell, the answer is "no". The sad reality is I'm too busy being a DBA to have time to be a developer. When Microsoft decides to provide a different method of helping me accomplish the things I need to - by exposing the operating system or increased functionality in their current commands - I'll start using them instead, but I just can't "roll my own" because I don't like what Microsoft gives me.
Some solutions may be easy (MS: please add a 'total drive space' column to xp_fixeddrives) and some may not. If only Microsoft would take a look at what the average DBA uses xp_cmdshell for and provide a better alternative, life would be grand (gee MS, if we need auditing, why not bake it into xp_cmdshell...?)
BTW: Whether you're "for" or "against" making this functionality available, the commentary and passion for the topic show the level of skill, experience and professionalism (agreeing to disagree) of the SQLServerCentral members. The DBAs we really need to be aware of (read: "afraid of") are the ones who read Steve's editorial and said "Meh. Who cares?"
April 1, 2013 at 10:38 pm
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.
April 1, 2013 at 10:57 pm
Your Name Here (4/1/2013)
I apologize for being late to the party. Typically I'd quietly read but I'd like to post my thoughts even though I'm not at the level of prior contributors to this thread.Everyone has valid points and of course it all boils down to the environment you're in. In one extreme, xp_cmdshell isn't so bad while in others it's just a horrible idea. Regardless, you have to decide if you trust the people that can execute this AND trust the code being executed. If a sysadmin "tests" code in production, the deserve what they get up to and including getting fired.
I use xp_cmdshell only for my own "admin" functionality but I [do] try to force the little bit of auditing I'm able to: it's off by default and my jobs (precious few that need xp_cmdshell) explicitly turn it on, do their thing then turn it off again (error on "off" = alert) along with logging the exact statement executed. The errorlog contains the enable and disable. Poor man's auditing? Sure. But it's the best I can do with the tools I have.
I give props to any DBA that has the time to learn .NET. I'd love to but if it's not T-SQL or PowerShell, the answer is "no". The sad reality is I'm too busy being a DBA to have time to be a developer. When Microsoft decides to provide a different method of helping me accomplish the things I need to - by exposing the operating system or increased functionality in their current commands - I'll start using them instead, but I just can't "roll my own" because I don't like what Microsoft gives me.
Some solutions may be easy (MS: please add a 'total drive space' column to xp_fixeddrives) and some may not. If only Microsoft would take a look at what the average DBA uses xp_cmdshell for and provide a better alternative, life would be grand (gee MS, if we need auditing, why not bake it into xp_cmdshell...?)
BTW: Whether you're "for" or "against" making this functionality available, the commentary and passion for the topic show the level of skill, experience and professionalism (agreeing to disagree) of the SQLServerCentral members. The DBAs we really need to be aware of (read: "afraid of") are the ones who read Steve's editorial and said "Meh. Who cares?"
Great post and thanks for taking the time.
Still, the points being made about whether to use xp_CmdShell or not are only secondary to me. If someone doesn't actually want to use the tool, I get that. The real problem is that even some of the very heavy hitters in the world of SQL Server imply or even come right out and say that turning it off provides additional security. My take is that the level of security provided by turning it off is so thin and weak that you might as well leave it turned on for all the good that turning it off will actually do for you.
I would take the same stance even if I agreed that you should never use xp_CmdShell.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 1, 2013 at 11:38 pm
So, if I understand correctly, the only use of turning off xp_cmdshell is to remind database administrators that is company policy not to use xp_cmdshell.
I also read on MSDN that it is possible to disable xp_cmdshell by Policy-Based Management. Does this work effectively? Does this work effectively for the other means of executing Operating System Commands?
April 2, 2013 at 5:39 am
Arjen Krap (4/1/2013)
So, if I understand correctly, the only use of turning off xp_cmdshell is to remind database administrators that is company policy not to use xp_cmdshell.I also read on MSDN that it is possible to disable xp_cmdshell by Policy-Based Management. Does this work effectively? Does this work effectively for the other means of executing Operating System Commands?
That's pretty much what I've been trying to get across to people. Without taking other steps, the only security it provides is a log file that says someone turned it on. For those that didn't take the other steps, it's a log about when you got hacked.
I've not tried it because I use xp_CmdShell but, from what I've seen, PBM is very good at keeping it turned off. Like I said though, if your system isn't secure, even that won't matter. People can use other methods to get to command line functionality if they get into SQL Server as an "SA".
--Jeff Moden
Change is inevitable... Change for the better is not.
April 2, 2013 at 7:26 am
I would be concerned about Windows Admins using this feature and accidentally causing problems in the SQL Server.
April 2, 2013 at 7:58 am
Unless they have bad intent it is unlikely the Windows Admins know about cmd shell.
Cheers
April 2, 2013 at 8:14 am
If your application or user accounts are members of the sysadmin role, then it's like having a house full of kids and then leaving the gun cabinette unlocked.
However, most organizations should trust their in-house database administrator with xp_cmdshell.
The exception to this would be outsourced DBAs or 3rd party hosted environments where the organization owning the SQL Server instance doesn't want to entrust the DBA beyond allowing them to perform standard "inside the box" tasks.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 2, 2013 at 11:31 am
The issue of whether to use it or not also seems to go a bit beyond security, spilling into application design. If we break this down into two areas where xp_cmdshell might be useful:
1. For DBAs it is a convenient tool to get things done on the host OS'es operating system but could offer permission elevation and does allow for identity obfuscation. Alternatives: have the DBA run PowerShell or VBScript from their own workstation under their own account. For automated processes use SQL Agent or another scheduler where there is an option to run jobs under a distinct security context.
2. For Developers, using xp_cmdshell to expose functionality through stored procedures is analog to multi-purposing your SQL Server as a Windows Application Server. You still have the problem of identity obfuscation and the lack of options in terms of segmenting tasks so that least-privilege is honored. Alternatives: use an application development language separate from T-SQL to interact with file systems or resources external to the database engine, e.g. SSIS, .NET or PowerShell.
Trust is key but bottom line is xp_cmdshell is a security threat, by definition and in practice. The fewer exposures there are, the better off the environment is.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 3, 2013 at 8:46 am
Trust is key but bottom line is xp_cmdshell is a security threat, by definition and in practice. The fewer exposures there are, the better off the environment is.
Powering up your server could be considered a "security threat" if you can only view this sort of thing in binary terms. I believe I've seen posts suggesting that functions like xp_cmdshell simply be removed from the product, but I'd much rather people just understand the darn functionality, and not ask that parts of Microsoft products just start gettting hacked off until every conceivable danger is eliminated.
April 3, 2013 at 10:31 am
patrickmcginnis59 10839 (4/3/2013)
Trust is key but bottom line is xp_cmdshell is a security threat, by definition and in practice. The fewer exposures there are, the better off the environment is.
Powering up your server could be considered a "security threat" if you can only view this sort of thing in binary terms. I believe I've seen posts suggesting that functions like xp_cmdshell simply be removed from the product, but I'd much rather people just understand the darn functionality, and not ask that parts of Microsoft products just start gettting hacked off until every conceivable danger is eliminated.
It could be viewed that way, and that would likely be the most fundamental and basic way to look at things in terms of where to begin securing a system. I am not taking it to that extreme, but it could be taken there, and I am sure it has. Consider any computer that has the ability to control the launching of a nuclear weapon. I would say that "power on" is one of those system functions where security needs to be considered.
One comment of yours in particular stood out to me. You're implying that if something is available, that by understanding how that thing works it makes it less dangerous. I would disagree with that assertion since it is not always the case, and I would say is not the case here. I understand how xp_cmdshell works but if it's made available in my environment that won't necessarily stop a developer from abusing it in an application design capacity, and it won't necessarily stop a DBA from using it to elevate their own permissions so they can gain access to files they would not normally be able to access. Because I understand it is why I do not want to make it available. Not everyone can be properly educated or trusted to use every tool properly or responsibly. That's just a fact of life.
Maybe hacking it out of SQL Server is a bit extreme. To be fair I am certain that many people have gained lots of utility from using xp_cmdshell, and probably built some pretty cool solutions. I am saying that at minimum I would like to see there be an option to raise the bar in terms of bringing xp_cmdshell online, i.e. make it an explicit installation option in addition to having to run sp_configure to enable it. I would say the same for things like OLE Automation (sp_OA procs), SQLCLR, and a few other SQL Server features as well.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 3, 2013 at 11:28 am
Very interesting discussion.
Opc.three, you did bring up a few good points, but this isn't one of them.
opc.three (4/3/2013)I understand how xp_cmdshell works but if it's made available in my environment that won't necessarily stop a developer from abusing it in an application design capacity,
There definitely a danger of exposing this in applications, especially to developers, but the main point I was asking about was administrative issues. If someone already is a sysadmin, and potentially can run unsafe code in PoSh or some other scripting language, is xp_cmdshell worse? I'm not sure it is. There is a potential issue for injection attacks as TravisDBA pointed out, but I'm also not sure those attacks couldn't be sent through PoSh as well.
April 3, 2013 at 11:50 am
Steve Jones - SSC Editor (4/3/2013)
Very interesting discussion.Opc.three, you did bring up a few good points, but this isn't one of them.
opc.three (4/3/2013)I understand how xp_cmdshell works but if it's made available in my environment that won't necessarily stop a developer from abusing it in an application design capacity,
There definitely a danger of exposing this in applications, especially to developers, but the main point I was asking about was administrative issues. If someone already is a sysadmin, and potentially can run unsafe code in PoSh or some other scripting language, is xp_cmdshell worse? I'm not sure it is. There is a potential issue for injection attacks as TravisDBA pointed out, but I'm also not sure those attacks couldn't be sent through PoSh as well.
Of course, if the application and user accounts are simply not members of an admin role, then the security threat posed by xp_cmdshell is marginal. If role based security and permissions are properly setup in production, then the worst that can happen is that the developer attempts to leverage something via xp_cmdshell, it works in development, but then it fails with access denied in QA or Production. That's not really a disaster (at least not from the DBA's perspective), but it will mean that the developer has to pull at all-nighter to re-write their code.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
April 3, 2013 at 1:09 pm
Steve Jones - SSC Editor (4/3/2013)
Very interesting discussion.Opc.three, you did bring up a few good points, but this isn't one of them.
opc.three (4/3/2013)I understand how xp_cmdshell works but if it's made available in my environment that won't necessarily stop a developer from abusing it in an application design capacity,
There definitely a danger of exposing this in applications, especially to developers, but the main point I was asking about was administrative issues. If someone already is a sysadmin, and potentially can run unsafe code in PoSh or some other scripting language, is xp_cmdshell worse? I'm not sure it is. There is a potential issue for injection attacks as TravisDBA pointed out, but I'm also not sure those attacks couldn't be sent through PoSh as well.
That's fair. If we're wanting to restrict this particular thread to only the administrative aspects of xp_cmdshell then consider the DBA who is in the sysadmin Role but who does not otherwise have file system access to the host OS and does not have the SQL Server service account password. For fun let's say that the SQL Agent service is disabled and the hole allowing cmd-line access by tickling the registry and using another unmentioned T-SQL feature, the hack Jeff mentioned, has been closed in 2008+. In environments setup this way with DBAs working this way, and there are many DBAs setup this way in many environments including the one I am in right now, sanctioning the use of xp_cmdshell amounts to the sanctioning of permission elevation and identity obfuscation. In this scenario it becomes extremely difficult, if not effectively impossible, to audit the actions occurring in the context of the SQL Server service account. How to account for which cmd-shells were part of an automated process or an ad hoc attempt to access data that would otherwise be unreachable to the DBA? This may or may not be an issue in many environments, but environments are also not static. If you have a ton of DBA processes built up around the use of xp_cmdshell and a DBA is added, or a server is one day reclassified differently and is subject to compliance regulations, then how do you back down access to resources accessible to xp_cmdshell so this DBA can do their job? I am back to thinking that using xp_cmdshell paints us into a corner because there are no options to maintain the security context of the caller all the way through the stack. In some environments, maybe even most or the majority, it doesn't really make a difference one way or the other. I am just saying that the advice to leave it disabled has merit and should be the default response when discussing xp_cmdshell. Any advice that says to use it freely, especially on a public forum where little to no knowledge of the source environment is available, seems irresponsible to me.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 3, 2013 at 4:12 pm
The auditing issue is a potential security issue. However that depends on the circumstances and whether or not there are other ways to audit actions.
That's a fair point and a good argument to not enable this feature. I'm not sure it means we should never allow it, but it does point out a hole.
Viewing 15 posts - 31 through 45 (of 76 total)
You must be logged in to reply to this topic. Login to reply