SQL Server Performance

XP_cmdShell

Discussion in 'Getting Started' started by sssudhakar, Jun 24, 2007.

  1. sssudhakar New Member

    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
  2. FrankKalis Moderator

  3. MohammedU New Member

  4. Madhivanan Moderator

    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
  5. FrankKalis Moderator

  6. Madhivanan Moderator

    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
  7. satya Moderator

    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.
  8. sssudhakar New Member

    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.
  9. FrankKalis Moderator

    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
  10. satya Moderator

  11. sssudhakar New Member

    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.

  12. MohammedU New Member

  13. satya Moderator

    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.
  14. sssudhakar New Member

    Thanks. I will set up the proxy user and use it for the XP_Cmdshell.
  15. satya Moderator

    ... 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.

Share This Page