February 1, 2010 at 8:46 pm
Comments posted to this topic are about the item Easy Error Trapping When Using xp_cmdshell
February 2, 2010 at 1:02 am
create table XPCmdShellOutput (OutputLine varchar(1000), ID INT IDENTITY(1,1))
Please add an identity column, so you can have output lines in the correct order
February 2, 2010 at 5:32 am
Dealing with xp_cmdshell is even easier than this... it shouldn't be enabled. Article really should have mentioned that somewhere; "...can be a security problem in some environments" is an understatement.
February 2, 2010 at 6:54 am
Not sure what kind of security problem it could be if you use it properly, say use proxy account.
February 2, 2010 at 7:16 am
Per auditor's requests, at our site we are going through and removing the use xp_cmdshell completely.
Some of the changes to replace the use of xp_cmdshell have involved a fair amount of processing code changes, but we've been able to find a way to get the same work done and not use this sp.
February 2, 2010 at 8:21 am
Pei Zhu-415513 (2/2/2010)
Not sure what kind of security problem it could be if you use it properly, say use proxy account.
A proxy account can "limit" the effect but because you are letting a remote user execute "WHATEVER" is in the sql server hard drive you are opening your doors to many kinds of exploits.
* Noel
February 2, 2010 at 9:23 am
I think that is your job to grant the permissions to the "remote user" on sql server side with what the user needs i/o "whatever".
February 2, 2010 at 10:00 am
When the only option I had was xp_cmdshell it was a godsend, however when SQL 2005/2008 came available we moved away from its use because of security considerations. We took a "just don't use it" position.
However with all that said, there were still times when command-line executables need to be called, what we did was to use SQLCLR to instantiate a process and call the command-line. But the SQLCLR function was only capable of calling that one command-line executable. We viewed this as the safest method since we controlled what could be called and how it was called and what command-line options could be passed. It also didn't use very much memory which can sometimes be an issue with SQLCLR. We had a very limited number of cases where we allowed command-line calls so our CLR code wouldn't change often.
CEWII
February 2, 2010 at 10:29 am
It is just a story "close one door/open another door". If you use sproc to wrap xp_cmdshell, you control what the sproc can do. Keep in mind the sqlclr is not easy to debug and not everyone knows how to write it/deploy it, etc , and it could cause memory leaking.
February 2, 2010 at 12:49 pm
I partially agree..
I don't wrap xp_cmdshell at all, I instantiate a process directly, this also means I can control security context. If you wrap xp_cmdshell in a sproc you are still using it.. I want it disabled entirely.
SQLCLR is harder to debug, but once you've done it once it is pretty straightforward.
As far as not everyone knowing how.. So? If you want to do it you LEARN, just like everything else.
Memory leaks are always a possibility, but I have had no issues with this methodology, mileage may vary..
Also, as a side note, a number of the newer datatypes are actually SQLCLR functions, geography/geometry are examples. So sayeth MS..
If it makes sense I use SQLCLR, but it has to make sense. I've done this stuff long enough to realize there are VERY few hard and fast rules, and the words "always" and "never" are rarely used.
CEWII
February 2, 2010 at 2:30 pm
It will challenge the religious dogma from all the jobsworths out there, but I would love to find just one REAL example of a security breach via xp_commandshell.
Yes a malicious employee or an external user of a poorly written site could potentially execute anything that’s permitted in the context of the SQL service account. But in the real world has anyone in a position to exploit this ever done anything they couldn’t have done via many other methods? Tie this loophole down by via well managed service accounts by all means, but don’t handicap database applications by accepting poor network security
Honestly! In a world where buffer overflows afflict almost every browser, most SQL servers operate as domain admin and the majority of Oracle shops still use passwords that haven’t changed for years there are better uses for everyone’s time.
February 2, 2010 at 3:06 pm
Pete,
I have to challenge a number of things in your post.
The biggest problem with security is apathy and lazyness. FAR too many people use highly privileged accounts for app logins, OR their application is architected in such a way that requires excess rights. A little SQL injection and BOOM, I do what I want to your server. There are many examples of this in the news and on this site.
I think you'll find the number of SQL Server logins that are Domain Admins is fairly low, that is lazy in the extreme. What I think you will see a LOT of is domain accounts as Local Admins, this seems to be the middle ground. It would be best to use a domain account just as a user, but most shops don't want to deal with that, per my experience. And if the machine will NEVER (hate that word) touch ANY machine but itself you could potentially use a local account, but that is a tall order..
CEWII
February 2, 2010 at 5:56 pm
Pei Zhu-415513 (2/2/2010)
It is just a story "close one door/open another door". If you use sproc to wrap xp_cmdshell, you control what the sproc can do. Keep in mind the sqlclr is not easy to debug and not everyone knows how to write it/deploy it, etc , and it could cause memory leaking.
You are quite correct. There are ways and times when xp_cmdshell can be used appropriately and correctly. You can limit who can use xp_cmdshell and how they can do it, and as you mentioned you can wrap it in a procedure and only allow access to the procedure so its use can be very limited. And by using a proxy account with limited permissions you can limit the worst case scenario.
There are certainly ways it can be abused (when I took over as DBA at one organization, I found ways that our developers effectively admin level control on the machines they had access to because they could run xp_cmdshell and it proxied out to an account with admin priveleges....I changed that quickly.) but this is true of sqlclr. Yes, there certainly ways that you can use sqlclr very securely, but the same is true of xp_cmdshell.
I tend to allow xp_cmdshell only where I see that it is truly justified and only with the lowest level of access it needs to get the job done. But then I say exactly the same thing about sqlclr, and we use both in my current organization in moderation and with care.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
February 2, 2010 at 6:01 pm
pete callaghan (2/2/2010)
It will challenge the religious dogma from all the jobsworths out there, but I would love to find just one REAL example of a security breach via xp_commandshell.Yes a malicious employee or an external user of a poorly written site could potentially execute anything that’s permitted in the context of the SQL service account. But in the real world has anyone in a position to exploit this ever done anything they couldn’t have done via many other methods? ...
I am quite curious about this myself. As I mentioned in another post, I have seen areas where an employee *could have* used xp_cmdshell to perform a privelege escalation or other forms of nastiness, but I have never heard of it actually being done anywhere. And I quickly closed that particularly loophole without disably xp_cmdshell. People could still have used xp_cmdshell to do horrible things, but after I changed the settings they could only have done things that they could have done in other ways.
Does anyone know of an actual real-world exploit of xp_cmdshell? What about one where the xp_cmdshell was set up intelligently and still used in an exploit?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
February 2, 2010 at 6:27 pm
put it simple. there is reason why it exists just like sqlclr. use it wisely it could be very useful component. do not limit youself.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply