Easy Error Trapping When Using xp

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Sylvia Moestl... » Easy Error Trapping When Using xp_cmdshell

17 posts, Page 1 of 2

1 2 »»

Easy Error Trapping When Using xp_cmdshell

Rate Topic

Display Mode

Topic Options

Author

Message

Sylvia Moestl Vasilik

Posted Monday, February 01, 2010 8:46 PM

Grasshopper

Group: General Forum Members
Last Login: Saturday, March 16, 2013 4:03 PM
Points: 14, Visits: 336

Comments posted to this topic are about the item Easy Error Trapping When Using xp_cmdshell

Post #857694

Carlo Romagnano

Posted Tuesday, February 02, 2010 1:02 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 2:04 AM
Points: 1,968, Visits: 1,819

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

Noel McKinney

Posted Tuesday, February 02, 2010 5:32 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, April 05, 2012 2:35 PM
Points: 2,007, Visits: 767

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

Pei Zhu-415513

Posted Tuesday, February 02, 2010 6:54 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:53 PM
Points: 1,050, Visits: 2,764

Not sure what kind of security problem it could be if you use it properly, say use proxy account.

Post #857900

dough-378918

Posted Tuesday, February 02, 2010 7:16 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, October 31, 2012 1:28 PM
Points: 21, Visits: 121

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

noeld

Posted Tuesday, February 02, 2010 8:21 AM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260, Visits: 1,977

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

Pei Zhu-415513

Posted Tuesday, February 02, 2010 9:23 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:53 PM
Points: 1,050, Visits: 2,764

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

Elliott Whitlow

Posted Tuesday, February 02, 2010 10:00 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

Pei Zhu-415513

Posted Tuesday, February 02, 2010 10:29 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 7:53 PM
Points: 1,050, Visits: 2,764

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

Elliott Whitlow

Posted Tuesday, February 02, 2010 12:49 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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 »

17 posts, Page 1 of 2

1 2 »»

Permissions