Post reply

xp_cmdshell failed to execute

  • December 14, 2006 at 10:55 am

    #62688

    OK so I have a situation where I am working with a developer who is kicking off a stored procedure which uses xp_commandshell on one server from a secondary server via an ODBC connection to the database and come across a problem running the xp_commandshell stored procedure.  I recieve the error...

    xp_cmdshell failed to execute because current security context is not sysadmin and proxy acount is not setup correctly. For more information, refer to Book Online, search for topic related to xp_sqlagent_proxy_account.

     

    After doing some research I have come up with the following:  I need to give the non-sysadmin account access to the master database and the extended stored procedure xp_commandshell.  I did that but still it did not work...as a temporary work around I made the sql login account a sysadmin and the process worked...but that is not how I would like to have it set up...

    So after more research I came across the xp_sqlagent_proxy_account setting and have a couple of questions. 

    1.  In order for xp_sqlagent_proxy_account process to work you need to have a windows DOMAIN account which is not a member of the sys-admins group correct??? 

    So Once you have the account is it correct that all you need to do is go into sql agent properties and enter the account name, password and domain & then give that account rights to the master database and xp_commandshell?

    2. What are the security risks involved with setting up a process in this way...does this not allow all accounts who are non-sysadmins the ability to run the xp_commandshell then?  Or is that fixed by the fact that they explicitly need to be given rights to execute the xp_commandshell stored procedure?

    3.  Is this a preferred way to set up a process vs. just creating a local sql account that is a sysadmin?  ( I already know that this is bad but would appreciate some comments  or beliefs as to your feelings on the subject).

     

    Thanks to all who take the time to read and/or reply.

     

    Leeland

  • December 14, 2006 at 12:29 pm

    #678178

    It is xp_cmdshell not xp_commandshell...

    From BOL:

    xp_cmdshell operates synchronously. Control is not returned until the command shell command completes.

    When you grant execute permissions to users, the users can execute any operating-system command at the Microsoft Windows NT® command shell that the account running Microsoft SQL Server™ has the needed privileges to execute.

    By default, only members of the sysadmin fixed server role can execute this extended stored procedure. You may, however, grant other users permission to execute this stored procedure.

    When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed server role, xp_cmdshell will be executed under the security context in which the SQL Server service is running. When the user is not a member of the sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy account, which is specified using xp_sqlagent_proxy_account. If the proxy account is not available, xp_cmdshell will fail. This is true only for Microsoft® Windows NT® 4.0 and Windows 2000. On Windows 9.x, there is no impersonation and xp_cmdshell is always executed under the security context of the Windows 9.x user who started SQL Server.

    Note  In earlier versions, a user who was granted execute permissions for xp_cmdshell ran the command in the context of the MSSQLServer service's user account. SQL Server could be configured (through a configuration option) so that users who did not have sa access to SQL Server could run xp_cmdshell in the context of the SQLExecutiveCmdExec Windows NT account. In SQL Server 7.0, the account is called SQLAgentCmdExec. Users who are not members of the sysadmin fixed server role now run commands in the context of this account without specifying a configuration change.

     

    I don't think you need to have domain account..local nt account also works...

    xp_cmdshell is the command you can do anything with it...so becarefull...

    Read the following articles...

    http://www.novicksoftware.com/Articles/SQL-Server-2000-SP3-and-xp_cmdshell-Woes.htm

    http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops3.mspx

     

    MohammedU
    Microsoft SQL Server MVP

  • December 14, 2006 at 2:32 pm

    #678216

    Thanks for the information it helped a lot...So with a little free time I decided to run a few tests…

    I got it to work by testing it. Here are the steps I performed.

    1. Created a local SQL login account 01Lee with rights to the Master database and granted EXEC rights to the xp_cmdshell stored procedure.

    2. Created a Local system account on Windows Server sql_proxy, and put it in the admins group.

    3. Went into SQL Server Agent and under Job system and set the proxy account to my new local account sql_proxy I created.

    I ran the following command

    exec master..xp_cmdshell 'echo abcd'

    go

    OUTPUT

    ------------

    abcd

    NULL

    So the changes appear to work just fine, I verified that by removing the sql_proxy stuff and re-running it…which it failed with the following

    Msg 50001, Level 1, State 50001

    xp_cmdshell failed to execute because current security context is not sysadmin and proxy account is not setup correctly. For more information, refer to Book Online, search for topic related to xp_sqlagent_proxy_account.

    After I enabled the Non-SysAdmin job step proxy account under properties for SQL agent, with the sql_proxy account I created I was able to run the exec statement with the same results.

    So it looks like it works.

     

    Thanks,

     

    Leeland

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply