Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Easy Error Trapping When Using xp_cmdshell Expand / Collapse
Author
Message
Posted Monday, February 1, 2010 8:46 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:24 PM
Points: 14, Visits: 364
Comments posted to this topic are about the item Easy Error Trapping When Using xp_cmdshell
Post #857694
Posted Tuesday, February 2, 2010 1:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:57 AM
Points: 2,587, Visits: 2,443
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
Post #857759
Posted Tuesday, February 2, 2010 5:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 4:12 PM
Points: 2,007, Visits: 768
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.
Post #857859
Posted Tuesday, February 2, 2010 6:54 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:25 AM
Points: 1,227, Visits: 3,251
Not sure what kind of security problem it could be if you use it properly, say use proxy account.
Post #857900
Posted Tuesday, February 2, 2010 7:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, June 18, 2014 7:01 AM
Points: 30, Visits: 156
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.
Post #857929
Posted Tuesday, February 2, 2010 8:21 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:34 AM
Points: 6,259, Visits: 2,031
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
Post #857989
Posted Tuesday, February 2, 2010 9:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:25 AM
Points: 1,227, Visits: 3,251
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".
Post #858056
Posted Tuesday, February 2, 2010 10:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #858080
Posted Tuesday, February 2, 2010 10:29 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:25 AM
Points: 1,227, Visits: 3,251
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.
Post #858105
Posted Tuesday, February 2, 2010 12:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, October 9, 2014 1:02 PM
Points: 6,032, Visits: 5,284
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
Post #858208
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse