January 6, 2011 at 12:03 pm
Mentioned below are my few opinions/questions about how xp_cmdshell works in 2005 vs 2008
i) if i am right in 2005 as long as you have permissions to execute xp_cmdshell proc you could do anything on the server from the client like shutdown any service or box itself because by default on windows 2003 cmd shell be default runs under administrator.
ii) in windows server 2008 a user cannot shutdown any server using sql unless the user in admin on the box because by default cmd shell on windows 2008 doesnt run under admin context?
Is there any other difference, pros/cons of enabling xp_cmdshell in 2005 and 2008?
January 6, 2011 at 12:17 pm
In 2005/2000 and as far as I know in 2008, xp_cmdshell runs by default under the credentials of the connection it's being run from. The issue you mention is because, most of the time, it's set up to run under an escalated-privileges "proxy account". I could be wrong about it in 2008, since I haven't used it in a while (I use CLR procs instead), but I know that under 2000 and 2005, it could easily be limited as to what it can do. The main security issue with it is if it's used in any context that allows rights escalation, or any connection with sa as the login or a domain/system admin credential, or if the proxy account has too many rights.
- 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
January 6, 2011 at 2:23 pm
My understanding is that for non-sysadmin users (2000/2005/2008) that are able to use xp_cmdshell it will use a proxy account, where that account is setup changed (I think) in 2005/2008, but if you are not a sysadmin then you will run in the context of the proxy user. This user generally has limited rights and is almost NEVER configured as a Local Admin on the box and NEVER (EVER!) a Domain Admin.
Like GSquared indicated, I try REALLY hard to never enable xp_cmdshell and build SQL CLR code to call executables explicitly. They are built to call either one or a very defined set of executables. They are also designed to handle (reject) SQL/code injection.
CEWII
January 6, 2011 at 3:47 pm
thanks . Lets say there is a sql login "test" who has sa priviledge on sqlserver ServerA and doesnt have admin priviledge on the ServerA, still he can stop any service using command shell using sql management studio on windows 2003 server and he cannot stop on a windows 2008 server. I know this is one of the threats apart from that what are the other threats/issues by enabling xp_cmdshell.
January 7, 2011 at 6:25 am
Entirely possible. I haven't used xp_cmdshell in years. What are you using it for?
- 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
January 7, 2011 at 6:53 am
GSquared (1/6/2011)
I haven't used it in a while (I use CLR procs instead)
i am also using sysadmin credential for xp_cmdshell.
can you help me out how you are using CLR function for it ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 7, 2011 at 9:50 am
Being sa on SQL Server, either on Win2K3 or Win2K8 doesn't give you rights to stop any service. That depends on your rights in the underlying Windows host.
In 2008, the rights are those of the "service account" of the db engine. http://msdn.microsoft.com/en-us/library/ms175046%28v=SQL.100%29.aspx
In 2005, this is the same http://msdn.microsoft.com/en-us/library/ms175046%28v=SQL.90%29.aspx
This is if you do not have some proxy set. My guess is that you are seeing the capability on W2K3 based on some rights differences for the service account or proxy.
January 7, 2011 at 11:24 am
Bhuvnesh (1/7/2011)
GSquared (1/6/2011)
I haven't used it in a while (I use CLR procs instead)i am also using sysadmin credential for xp_cmdshell.
can you help me out how you are using CLR function for it ?
What are you using xp_cmdshell for? That'll determine how to approach a CLR solution.
- 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
January 7, 2011 at 11:26 am
Steve, I have to partially disagree.. It is very common to make the SQL service account a local admin on the box, not a best practice but still very common. Since when running xp_cmdshell as a sysadmin translates to running AS the server any commands executed by xp_cmdshell are executed by the user that SQL starts as. Therefore any rights that SQL has on the box the sysadmin using xp_cmdshell does as well.
When running as a non-sysadmin user the proxy comes into play. Although I can't remember for sure I don't think that you can allow non-sysadmin users to execute xp_cmdshell if a proxy is not setup. You can give them rights to execute the sproc but I think it gives an error.
Reading your response again, I'm not sure we are disagreeing..
CEWII
January 7, 2011 at 1:01 pm
Elliot, we are not disagreeing. I was responding to the OP as a clarification. What you have written is correct, but this is not a difference because of the OS change. The underlying OS does not dictate how xp_cmdshell behaves, it is the permissions and configuration of the service account/proxy.
January 7, 2011 at 1:07 pm
OK. I get it now.Basically we are using xp_cmdshell to transfer files..I have heard many saying xp_cmdshell should not be enabled but i do not see a reason why? Can you please point me to an article or mention few cases where enabling xpcmdshell would be threat? Thanks
January 7, 2011 at 1:12 pm
The problem with xp_cmdshell is that, if the account privileges are set even slightly wrong, it allows all kinds of nasty things to be done to the server.
It can, if the proxy account is enabled, allow a malicious connection to do things like issue a shut-down command, or open FTP connections, or install malware, all depending on the rights available.
A CLR procedure can only do the things that it's got in its code. If it takes a file path+name and moves it to a target directory, it might have some exploitable weaknesses, but it can't do anything other than move files. That could be its own problem, if someone moves critical files to a temp directory for example, but the damage can easily be mitigated or prevented in most cases.
Of course, that assumes you've locked down CLR security reasonably well, which is easier to do than xp_cmdshell security.
- 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
January 7, 2011 at 3:51 pm
GSquared (1/7/2011)
The problem with xp_cmdshell is that, if the account privileges are set even slightly wrong, it allows all kinds of nasty things to be done to the server.It can, if the proxy account is enabled, allow a malicious connection to do things like issue a shut-down command, or open FTP connections, or install malware, all depending on the rights available.
A CLR procedure can only do the things that it's got in its code. If it takes a file path+name and moves it to a target directory, it might have some exploitable weaknesses, but it can't do anything other than move files. That could be its own problem, if someone moves critical files to a temp directory for example, but the damage can easily be mitigated or prevented in most cases.
Of course, that assumes you've locked down CLR security reasonably well, which is easier to do than xp_cmdshell security.
M only aguement is if i do not have proxy set up for xp_cmdshell a user can only harm the server if he has sa priviledge. Lets say there is a usera with sa priviledge on sql ServerA but does not have admin priviledge on the box itself. However since he has sa priviledge he could enable xp_cmdshell and do what he wants on the box through sql client. So my feeling is if we do not have proxy accounts setup we do not need to worry about xp_cmdshell..does that make sense?
January 7, 2011 at 4:13 pm
sqldba_icon (1/7/2011)
GSquared (1/7/2011)
The problem with xp_cmdshell is that, if the account privileges are set even slightly wrong, it allows all kinds of nasty things to be done to the server.It can, if the proxy account is enabled, allow a malicious connection to do things like issue a shut-down command, or open FTP connections, or install malware, all depending on the rights available.
A CLR procedure can only do the things that it's got in its code. If it takes a file path+name and moves it to a target directory, it might have some exploitable weaknesses, but it can't do anything other than move files. That could be its own problem, if someone moves critical files to a temp directory for example, but the damage can easily be mitigated or prevented in most cases.
Of course, that assumes you've locked down CLR security reasonably well, which is easier to do than xp_cmdshell security.
M only aguement is if i do not have proxy set up for xp_cmdshell a user can only harm the server if he has sa priviledge. Lets say there is a usera with sa priviledge on sql ServerA but does not have admin priviledge on the box itself. However since he has sa priviledge he could enable xp_cmdshell and do what he wants on the box through sql client. So my feeling is if we do not have proxy accounts setup we do not need to worry about xp_cmdshell..does that make sense?
First, always worry about xp_cmdshell. The enabling of xp_cmdshell appears in the log I think, I have been in environments that have a trace running that captures ALL xp_cmdshell calls. If it is at all possible, do not enable xp_cmdshell, there must be really good reasons before enabling because the likelyhood of it being abused is pretty high. Like G, I have built SQLCLR objects to handle such things as moving/copying files, they often include some checks about where a file cannot be copied to or from, these are things that simply cannot be accomplished with xp_cmdshell. With SQL 2000 it was VERY difficult to get away with not turning it on, nowadays, not so much..
Steve Jones - SSC Editor (1/7/2011)
Elliot, we are not disagreeing. I was responding to the OP as a clarification. What you have written is correct, but this is not a difference because of the OS change. The underlying OS does not dictate how xp_cmdshell behaves, it is the permissions and configuration of the service account/proxy.
No problem..
CEWII
January 8, 2011 at 9:16 am
Javascript is amazing. It makes web pages so much more interactive and interesting. It runs in a fairly low privileged manner under your account, and it's an amazing language.
It's also responsible for some nasty cross site scripting exploits that have caused major issues for companies and individuals.
Anytime you open access to the OS, you are asking for a potential security hole. Using a low privileged account, no admin, no SA, is a good idea, but there are likely vectors that you and I have not thought about. The users might not have, but a Google search can turn up something to cause you problems with xp_cmdshell.
If you must do it, use a specific account you can track/audit/secure. Not the user's account, but one with other permissions that you can alter.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply