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 12345»»»

If you could use xp_CmdShell securely, would you? Expand / Collapse
Author
Message
Posted Tuesday, August 16, 2011 10:53 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 35,983, Visits: 30,273
Who knows what your reason for doing so may be. Maybe it's to launch some PowerShell functionality from T-SQL. Maybe it's to do BCP OUT or some file handling. Whatever it is, if you could do it from T-SQL in a super secure manner where the user of a stored proc with xp_CmdShell in it couldn't run xp_CmdShell directly him/herself and had nothing more than standard PUBLIC privs on the given database, would you use xp_CmdShell?

Just to throw my use for it into the ring to get things started, I use xp_CmdShell in the secure manner described for ETL (both in and out) along with some necessary file handing in the very secure method I suggested instead of using SSIS packages. I've also used xp_Cmdshell with VBA to create and export to some rather colorful spreadsheets. I understand that Powershell is even better for that task and am considering making calls to Powershell from T-SQL to do just that.

And, no... this isn't an SSIS bashing party. I just want to know, if you could use xp_CmdShell securely, what might you use it for and would you actually use it? xp_CmdShell bashing and praising is absolutely welcome. Just be careful not to bash me on the subject. I'm just trying to get a friendly conversation started on what normally terns out to be a highly controversial subject because I'd like to know what others think on the subject.



--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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1160922
Posted Wednesday, August 17, 2011 1:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:32 AM
Points: 5,963, Visits: 12,850
Its always been given bad press in the past due to the vulnerability. Its a feature hackers knew was there and could be exploited to harmful use.
I have an admin script that enables the feature, does the work and then disables it afterwards, but i don't use it widely


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1160970
Posted Wednesday, August 17, 2011 1:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, December 03, 2013 11:33 PM
Points: 1,789, Visits: 1,013
Probably not in production. While there are secure ways of using xp_cmdshell. I can't/won't trust everybody to use it the way its meant to be. I could leave the company in a few years and don't really know how others would use it and I wouldn't want to leave a bad legacy.

Jayanth Kurup
Post #1160972
Posted Wednesday, August 17, 2011 2:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, April 18, 2014 6:27 AM
Points: 6,997, Visits: 8,411
Perry Whittle (8/17/2011)
Its always been given bad press in the past due to the vulnerability. Its a feature hackers knew was there and could be exploited to harmful use.
I have an admin script that enables the feature, does the work and then disables it afterwards, but i don't use it widely

+1

I wouldn't advise it for in-transaction usage, because external factors may cause all kind of hickups.

Exports, run of apps using xp_cmdshell, ... I try to launch using SQLAgent as much as possible. These jobs are launched using sql alerts or SSB to be able to take control ( enable/disable ) of the system for maintenance / debug purposes.


Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- How to post Performance Problems
- How to post data/code to get the best help


- How to prevent a sore throat after hours of presenting ppt ?


"press F1 for solution", "press shift+F1 for urgent solution"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #1160983
Posted Wednesday, August 17, 2011 6:50 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, March 13, 2014 9:27 PM
Points: 3,283, Visits: 6,670
We do not use xp_cmdshell because of the bad rep. The only time we used it was when we were migrating to the new server. At that time it was enabled using sp_configure and then disabled.
I have read lot of articles saying how xp_cmdshell can be used to hack your DB server.


-Roy
Post #1161103
Posted Wednesday, August 17, 2011 7:27 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, March 24, 2014 3:11 AM
Points: 1,151, Visits: 4,600
I tried to avoid using xp_cmdshell but sometime I can't.

Most of time I used the xp_cmdshell for deleting the old backups because the drive got full and apps goes down. Once i had done I disabled it. (even scripts too)

The reason for using this some of the servers I don't have MSTSC access (OS level). Whatever the reason they didn't give(may be haven’t trust us).


Muthukkumaran Kaliyamoorthy

Helping SQL DBAs and Developers >>>SqlserverBlogForum
Post #1161128
Posted Wednesday, August 17, 2011 9:17 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:08 AM
Points: 2,674, Visits: 695
If I need to use xp_cmdshell then I do - it depends upon client requests and their perception of course, There's always a certain amount of using what you have to achieve an end result, preferably without re-inventing the wheel to do so.

The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Post #1161231
Posted Wednesday, August 17, 2011 9:37 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 35,983, Visits: 30,273
Perry Whittle (8/17/2011)
Its always been given bad press in the past due to the vulnerability. Its a feature hackers knew was there and could be exploited to harmful use.
I have an admin script that enables the feature, does the work and then disables it afterwards, but i don't use it widely


Thanks for the response, Perry. Understood and you've cited one of the most common fears.

Exploring that fear and reasoning a bit more, how is it that hackers get in? The equally most common answer is usually through the GUI and the associated login(s). If the GUI login(s) had ONLY "PUBLIC" privs with explicit privs to only EXECUTE stored procedures and didn't have even "Datareader" or "Datawriter", can you think of a way that a hacker could get in with enough privs (ie: "SA") to 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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1161250
Posted Wednesday, August 17, 2011 9:47 AM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 35,983, Visits: 30,273
Jayanth_Kurup (8/17/2011)
Probably not in production. While there are secure ways of using xp_cmdshell. I can't/won't trust everybody to use it the way its meant to be. I could leave the company in a few years and don't really know how others would use it and I wouldn't want to leave a bad legacy.


I have a huge appreciation for that, Jayanth... not trusting others to do things correctly either because of perceived inconvenience on their part or a simple lack of knowledge is a problem for many DBA's including myself. It's a bit of paranoia that good DBA's not only agree with, but strongly embrace, as well.

Let me change the question a bit to match this particular problem. What privs do GUI login(s) currently enjoy against your production systems? How about individual non-DBA users (including but certainly not limited to Developers)? Do they have at least "DataReader/DataWriter" privs instead of only the privs to EXECUTE "parameterized" stored procedures? If so and someone deletes or overwrites a bunch of data, would that be considered to "leave a bad legacy", 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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(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 #1161258
Posted Wednesday, August 17, 2011 9:51 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 1:11 PM
Points: 3,081, Visits: 11,230
I think xp_cmdshell can be used securely if you take the trouble to set it up correctly.

The one thing that I don't like is that you basically have two levels of privilege, users with sysadmin that run xp_cmdshell under the context of the service account, and non-sysadmin users who run in the context of the proxy account (or nor at all if there is no proxy account).

It would be better if you could have multiple proxy accounts, and had the ability to assign proxy accounts at the level of login, database, database user, or stored procedure. Then it would be far easier to allow the use of xp_cmdshell with the proper level of security and no more than what is needed.



Post #1161264
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse