March 11, 2009 at 12:09 am
Hi All,
As i am new to Sql server , I want to know how to work with xp_cmdshell. what is the use of it and please give a simple example to know the functionality of it.
Thanks In Advance.
Regards,
Vijay Singh.:)
March 11, 2009 at 12:48 am
Please go through this
http://msdn.microsoft.com/en-us/library/aa260689(SQL.80).aspx
March 12, 2009 at 12:15 pm
You should also have a look at a related issue:
How do I secure a SQL Server extended stored procedure?
Written By: Edgewood Solutions Engineers -- 9/14/2006
http://www.mssqltips.com/tip.asp?tip=961
Regards
March 12, 2009 at 12:24 pm
I would recommend you try to get by without it. If you have questions about a specific thing you are planning, please start a new thread and see if there are alternatives.
March 12, 2009 at 12:30 pm
Steve Jones - Editor (3/12/2009)
I would recommend you try to get by without it. If you have questions about a specific thing you are planning, please start a new thread and see if there are alternatives.
Would you please be specific as to why this approach is to be avoided ?
March 12, 2009 at 12:56 pm
The first rule with xp_cmdshell is don't use it unless you absolutely have to.
The second rule is, if you think you have to use it, you're probably wrong.
All that aside, if you're using SQL 2005, there's very little you can do with xp_cmdshell that you can't do better with CLR. SQL 2000, that doesn't apply.
The main problem with xp_cmdshell is that it usually opens up all kinds of security holes, just by being enabled.
Let's say you have a proc that uses xp_cmdshell to delete some files off the hard drive, for whatever reason. That means the login that runs that proc needs to have the rights to work in the file system of the server (or, worse yet, on a network share). That means, if someone can get an SQL injection attack into your system, they can do things to your file system. Bad enough that they can mess with your databases, but now they might be able to do things like delete files, or load malmare onto your server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 12, 2009 at 1:01 pm
I was talking about using the bcp utility, accessing the file system, generating Excel spreadsheet.
Yes, this was in SS2K, not SS2K5 of course -- now less and less appropriate to this forum.
Still, what do you think of the advice givejn in the link I posted ?
Regards
March 13, 2009 at 9:19 am
J (3/12/2009)
I was talking about using the bcp utility, accessing the file system, generating Excel spreadsheet.Yes, this was in SS2K, not SS2K5 of course -- now less and less appropriate to this forum.
Still, what do you think of the advice givejn in the link I posted ?
Regards
I generally try to use OpenRowset instead of BCP, and DTS/SSIS to create files (including Excel files) from SQL data.
Again, if the best solution to you is to open up xp_cmdshell and allow access to it, do so. It is the right tool for some jobs. It's just also a security risk, so if you use it, use it for the right reasons. That's all I'm saying. I consider it a sort of last resort, like cursors. But it is a valid resort, just like cursors, so long as it isn't the first tool you reach for, but is just about the last one, if not actually the last one.
The same does for go other extended stored procs, in many cases. The article you linked to has some good advice on that.
Like all security advice, it comes down to a balance between ease-of-use and functionality, vs security. The easier something is to use and the more it can do, the hard it is to make it secure. The more secure something is, the harder it is to make it do anything.
If gold's function is to look pretty, then the gold in Fort Knox isn't doing what it's supposed to, but it is highly secure. On the other hand, a gold statue on a street corner is doing what it's supposed to, but isn't very secure at all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply