August 16, 2011 at 10:53 pm
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
Change is inevitable... Change for the better is not.
August 17, 2011 at 1:34 am
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" 😉
August 17, 2011 at 1:40 am
August 17, 2011 at 2:01 am
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
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- 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
August 17, 2011 at 6:50 am
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
August 17, 2011 at 7:27 am
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
https://www.sqlserverblogforum.com/
August 17, 2011 at 9:17 am
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.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 17, 2011 at 9:37 am
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
Change is inevitable... Change for the better is not.
August 17, 2011 at 9:47 am
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
Change is inevitable... Change for the better is not.
August 17, 2011 at 9:51 am
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.
August 17, 2011 at 9:53 am
ALZDBA (8/17/2011)
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.
As I said to Perry, I have a real appreciation for that. Let me ask this though. If an attacker can get into your system with sufficient privs (PUBLIC with DataReader/DataWriter), is isolating xp_CmdShell going to keep your system safe? If an attacker can get in as an "SA", will any of those good and thoughtful steps help? What if I told you (and, it's true) that you could actually delete the xp_CmdShell DLL and that an attacker could still execute CMD.EXE through the likes of OPENROWSET?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2011 at 9:56 am
Jeff Moden (8/17/2011)
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, the biggest problem in the past (SQL Server 2000 era) is that XP_CmdShell could not be disabled or removed. It was an open invite for a hacker to gain OS access through SQL Injection. Things have moved on a lot since those days, i see nothing wrong with using for administrative use, just ensure your script enables and disables it after use.
Getting in as SA boils down to permissions. I have in the past seen website connections using the SA account which i obviously vetoed immediately
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 17, 2011 at 9:58 am
Roy Ernest (8/17/2011)
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.
Understood and thank you for the response. That's exactly what this thread is meant to address. Despite the "bad rep" and visceral/primordial fear of using it that has been engrained in us all by such articles, would you use xp_CmdShell in production code if you personally found a way to make the use of xp_CmdShell in stored procedures absolutely safe short of someone hacking into your system as an "SA" (at which point, they can do what they want no matter what safeguards you've taken)?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2011 at 10:08 am
muthukkumaran Kaliyamoorthy (8/17/2011)
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).
Ah... there's the other side of the coin. Thank you for the response, Muthukkumaran.
As you have, some other folks in this thread have also justified the occasional need for the use of xp_CmdShell for administrative chores/tasks. I have a question for you that will help me solve one of the questions that I have about xp_CmdShell and the fear of using it, if you don't mind. Since you generally need to have "SA" privs to execute xp_CmdShell, why do you bother disabling xp_CmdShell at the server level when you are done with it as an "SA" prived DBA? Who or what else has "SA" privs on your production server(s) and how do you prevent them from simply turning xp_CmdShell back on?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 17, 2011 at 10:18 am
While they did make some improvements to how it operates (the fact that you can in fact disable it is very good) - I still don't understand why they keep making asinine choices as to default security. Specifically - I don't understnad why it would EVER be a good thing to have it reuse context/privileges of the SQL Server service account. I just think they haven't gone far enough to put it in its own secure box from where you cannot get out, ever. There frankly has never been a rational reason to ever allow that to happen: if you need that much control of stuff happening outside, then you're using your SQL server to run things it has no business doing.
The single proxy account also bothers me, a lot: that just invites having an account with altogether to many rights.
Another way to describe in my mind would be - you should not have to work to MAKE it secure, you should have to work to make it not secure. My vote is still no.
If there was a 100% secure way, as you asked, to prevent these weaknesses - I'd be compelled to look again. That said, to each action there is an equal and opposite reaction, so I'd be looking hard for what got opened up in order to allow this 100% secure solution.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 15 posts - 1 through 15 (of 93 total)
You must be logged in to reply to this topic. Login to reply