December 23, 2007 at 9:11 am
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
December 23, 2007 at 9:19 am
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 23, 2007 at 9:27 am
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
December 23, 2007 at 2:23 pm
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.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
December 23, 2007 at 5:13 pm
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply