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


Easy Error Trapping When Using xp_cmdshell


Easy Error Trapping When Using xp_cmdshell

Author
Message
Sylvia Moestl Vasilik
Sylvia Moestl Vasilik
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 393
Comments posted to this topic are about the item Easy Error Trapping When Using xp_cmdshell
Carlo Romagnano
Carlo Romagnano
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3836 Visits: 3276
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

I run on tuttopodismo
Noel McKinney
Noel McKinney
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2031 Visits: 797
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.
Pei Zhu-415513
Pei Zhu-415513
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 4018
Not sure what kind of security problem it could be if you use it properly, say use proxy account.
dough-378918
dough-378918
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 185
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.
noeld
noeld
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6924 Visits: 2048
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
Pei Zhu-415513
Pei Zhu-415513
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 4018
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".
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7120 Visits: 5314
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
Pei Zhu-415513
Pei Zhu-415513
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1521 Visits: 4018
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.
Elliott Whitlow
Elliott Whitlow
SSCertifiable
SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)SSCertifiable (7.1K reputation)

Group: General Forum Members
Points: 7120 Visits: 5314
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
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