Using xp_cmdshell

Question

Using xp_cmdshell

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
December 23, 2007 at 9:11 am

#127369

Hello All
Can someone give me advice, good and bad points to allowing the use of xp_cmdshell on any server. I am hoping to determine if it is safe to allow the use of this.
We have a vendor developed database app that uses this to bcp some data from a table. But we do not allow xp_cmdshell to be used or configured to use on any of our database boxes.
What type of security risks are involved with allowing the use of xp_cmdshell? I have only learned what I have read in the SQL BOL. Is there any way of using bcp without xp_cmdshell being configured? I understand that it cannot be.
Thanks
Andrew

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 1

The main risks are that someone can access resources outside SQL Server using a shell and could potentially damage the server. For bcp out, I don't know of another way. You can hide this by using SSIS or having a job run the bcp through xp_cmdshell, and not allowing normal accounts to run it, but that might not work in your situation.

If it's run often, and ad hoc, you might be stuck. If you can trace what it bcps and it's not too often, maybe you can disable this and have people just request the export from you.

AndrewSQLDBA SSC-Insane Points: 21827 More actions · Answer 2

That is what I thought also. I have tried to explain that to the company that wrote this aweful piece of software. I wish that the Exec's would have had us design and develop in house. They are pretty big on using canned products, and then are dis-satisfied with the time and the end results.

I have created a SSIS package for them to use. But trying to figure out how to fire off the package using a sproc has been difficult. The only thing I have right now is to create a Job and let the sproc fire off the job, that in turn fires off the package. Not the cleanest way. I am hoping to find some code examples on how to accomplish this directly in the sproc.

Thanks

Andrew SQLDBA

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 3

You can't do it directly with a SPROC unless you are using xp_cmdshell. The job fires dtsexec, a command utility, which loads and executes the package. The way you're doing it is probably the best.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 4

AndrewSQLDBA (12/23/2007)
That is what I thought also. I have tried to explain that to the company that wrote this aweful piece of software. I wish that the Exec's would have had us design and develop in house. They are pretty big on using canned products, and then are dis-satisfied with the time and the end results.
I have created a SSIS package for them to use. But trying to figure out how to fire off the package using a sproc has been difficult. The only thing I have right now is to create a Job and let the sproc fire off the job, that in turn fires off the package. Not the cleanest way. I am hoping to find some code examples on how to accomplish this directly in the sproc.
Thanks
Andrew SQLDBA

I have a similar problem with a real piece of crud stored procedure by a 3rd party... I'm slowly replacing one piece at a time. Pretty soon, we won't need the vendor. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)