locked
xp_cmdshell not working, returns NULL RRS feed

  • Question

  • Hi,

    xp_cmdshell is intermittent working recently.

    It was working fine, and we just upgraded from 2005 to 2008 3weeks ago. just recently (within this week) we got this problem that xp_cmdshell returns NULL.

    When we reboot the server, it will work fine. but some time later, not working again.

    how we can track that where is the problem ...

    Thanks,

    Yuyu

    Friday, June 28, 2013 11:47 AM

All replies

  • can you tell us where ur using XP_CMDSHELL,in a proc,in a batch..What account you are using to run it is it a sysadmin or some proxy account?may be more details can help

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Friday, June 28, 2013 12:29 PM
  • Hi,

    we use xp_cmdshell in a sql agent job.

    but now we're testing it manually

    just run exec xp_cmdshell 'dir c:' using a sysadmin account

    most of the time it's not working.

    Monday, July 1, 2013 3:31 AM
  • When  you use this command via SQL Agent it will be running under the account SQL Server Agent runs under... Make sure that it has an appropriate permissions.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Monday, July 1, 2013 5:10 AM
    Answerer
  • just run exec xp_cmdshell 'dir c:' using a sysadmin account

    most of the time it's not working.

    I think as Uri suggested make sure SQL server service account has read,write,modify(appropriate)  permissions on C drive so that when u run this command it can list u all files.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Monday, July 1, 2013 5:42 AM
  • Hi,

    I don't think it is permission problem, as sometimes it works, also tested dir c: using this account.

    thanks

    Yuyu

    Monday, July 1, 2013 5:59 AM
  • Hi,

    Any advise on how to track where is the problem ?

    Thanks,

    Yuyu

    Tuesday, July 2, 2013 8:59 AM
  • Try setting off UAC on Windows server for the account you run xp_cmdshell.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, July 2, 2013 9:12 AM
    Answerer
  • Our SQL Server and SQL Server Agent running using Local System.

    SSIS running under Nerwork Service.

    how to set UAC off for those accounts?

    and I have googled to turn off UAC, seems that is for the server, not for a particular account..

    and does this user account control will affect the xp_cmdshell sometimes working but sometimes not working?


    Wednesday, July 3, 2013 2:01 PM