XP_cmdShell | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

XP_cmdShell

Hi, I am using XP_cmdShell in storedprocedures to Copy,delete, FTP files. I am migrating database from SQL Server 2000 to SQL Server 2005. I have to find an alternative to XP_cmdshell to perform the tasks (Copy, Delte, FTP) from Stored Procedures. Please help. Regards

Ahem, why?
xp_cmdshell is still available in SQL Server 2005. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
And it is documented …
http://msdn2.microsoft.com/en-us/library/ms143729.aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

quote:Originally posted by sssudhakar Hi, I am using XP_cmdShell in storedprocedures to Copy,delete, FTP files. I am migrating database from SQL Server 2000 to SQL Server 2005. I have to find an alternative to XP_cmdshell to perform the tasks (Copy, Delte, FTP) from Stored Procedures. Please help. Regards
Did you try executing the same code in 2005? Madhivanan Failing to plan is Planning to fail
I think it is disabled by default in SQL Server 2005 and you need to enable it via the Surface Area Configuration for Features. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
quote:Originally posted by FrankKalis I think it is disabled by default in SQL Server 2005 and you need to enable it via the Surface Area Configuration for Features. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
Yes I think so as I also get permission denied error Madhivanan Failing to plan is Planning to fail
As suggested by Frank you have to enable it using Surface Area Configuration tool, as by default it will be in disable mode.
quote:This module has been marked OFF. Turn on ‘xp_cmdshell’ in order to be able to access the module."

Or you can use EXECUTE sp_configure ‘show advanced options’, 1
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure ‘xp_cmdshell’, ‘1’
RECONFIGURE WITH OVERRIDE
GO
EXECUTE sp_configure ‘show advanced options’, 0
RECONFIGURE WITH OVERRIDE
GO
Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Yes, what ever you guys said is true. The XP_cmdShell is available and we have to configure it to use it. Since the XP_CmdShell runs under security context and you need a SYSADMIN privilege to run the sp. As per my conpanies new policy Sysadmin rights will not be given to the users/application user to run the sp. So, I have to find an alternate solution.
Have you had a look at the new security features in SQL Server 2005. Users don’t need sysadmin privileges. Check out EXECUTE AS, for example. —
Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
If not you have to go through the proxy account route,http://www.microsoft.com/technet/prodtechnol/sql/2005/newsqlagent.mspx fyi in addition to books online refrence. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
The explanation I got from my SQL Server team is: XP_cmdshell spawns a windows command shell and executes in the security context of the SQL server serice account which leads o security vulnerability.
There is always pros and cons of these procedures…
Check the following…
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops3.mspx
http://www.databasejournal.com/features/mssql/article.php/3372131
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

As long as you have tied down the XP_CMDSHELL to SYSADMINS it will not be a problem, only for non-sysadmin access you have to use PROXY setup. So far no issues or security threats at our side! Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
Thanks. I will set up the proxy user and use it for the XP_Cmdshell.
… let us the feedback too that can help others too, fyi Proxy account settings are documented in BOL. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>