August 18, 2011 at 7:08 am
Here is the situation at our place. We have only two least privileged users that access the DB from the front end (xp_cmdshell is disabled). We have been hacked twice. Both of the times they used the vulnerability of IIS to do hack into our Webserver. Once they were inside they got into our domain. After that we were at their mercy. They put something called psexec.exe or something like that and created a login with sysadmin privilege. Before they could damage us a lot, we were able to stop them.
My take is you can take as much precaution as you want (And you should) but if the infrastructure is weak, there is no way of escaping the threat whether you have xp_cmdshell off or not.
Now the servers are patched more efficiently, a better IDS is in place and we actually do monitoring on the network layer.
-Roy
August 18, 2011 at 7:48 am
Jeff Moden (8/17/2011)
Marios Philippopoulos (8/17/2011)
Jeff Moden wrote:More specific to the problems you’ve resolved through the use of thoughtful and narrowly-scoped CLRs, my question on that would be, why do most (and I do mean “almost all”) people think such a thing isn’t possible through the use of xpCmdshell via a T-SQL based stored procedure? It certainly is possible to make T-SQL stored procedures do something very narrow in scope even with xpCmdshell based only on proper and “cleansed” input parameters. It certainly is possible to grant execute privs to “PUBLIC- only” users that don’t even have “DataReader” privs to execute the proc even though the proc contains a call to xpCmdShell without giving those users any privs to execute xpCmdShell directly. And, the users can’t even see what’s inside the proc. What is the difference between such a T-SQL stored procedure and the narrow-scope CLR’s you and others have created to solve such problems?
One reason I can think of why, to me, CLR would be preferable to T-SQL: it introduces an extra layer of complexity to developing and deploying code as an assembly (say with EXTERNAL_ACCESS permissions), as opposed to a T-SQL module.
CLR code within an assembly is not as "visible" or "hackable" as code in a T-SQL sproc, and, therefore not as easy to break through and manipulate.
Seems to me this code may be more secure in that sense than T-SQL code for accessing resources outside the SQL process.
That's not to say I don't use xpcmdshell from time to time; but I generally keep it off on all servers.
That's a very good point and a good point of view. However, if (as I've already stated) you have users with only "PUBLIC" privs and they've been granted only EXECUTE privs on a given stored procedure, who can see the code inside that given stored procedure?
Again, thank you for the wonderfully thoughtful responses. This is getting really good! 🙂
Sproc code is more easily accessible than a CLR assembly, even if the production server has been highly secured.
Databases are usually restored from prod backups on QA and dev environments, and those are generally non-secure environments, accessed by a lot of users.
CLR code is contained within a dll, and, although there are ways to hack into that, it is not as simple as getting to a plain-text sproc on a DEV/QA server.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 18, 2011 at 8:21 am
I don't bother with xp_cmdshell these days, because I can't think of anything I can do with it that can't be done better with CLR or SSIS. It's not that I'm pathologically afraid of xp_cmdshell, it's just that I can do the same things through better means otherwise.
There are distinct security advantages for CLR over xp_cmdshell, even with rights escalation issues. But if you have rights escalation issues on your server, the least of your concerns is whether or not xp_cmdshell is enabled, because you've got much bigger issues than that! If you can avoid rights escalation, xp_cmdshell and CLR can both be reasonably secure.
So, for me it's not about security when choosing which to use, it's about functionality.
For example, if I need to find out what files are in a directory, I can do it through either. BUT, through CLR, I can also easily get the size, last modified date, archived/read-only attributes, et al, and can do so across domains if needed, while it's significantly more difficult to get those things through xp_cmdshell.
If I need to track total disk space, used disk space, and available disk space, on a network drive, I can do that easily through a simple VB.NET proc, but it's much more complex to get through xp_cmdshell. (This comes in handy when running automated tasks like, "Is there still room on my remote DR server for log shipping to it?")
If I need to delete a single file, and already have the file name, it's easier through xp_cmdshell, but I won't get the error-trap/handling options that .NET gives me. Big deal? Not really. But why not take advantage of these things since we're talking about only a very small amount of increased work for a very large increase in functionality?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 18, 2011 at 8:44 am
My main use of xp_cmdshell is for log shipping, I use my own ( have since sql 6.0 ). Although I am looking to test using DFSR when we lift the o/s win 2008 R2, this means I only run as the service account.
I don't let users access to xp_cmdshell unless the client requests it. I am looking to powershell and the CLR but as always it's the client who has the ultimate say so. It's taken several years to get one client to accept that using the CLR isn't going to break the system, and despite the offer of a good bottle bottle of wine for the first production CLR function ( that doesn't contain t-sql 🙂 ) we still don't have a function deployed 9 months on!
On the security aspect well, if a hacker gains sa then xp_cmdshell being there or not doesn't make any difference to be honest. I do the best I can with security, again within what the client wants - It's all well and good but we have a well known accounts/hr package where all connections are as sa - what can you do?
It's very good thread Jeff - very interesting and certainly one which stirs the old grey matter, thanks.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
August 18, 2011 at 9:21 am
The 95% of the time where I would use this, and find it useful, is connected to the bcp utility. Frequently users need to be able to run a process which creates an output file which is submitted to another vendor or software. The user kicks off a stored proc from a front end, most of the time with no input, just a click this interface.
August 18, 2011 at 12:48 pm
muthukkumaran Kaliyamoorthy (8/17/2011)
Jeff Moden (8/17/2011)
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,
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?
Yep most of the servers i am the only admin(SA) but some times the developers also needs xp_cmdshell to do some activities (BCP,ftp...)
Absolutely i have disabled the xp_cmdshell once i the tasks completed. Even if its stored procedures too.
Who or what else has "SA" privs on your production server(s)
It depends. most of the servers i am the only but some of the customer servers many others too.
how do you prevent them from simply turning xp_CmdShell back on?
i tried using PBM but what to do they have also sysadmins.
Thanks for the thread jeff.
Understood but my whole point is that if only people who have "SA" privs (or special exec privs on xp_Cmdshell itself) can get to xp_CmdShell and you let those people turn it on themselves, why do you even bother turning it off? It's not like it prevents a security hole or anything. Needing to use BCP for output is no excuse for people having privs to run xp_CmdShell directly, either. You should build a proc that does it and give them permissions to run the proc, instead.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2011 at 12:50 pm
David Lester (8/18/2011)
The 95% of the time where I would use this, and find it useful, is connected to the bcp utility. Frequently users need to be able to run a process which creates an output file which is submitted to another vendor or software. The user kicks off a stored proc from a front end, most of the time with no input, just a click this interface.
Perfect use, in my eyes, David.
Because of other people's responses in this area, I have to ask... why aren't you using SSIS for such a thing?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2011 at 1:30 pm
colin.Leversuch-Roberts (8/18/2011)
My main use of xp_cmdshell is for log shipping, I use my own ( have since sql 6.0 ). Although I am looking to test using DFSR when we lift the o/s win 2008 R2, this means I only run as the service account.I don't let users access to xp_cmdshell unless the client requests it. I am looking to powershell and the CLR but as always it's the client who has the ultimate say so. It's taken several years to get one client to accept that using the CLR isn't going to break the system, and despite the offer of a good bottle bottle of wine for the first production CLR function ( that doesn't contain t-sql 🙂 ) we still don't have a function deployed 9 months on!
[font="Arial Black"]On the security aspect well, if a hacker gains sa then xp_cmdshell being there or not doesn't make any difference to be honest.[/font] I do the best I can with security, again within what the client wants - It's all well and good but we have a well known accounts/hr package [font="Arial Black"]where all connections are as sa - what can you do?[/font]
It's very good thread Jeff - very interesting and certainly one which stirs the old grey matter, thanks.
Excellent feedback. Thanks, Colin.
I've highlighted two of your statements in the quote above. You're absolutely correct and it's something that a whole lot of people seem to have forgotten about where "it's" refers to correct overall security.
The second highlighted statement falls into the same category and I definitely sympathize with your problems and concerns there. It would take a bit of time to do but (and I realize that I’m preaching to the proverbial choir here but still have to say it to feel better about it 😉 ) all connections should be limited to "PUBLIC" with EXEC privs only on the appropriate stored procedures to get the job done whether or not calls to xp_CmdShell are used or not. Convincing people that should be true is a truly monumental task and I don’t envy anyone that takes the task on.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2011 at 1:45 pm
Marios Philippopoulos (8/18/2011)
Databases are usually restored from prod backups on QA and dev environments, and those are generally non-secure environments, accessed by a lot of users.
That may be but there's no reason why anyone would need to turn off xp_CmdShell if they allow users to turn it back on. It prevents no security holes to do so. Neither does it prevent any security holes even if you're the only one with "SA" privs and haven't granted any explicit "EXEC" privs on xp_CmdShell (and you shouldn't) because non-SA users can't use it anyway. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2011 at 1:56 pm
Jeff Moden (8/18/2011)
David Lester (8/18/2011)
The 95% of the time where I would use this, and find it useful, is connected to the bcp utility. Frequently users need to be able to run a process which creates an output file which is submitted to another vendor or software. The user kicks off a stored proc from a front end, most of the time with no input, just a click this interface.Perfect use, in my eyes, David.
Because of other people's responses in this area, I have to ask... why aren't you using SSIS for such a thing?
I'd say it's probably because running SSIS on-demand is more complex and error-prone than running bcp on-demand.
If all you're doing is exporting some rows to a text file, SSIS is serious overkill.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 18, 2011 at 2:04 pm
Sadly the reason I don't use SSIS is an IT political one. By sticking to as much version independent TSQL and pure stored procs as I can, I am not constantly having to recode projects based off someone's new whim of what should be done.
ie: Oh, so your downgrading this 2005 box to 2000? No problem, just have to copy the procs.
six weeks later... Hmm? That 2000 box is not working? Your moving it to 2008 R2? Sure, great, just need to copy the procs. Huh? Oh, a week or two to move them. Hmm? The other developers say it will take a year? I suppose it would, since they re-coded it to the 2000 tools.
6 months later... Now we have to use nothing but dotNet to get things to users? Ok, lets see, just have to call the procs from dotNet. Oh, the other developers are angry about it? Probably, did they even manage to convert half of their projects to the 2008 R2 change? No? Well there you go.
and so on.
Life is always intersting here. Wait isn't that a curse from somewhere? "I wish you to have an interesting life."
August 18, 2011 at 2:08 pm
David Lester (8/18/2011)
Sadly the reason I don't use SSIS is an IT political one. By sticking to as much version independent TSQL and pure stored procs as I can, I am not constantly having to recode projects based off someone's new whim of what should be done.ie: Oh, so your downgrading this 2005 box to 2000? No problem, just have to copy the procs.
six weeks later... Hmm? That 2000 box is not working? Your moving it to 2008 R2? Sure, great, just need to copy the procs. Huh? Oh, a week or two to move them. Hmm? The other developers say it will take a year? I suppose it would, since they re-coded it to the 2000 tools.
6 months later... Now we have to use nothing but dotNet to get things to users? Ok, lets see, just have to call the procs from dotNet. Oh, the other developers are angry about it? Probably, did they even manage to convert half of their projects to the 2008 R2 change? No? Well there you go.
and so on.
Life is always intersting here. Wait isn't that a curse from somewhere? "I wish you to have an interesting life."
Ouch!!!!
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 18, 2011 at 2:26 pm
Yes, but I am nearly in the victory position now. This week ends the contract with the 4th DBA consultant they hired to come in to prove that the way I use SQL, and the standards I am fighting for are wrong, and theirs are correct.
Why the 4th? The first three agreed with me, and echoed every suggestion I have given over the last several years. The 4th? He agrees with my ideas as well. Now we all made a promise that whatever this last one says is what will become the standard.
(Ok, I presume they will ignore the recommendations and continue on with their disasterous methods.)
August 18, 2011 at 2:45 pm
David Lester (8/18/2011)
Sadly the reason I don't use SSIS is an IT political one. By sticking to as much version independent TSQL and pure stored procs as I can, I am not constantly having to recode projects based off someone's new whim of what should be done.
Heh... "Sadly"??? 😛 I'm no "Luddite" and have no objection to other people using SSIS for their tasks... but you've hit the proverbial nail on the head as to why I try to do everything through T-SQL. As they say, "been there, done that". 🙂
You sound like a person that I'd like to work with someday, David.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 18, 2011 at 2:50 pm
GSquared (8/18/2011)
Jeff Moden (8/18/2011)
David Lester (8/18/2011)
The 95% of the time where I would use this, and find it useful, is connected to the bcp utility. Frequently users need to be able to run a process which creates an output file which is submitted to another vendor or software. The user kicks off a stored proc from a front end, most of the time with no input, just a click this interface.Perfect use, in my eyes, David.
Because of other people's responses in this area, I have to ask... why aren't you using SSIS for such a thing?
I'd say it's probably because running SSIS on-demand is more complex and error-prone than running bcp on-demand.
If all you're doing is exporting some rows to a text file, SSIS is serious overkill.
Exactly what I was thinking. Thank you for this and the previous post, Gus. It's always a pleasure to hear your take on things.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 31 through 45 (of 93 total)
You must be logged in to reply to this topic. Login to reply