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 «««23456»»»

The Command Shell Expand / Collapse
Author
Message
Posted Monday, April 1, 2013 7:38 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:18 AM
Points: 139, Visits: 615
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?"
Post #1437433
Posted Monday, April 1, 2013 10:38 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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
"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 #1437686
Posted Monday, April 1, 2013 10:57 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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
"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 #1437692
Posted Monday, April 1, 2013 11:38 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 8:22 AM
Points: 51, Visits: 178
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?
Post #1437704
Posted Tuesday, April 2, 2013 5:39 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 36,794, Visits: 31,253
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
"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 #1437835
Posted Tuesday, April 2, 2013 7:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 17, 2013 12:58 PM
Points: 39, Visits: 30
I would be concerned about Windows Admins using this feature and accidentally causing problems in the SQL Server.
Post #1437882
Posted Tuesday, April 2, 2013 7:58 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 1:49 PM
Points: 371, Visits: 955
Unless they have bad intent it is unlikely the Windows Admins know about cmd shell.

Cheers
Post #1437899
Posted Tuesday, April 2, 2013 8:14 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:10 PM
Points: 1,605, Visits: 4,595
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.
Post #1437914
Posted Tuesday, April 2, 2013 11:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 11:45 PM
Points: 7,094, Visits: 12,582
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
Post #1438023
Posted Wednesday, April 3, 2013 8:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 389, Visits: 2,317

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.
Post #1438400
« Prev Topic | Next Topic »

Add to briefcase «««23456»»»

Permissions Expand / Collapse