none
sp_xp_cmdshell_proxy_account returns error code 5 RRS feed

  • Question

  • As part of configuring my SQL Server 2005 box to be able to use the xp_cmdshell function, I ran sp_xp_cmdshell_proxy_account to set up the credentials:

    sp_xp_cmdshell_proxy_account 'Domain\DomainUser', 'password'

    Unfortunately, it just returns:

    Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
    An error occurred during the execution of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '5'.

    I am assuming that this is an "Access Denied" error message.  Has anyone got any ideas on why this is happening or what I can do to narrow down what the problem is.  I can't find any logs with further information.

    I have used a similar setup on another SQL Server 2005 box before and have never had a problem running the sp_xp_cmdshell_proxy_account function.  I can't see what I have done differently this time.

    Further information:

    The SQL Server and SQL Server Agent services are running under the Domain\DomainUser account.

    I have given the Domain\DomainUser the following privileges:
    Act as part of the operating system
    Log on as a batch job
    Log on as a service
    Replace a process level token

    Let me know if you need any further information.

    Thanks in advance for any help.

    Ben
    Monday, August 6, 2007 12:50 PM

Answers

  • It's not an answer to the original problem, but I have found a work-around.  The following command will create the credential that sp_xp_cmdshell_proxy_account was trying to create.

    create credential ##xp_cmdshell_proxy_account## with identity = 'Domain\DomainUser', secret = 'password'

    My calls to xp_cmdshell now work from a non-administrator account.

    This makes me wonder what sp_xp_cmdshell_proxy_account was trying to do that caused the error.

    Ben
    Tuesday, August 7, 2007 12:26 PM

All replies

  • Have you checked in Surface Configuration that xp_cmdshell itself is enabled?

     

    /Kenneth

    Monday, August 6, 2007 2:11 PM
  • Yes, the xp_cmdshell option is enabled in the surface configuration.

    As a further check, I went back to another server where I had successfully configured the xp_cmdshell function (including running sp_xp_cmdshell_proxy_account) and tried re-running sp_xp_cmdshell_proxy_account with xp_cmdshell enabled in surface configuration and then disabled.  The result was that it made no difference whether xp_cmdshell was enabled or disabled, I could still successfully run sp_xp_cmdshell_proxy_account to set up the credentials for xp_cmdshell.

    Ben

    Tuesday, August 7, 2007 6:22 AM
  • It's not an answer to the original problem, but I have found a work-around.  The following command will create the credential that sp_xp_cmdshell_proxy_account was trying to create.

    create credential ##xp_cmdshell_proxy_account## with identity = 'Domain\DomainUser', secret = 'password'

    My calls to xp_cmdshell now work from a non-administrator account.

    This makes me wonder what sp_xp_cmdshell_proxy_account was trying to do that caused the error.

    Ben
    Tuesday, August 7, 2007 12:26 PM
  • Hey did you ever get a response to why sp_xp_cmdshell_proxy_account didn't work?
    Monday, January 12, 2009 6:37 PM
  • I tried it using create credential and was able to create a proxy account but the users were not able to use xpcmdshell.
    what could be the problem. do i need to change anything at the system level.
    Wednesday, March 18, 2009 12:58 PM
  • I ran into the same problem on my system and it was related to UAC.   If you launch SQL Enterprise Manager as an Administrator the command runs.  If you don't run as administrator internally the API call to LsaOpenPolicy fails.

    Vista SP1

    Msg 15137

    , Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
    An error occurred during the execution
    of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '5'.

     

    Vista SP2

    Msg 15137, Level 16, State 1, Procedure sp_xp_cmdshell_proxy_account, Line 1
    An error occurred during the execution
    of sp_xp_cmdshell_proxy_account. Possible reasons: the provided account was invalid or the '##xp_cmdshell_proxy_account##' credential could not be created. Error code: '0'.

     

     


    Robert A. Dorr (SQL Server Senior Escalation Engineer)
    Tuesday, April 7, 2009 6:48 PM
  • hi,

    I am also getting the same error.
    did you get any workaround for this issue?

    regards
    Rahul Bobal
    • Proposed as answer by maggoi Wednesday, March 3, 2010 11:15 AM
    • Unproposed as answer by Naomi NModerator Friday, December 2, 2011 6:05 PM
    Monday, November 2, 2009 5:37 PM
  • Use Windows Authentication to issue the command!!
    Wednesday, March 3, 2010 11:16 AM
  • This workaround from Ben12 worked for me on a SQL2008R2 new install with the same symptoms as the original problem.


    Brian Searle
    Friday, December 2, 2011 12:30 PM
  • I was experiencing the same problem.  I executed the commands using a windows authentication user instead of an SQL user.  It worked fine under the windows user.
    Friday, January 20, 2012 4:13 AM
  • Hi,

    Launch SSMS "run as administrator" and then the Command(s) completed successfully.

    Monday, May 7, 2012 1:49 PM
  • Thanks! That was the problem for me.

    UAC - a common cause for errors. Should've disabled it from the start.

    Friday, September 14, 2012 4:09 PM
  • To clarify, you need to put two of these suggestions together:

    1. Use Windows Authentication to issue the command

    2. Open SQL Server Management Studio "as Admin"

    Thursday, November 6, 2014 7:26 PM
  • Thanks a million Ben12!  This one works for both SQL Server 2012 and SQL Server 2014.
    Tuesday, May 17, 2016 6:09 PM
  • Excelente work for me , its very important run sql server mangement studio as admin.
    Tuesday, February 28, 2017 1:44 PM