Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


If you could use xp_CmdShell securely, would you?


If you could use xp_CmdShell securely, would you?

Author
Message
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45191 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8789 Visits: 16560
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" ;-)
Jayanth_Kurup
Jayanth_Kurup
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2093 Visits: 1351
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
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6976 Visits: 8839
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 w00t

- 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" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2494 Visits: 6852
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
muthukkumaran Kaliyamoorthy
muthukkumaran Kaliyamoorthy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1225 Visits: 4840
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

colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2725 Visits: 715
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/
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45191 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45191 Visits: 39925
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Michael Valentine Jones
Michael Valentine Jones
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3260 Visits: 11771
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search