locked
EXECUTE xp_cmdshell AS context of a sysadmin requires ##xp_cmdshell_proxy_account## credential to exist? RRS feed

  • Question

  • Hi,

    I am unable to use a stored procedure to allow a non-sysadmin to execute in the context of a sysadmin and call xp_cmdshell unless the ##xp_cmdshell_proxy_account## actually exists.

    My understanding is that a sysadmin does not require the use of a proxy in order to execute xp_cmdshell.  However without one set up I receive the following error:

    Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1

    The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

    A little confused as to why the ##xp_cmdshell_proxy_account## needs to be set up and if/how/when the credential identity's details are actually used?

    Regards
    Dan

    Wednesday, November 12, 2014 9:29 AM

Answers

  • I am unable to use a stored procedure to allow a non-sysadmin to execute in the context of a sysadmin and call xp_cmdshell unless the ##xp_cmdshell_proxy_account## actually exists.

    When you use EXECUTE AS in the procedure header, you impersonate a database user, but you don't get any permissions on server level, unless the database is marked as trustworthy. But don't make the database trustworthy, as it can can open a security hole.

    Instead a better solution is to create a certificate in the master database, and create a login from that certificate. That is not a login that can acutlly login, but only serves as a placeholder for permissions. In this case, you add the login to the sysadmin role. Then you export the certificate to the user database and you sign the procedure with the certificate. Below are the steps to take as a script. For more details on the technique, see this article on my web site:
    http://www.sommarskog.se/grantperm.html
    I also discuss the dangers with trustworthy in more detail in this article.

    USE master
    go
    -- Create certificate in master.
    CREATE CERTIFICATE xp_cmdshell_cert
       ENCRYPTION BY PASSWORD = 'All you need is love'
       WITH SUBJECT = 'For xp_cmdshell privileges',
       START_DATE = '20020101', EXPIRY_DATE = '20200101'
    go
    -- Create a login for the certificate.
    CREATE LOGIN xp_cmdshell_cert_login FROM CERTIFICATE xp_cmdshell_cert
    go
    -- Grant rights for the certificate login.
    EXEC sp_addsrvrolemember xp_cmdshell_cert_login, sysadmin
    go
    -- Save the certificate to disk.
    BACKUP CERTIFICATE xp_cmdshell_cert TO FILE = 'C:\temp\cert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\cert.pvk' ,
                      ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
                      DECRYPTION BY PASSWORD = 'All you need is love')
    go
    -- Move to test database.
    USE somedatabase
    go
    -- You procedure here.
    CREATE PROCEDURE run_xp_cmdshell AS
    EXEC xp_cmdshell 'DIR'
    go
    -- Give test user right to execute the procedure.
    GRANT EXECUTE ON run_xp_cmdshell TO someuser
    go
    -- Import the certificate we created in master into the test database.
    CREATE CERTIFICATE xp_cmdshell_cert FROM FILE = 'C:\temp\cert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\cert.pvk',
                      DECRYPTION BY PASSWORD = 'Tomorrow never knows',
                      ENCRYPTION BY PASSWORD = 'A day in life')
    go
    -- Delete the files.
    EXEC master..xp_cmdshell 'DEL C:\temp\cert.*', 'no_output'
    go
    -- Sign the test procedures.
    ADD SIGNATURE TO run_xp_cmdshell BY CERTIFICATE xp_cmdshell_cert
        WITH PASSWORD = 'A day in life'
    go


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Dan Harling Monday, November 17, 2014 9:00 AM
    Saturday, November 15, 2014 6:39 PM

All replies

  • Yes if its a non sysadmin login you need a proxy account to be setup

    See this part

    xp_cmdshell Proxy Account

    When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential named ##xp_cmdshell_proxy_account##. If this proxy credential does not exist, xp_cmdshell will fail.

    The proxy account credential can be created by executing sp_xp_cmdshell_proxy_account. As arguments, this stored procedure takes a Windows user name and password.

    from here

    http://technet.microsoft.com/en-us/library/ms175046(v=sql.110).aspx

    Also see

    http://technet.microsoft.com/en-us/library/ms190359(v=sql.110).aspx


    Please Mark This As Answer if it solved your issue
    Please Mark This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My MSDN Page
    My Personal Blog
    My Facebook Page

    • Proposed as answer by Uri DimantMVP Wednesday, November 12, 2014 11:35 AM
    Wednesday, November 12, 2014 11:17 AM
  • Hi Visakh,

    Thanks for the response but I am still confused.

    The procedure being called by the non-sysadmin first changes context (using EXECUTE AS) to a sysadmin.  I am checking this is the case using SUSER_NAME().  DML operations are successfully executed that the non-sysadmin has no direct rights to perform but the call to xp_cmdshell does not.

    Regards

    Dan


    • Edited by Dan Harling Wednesday, November 12, 2014 12:12 PM
    Wednesday, November 12, 2014 11:48 AM
  • Hi Dan,

    Follow the steps below, which will allow non-sysadmin user to run xp_cmdshell without sysadmin access:

    --If not already, create login that you would like to use as xp_cmdshell proxy user
    CREATE LOGIN [YourDomain\XPCMDSHELL_PROXY_USER_NAME] FROM WINDOWS;
    
    --Grant this user sysadmin access on the box
    ALTER SERVER ROLE [sysadmin] ADD MEMBER [YourDomain\XPCMDSHELL_PROXY_USER_NAME];
    
    --Execute the below T-SQL stored procedure to create proxy for the xp_cmdshell using the user you create in previous step. 
    EXEC [sp_xp_cmdshell_proxy_account] 'YourDomain\XPCMDSHELL_PROXY_USER_NAME','PasswordForThisAccount';
    
    --Create the database role and assign rights to the role to execute xp_cmdshell user
    CREATE ROLE [CmdShell_Executor] AUTHORIZATION [dbo]
    GRANT EXEC ON xp_cmdshell TO [CmdShell_Executor]
    
    --All done. Now you can assign any non-sysadmin login to CmdShell_Executor database role, which will give user the ability to run xp_cmdshell without being the member of sysadmin group. 
    CREATE USER [YourDomain\Non-Sysadmin-User] FROM LOGIN [YourDomain\Non-Sysadmin-User];
    ALTER ROLE [CmdShell_Executor] ADD MEMBER [YourDomain\Non-Sysadmin-User]
    GO

    I hope this information helps.


    Regards,

    Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

    http://basitaalishan.com

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    • Proposed as answer by Basit Farooq Thursday, November 13, 2014 11:35 AM
    Thursday, November 13, 2014 11:35 AM
  • I am unable to use a stored procedure to allow a non-sysadmin to execute in the context of a sysadmin and call xp_cmdshell unless the ##xp_cmdshell_proxy_account## actually exists.

    When you use EXECUTE AS in the procedure header, you impersonate a database user, but you don't get any permissions on server level, unless the database is marked as trustworthy. But don't make the database trustworthy, as it can can open a security hole.

    Instead a better solution is to create a certificate in the master database, and create a login from that certificate. That is not a login that can acutlly login, but only serves as a placeholder for permissions. In this case, you add the login to the sysadmin role. Then you export the certificate to the user database and you sign the procedure with the certificate. Below are the steps to take as a script. For more details on the technique, see this article on my web site:
    http://www.sommarskog.se/grantperm.html
    I also discuss the dangers with trustworthy in more detail in this article.

    USE master
    go
    -- Create certificate in master.
    CREATE CERTIFICATE xp_cmdshell_cert
       ENCRYPTION BY PASSWORD = 'All you need is love'
       WITH SUBJECT = 'For xp_cmdshell privileges',
       START_DATE = '20020101', EXPIRY_DATE = '20200101'
    go
    -- Create a login for the certificate.
    CREATE LOGIN xp_cmdshell_cert_login FROM CERTIFICATE xp_cmdshell_cert
    go
    -- Grant rights for the certificate login.
    EXEC sp_addsrvrolemember xp_cmdshell_cert_login, sysadmin
    go
    -- Save the certificate to disk.
    BACKUP CERTIFICATE xp_cmdshell_cert TO FILE = 'C:\temp\cert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\cert.pvk' ,
                      ENCRYPTION BY PASSWORD = 'Tomorrow never knows',
                      DECRYPTION BY PASSWORD = 'All you need is love')
    go
    -- Move to test database.
    USE somedatabase
    go
    -- You procedure here.
    CREATE PROCEDURE run_xp_cmdshell AS
    EXEC xp_cmdshell 'DIR'
    go
    -- Give test user right to execute the procedure.
    GRANT EXECUTE ON run_xp_cmdshell TO someuser
    go
    -- Import the certificate we created in master into the test database.
    CREATE CERTIFICATE xp_cmdshell_cert FROM FILE = 'C:\temp\cert.cer'
    WITH PRIVATE KEY (FILE = 'C:\temp\cert.pvk',
                      DECRYPTION BY PASSWORD = 'Tomorrow never knows',
                      ENCRYPTION BY PASSWORD = 'A day in life')
    go
    -- Delete the files.
    EXEC master..xp_cmdshell 'DEL C:\temp\cert.*', 'no_output'
    go
    -- Sign the test procedures.
    ADD SIGNATURE TO run_xp_cmdshell BY CERTIFICATE xp_cmdshell_cert
        WITH PASSWORD = 'A day in life'
    go


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Dan Harling Monday, November 17, 2014 9:00 AM
    Saturday, November 15, 2014 6:39 PM
  • I was having same error message 15153 and was fixed by executing the following command

    Exec sp_XP_cmdshell_proxy_account 'domainname\username','password'

    go

    you will see ## XP_cmdshell_proxy_account## under credential

    Tuesday, April 2, 2019 8:54 PM