Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»»

The Command Shell Expand / Collapse
Author
Message
Posted Wednesday, April 3, 2013 10:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1438471
Posted Wednesday, April 3, 2013 11:28 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,018, Visits: 15,456
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1438508
Posted Wednesday, April 3, 2013 11:50 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:39 PM
Points: 1,660, Visits: 4,748
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.
Post #1438515
Posted Wednesday, April 3, 2013 1:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1438548
Posted Wednesday, April 3, 2013 4:12 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:16 PM
Points: 31,018, Visits: 15,456
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1438609
Posted Wednesday, April 3, 2013 5:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
Steve Jones - SSC Editor (4/3/2013)
There is a potential issue for injection attacks as TravisDBA pointed out....


I brought it up in a post before that, as well.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1438615
Posted Wednesday, April 3, 2013 9:01 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 6:50 PM
Points: 634, Visits: 2,208
I've been following this post since the beginning. You want to talk about not seeing the forest for the trees? This is getting into not seeing a tree because of the weeds.

I look at that the use of xp_cmdshell as a production DBA as a simple need. There are some things that can be achieved through CLR or PS, but take about 15 steps to the three needed to use xp_cmdshell.

A little background on my thought processes. In eleventh grade I was taking two programming classes. One was BASIC (not VB) and the other was assembler. My senior year was Pascal and COBOL. From my touching COBOL, I determined I'd starve on the street before I would program in it.

So now more than twenty years forward in time, I'm watching this argument and laughing at it. If you have someone that is at the sysadmin level and can use xp_cmdshell and can't find a way to beat the audit log then they deserve to be caught. If the SQL service account has any raised domain level permissions then you didn't set up it up right in the first place.

This was like my last company. The auditors demanded that we have a regular user account and then use a separate admin account for doing the admin work. We would login into our computers as our regular account then had a batch file to launch everything else as an admin privilege with an input put parameter for the password. Why? Because we realized the stupidity.

The argument over whether xp_cmdshell is a threat means that someone, with decent knowledge, is already so far in it doesn't matter. Now I catch a programmer doing that crap, I'm going to bust his butt. But the usual end-user using an application is not going to be the danger. So trying to guard against the normal edge is good. But going to paranoiac extremes generally makes no sense.




----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.
Post #1438635
Posted Thursday, April 4, 2013 7:59 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:47 PM
Points: 35,215, Visits: 31,667
Jim P. (4/3/2013)
The argument over whether xp_cmdshell is a threat means that someone, with decent knowledge, is already so far in it doesn't matter. Now I catch a programmer doing that crap, I'm going to bust his butt. But the usual end-user using an application is not going to be the danger. So trying to guard against the normal edge is good. But going to paranoiac extremes generally makes no sense.


Well said. That's my whole point about xp_CmdShell. If someone gets in deep enough (meaning with "SA" privs, in this case), you're dead even if it's turned off and depriving DBAs of its SA-only usage just doesn't make sense to me.

For the record, I'm also one of those folks that will allow it in carefully constructed application-facing stored procedures where the user doesn't actually have privs to run xp_CmdShell directly but that's a whole different argument.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1438815
Posted Thursday, April 4, 2013 9:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:27 PM
Points: 7,107, Visits: 12,657
Leaving the development aspects aside per Steve's earlier comment...

Why do folks need xp_cmdshell for admin tasks? I definitely have a sense of people's attachment and willingness to use xp_cmdshell despite the fact that it brings with it various persistent and latent security risks. Comments about how using xp_cmdshell makes things simpler or easier just do not compute. Maybe it is because early in my career I ran into more than one mess of a system that heavily leveraged xp_cmdshell where I was asked to rewrite xp_cmdshell out of the system and either consciously or subconsciously made up my mind never to use it in new work. I found other ways to do things without xp_cmdshell that suit me quite well and while the languages have changed the techniques I have developed have come in quite handy in distributed environments where I have to manage more than just a few instances. I think I am being productive, and my scripts tend to scale out to manage n-instances quite easily, but there is always room for improvement. What types of tasks does xp_cmdshell make so much easier? Or reframed, why can't you live without xp_cmdshell?


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1438861
Posted Thursday, April 4, 2013 9:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, September 19, 2014 1:39 PM
Points: 1,660, Visits: 4,748
This is beginning to sound like the "Why do we need Cursors?" debate. Just like cursors, I'd be highly sceptical why anyone would need to leverage xp_cmdshell in a stored procedure. Still it's a useful tool for ad-hoc sysadmin tasks, like when the sysadmin doesn't have a login on the host operating system.

Case in point: Just this morning I was asked to rename a database (AbcCorp -> AbcCorpOld) in the DEV and QA environments and then create a new database with the same name (AbcCorp) and empty tables. To keep the physical file names consistent with the logical database name, I also had to detach, re-name files, and then re-attach the old database. However, to rename the files, I had to use xp_cmdshell, because I don't have a local login on the host operating server.
Post #1438871
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse