Post reply

xp_cmdshell permission in SQL Server Authentication mode

  • March 8, 2010 at 1:25 am

    #141873

    Hallo all together:

    Here is what I tried to do about xp_cmdshell permission

    Environment:

    - MSSQL Server 2005 Express

    - SQL Server Authentication Mode

    - I have created a Login, a User for the Login and a DB-Role

    - I have assigned permissions to the DB-Role and mapped the USER to this DB-Role.

    (all described explicit in topic here)

    Here is the scenario where I have problems with the xp_cmshell permission

    The execution chain:

    1. User INSERTs something in the table

    2. table calls a INSERT-Trigger

    3. INSERT-Trigger calls a Procedure

    4. Procedure executes a xp_cmdshell command

    For the DB-Role (and so the USER) I have assigned:

    -> INSERT permission on the Table

    -> Execution permission on the Procedure

    This was not enough because I got the error:

    "The EXECUTE permission was denied on the object 'xp_cmdshell', database 'my_database, schema 'sys'."

    I found in the web, that I have to do the following, to allow the USER (indirectly) the execution of xp_cmshell commands.

    USE [master]

    GO

    -- Grant database access to the SQL Server login account that you want to provide access.

    EXEC sp_grantdbaccess 'MY_LOGIN'

    GO

    -- Grant execute permission on xp_cmdshell to the SQL Server login account.

    GRANT EXEC ON xp_cmdshell TO MY_LOGIN

    But that was not enough,too, because now I got the error:

    "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. The statement has been terminated."

    For this error i found in the web the following:

    ..."If the user is not in the sysadmin role SQL Server will need to you need use a proxy account.

    You can set this up using sp_xp_cmdshell_proxy_account"…

    ...EXEC sp_xp_cmdshell_proxy_account 'MyDomain\MyUserName', 'myDomainPassword'….

    I think this will work for Windows Authentication, but I don't have (and I will not have) Windows Authentication.

    I have SQL Server Authentication.

    So how can I solve the problem for SQL Server Authentication ?????

    I hope that someone can help me here.

    Thanks and Best Regards

    Marc

  • March 8, 2010 at 5:49 am

    #1129941

    Marc,

    this has nothing to do with Windows or Mixed authentication.

    It is about a login that is not member of the sysadmin role.

    So, you have to create a windows user and execute the SP sp_xp_cmdshell_proxy_account with it. Then if a user executes xp_cmdshell and is not member of the sysadmin role SQL Server will use this proxy account instead.

    HTH,

    Robbert

  • March 9, 2010 at 6:25 am

    #1130593

    To add to Robert's reply. I usually have a windows account set up for my servers and use this user account as the owner of the sql and sql agent services. This system account has minimum read and write privileges on most of the servers that I need to get to. After turning on the xp_cmdshell access in the databases, my jobs automatically have access to this users privileges when sa owns the job that needs to have these rights.

    John.

  • March 9, 2010 at 6:41 am

    #1130604

    Do you mean to say, We need to create the proxy account for each and every user who need to run the xp_cmdshell

  • March 9, 2010 at 8:19 am

    #1130695

    Hello Robbert, hello John,

    thanks for these informations. I think now I get it.

    Together with my SystemAdmin I have solved the

    sp_xp_cmdshell_proxy_account issue, and now

    I can give the users xp_cmdshell execution rights

    successful.

    Thanks and best Regards.

    Marc

Viewing 5 posts - 1 through 4 (of 4 total)

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