locked
xp_cmdshell not able to access network path but same path is accessable from cmd prompt on database server RRS feed

  • Question

  • Hi

    we have shared path to place the backups of all servers. recently we changed security setting by removing "Everyone". SQL_svc_accts group has permissions on that share. we added service account of servers to this group so that we can take backups on that share.

    the shared path is not accessible from xp_cmdshell but accessible from cmd on that server itself.

     

     

    Thanks,

     

     


    ------------------------ Brahma http://brahmarao.tech.officelive.com http://nani1211.info/sql
    Thursday, September 29, 2011 6:51 PM

Answers

  • why do we need to resart SQL Server service when we changed secuirty setting on shared path? could you provide me any links (white papers, docs, pdfs...) which explains in-detail. 

     

    From the How Access Tokens Work topic:

    <Excerpt href="http://technet.microsoft.com/en-us/library/cc783557(WS.10).aspx">

    An access token contains a security identifier (SID) for the user, all of the SIDs for the groups to which the user belongs, and the user’s privileges. If you add a user to a group after the user’s access token has been issued, or modify privileges assigned to the user account, the user must log off and then log on again before the access token will be updated.

    </Excerpt>

    So in the case of the SQL Server service account, a service restrart will accomplish this.

     

    I have few questions to ask

    1) uner what account backup will execute

    a) when sysadmin role member executes backup statment on query analyzer?

    b) when sysadmin role member executes backup statment through job?

    c) when regular user with (grant) backup permission member executes backup statment on query analyzer?

     

    2) sql server agent account will b used for backup when backup is scheduled through job?

    3) under what account restore statement executes ? 

     

    Unlike xp_cmdshell, BACKUP always uses the SQL Server service account regardless of the invoking user or how the backup command was issued. The permission to use the backup command are controlled througth the normal SQL Server permissions system.  When invoked by a SQL Server Agent job owned by a sysadmin role member, the job runs as sysadmin and will therefore have backup permissions.  If the job is owned by a non-sysadmin user, that user must be granted BACKUP permissions.  But again, SQL Server uses the service account to access the share.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Uwe RickenMVP Tuesday, October 4, 2011 6:23 AM
    • Marked as answer by Peja Tao Thursday, October 6, 2011 9:30 AM
    Friday, September 30, 2011 12:21 PM

All replies

  • Is your xp_cmdshell call inside a job? If so your SQL Agent account may not have necessary Windows perms, whereas your own Windows cred from a command prompt does.

    Watch Event viewer on the server with the target share when you run your xp_cmdshell call to see if you can identify the account that is being rejected. My money is on the SQL Agent account.

    Best,

    Eric

     

     

    Thursday, September 29, 2011 8:17 PM
  • You mention xp_cmdshell and backups too.  A backup database command runs in the context of the SQL Server service account whereas xp_cmdshell runs in the context of the SQL Server service account if the user is a sysadmin role member or under the xp_cmdshell proxy account for non-sysadmins.  Be aware if the difference if you are using xp_cmdshell for troubleshooting permission problems.

    Try logging in interactively as the SQL Server service account and access the share.  It that succeedes, you might need to restart the SQL Server service so that the group membership change is recognized. 

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Thursday, September 29, 2011 9:57 PM
  • many Thanks for response.

     

    let me explain my scenario....I am sorry if you guys confused...

    we have shared backup path \\server1\sqlbackup$ and security setting for this share is "everyone","sql_admins" group, "sql_svc_accts" group, "windows_admins" group.

    every service account part of "sql_svc_accts" group. but we missed some service account to add this group. next day the backup job failed on server's whos service account not added to "sql_svc_accts" group. windows guys added those missed service accounts to "sql_avc_accts" group. but still the backup job failed on those servers.

    so i tried xp_cmdshell, cmd, interactively using service acount to access the backup shared path from query analyzer. i am no able to access backu share path using xp_cmdshell.

     

    we removed "everyone" and the problem started

     

     

    Eric,

    I ran xp_cmdshell from query analyzer under service account and my NT account (my account has sysadmin role). both ways I got error access denied.

    The strange thing (i am not expert on windows security) is

    when i tried \\server1\sqlbackup$ the result set is "filel not found" message with disk volumn details

    but when tried full backup location of particular sql server(i.e,  \\server1\sqlbackup$\sqlserver1\systembackups ) the result set is access denied.

    I will check event log...

     

    Dan,

    I am able to access UNC share using both my NT account and SQL server account interactively on serve and also I am able to access share from cmd on server using service account

    P:> dir \\server1\sqlbackup$\sqlserver1\systembackups

    why do we need to resart SQL Server service when we changed secuirty setting on shared path? could you provide me any links (white papers, docs, pdfs...) which explains in-detail.

     

    I have few questions to ask

    1) uner what account backup will execute

                             a) when sysadmin role member executes backup statment on query analyzer?

                             b) when sysadmin role member executes backup statment through job?

                            c) when regular user with (grant) backup permission member executes backup statment on query analyzer?

     

    2) sql server agent account will b used for backup when backup is scheduled through job?

    3) under what account restore statement executes ?

     

    I will try this.

     

    Thanks,

     


    ------------------------ Brahma http://brahmarao.tech.officelive.com http://nani1211.info/sql
    Friday, September 30, 2011 4:17 AM
  • why do we need to resart SQL Server service when we changed secuirty setting on shared path? could you provide me any links (white papers, docs, pdfs...) which explains in-detail. 

     

    From the How Access Tokens Work topic:

    <Excerpt href="http://technet.microsoft.com/en-us/library/cc783557(WS.10).aspx">

    An access token contains a security identifier (SID) for the user, all of the SIDs for the groups to which the user belongs, and the user’s privileges. If you add a user to a group after the user’s access token has been issued, or modify privileges assigned to the user account, the user must log off and then log on again before the access token will be updated.

    </Excerpt>

    So in the case of the SQL Server service account, a service restrart will accomplish this.

     

    I have few questions to ask

    1) uner what account backup will execute

    a) when sysadmin role member executes backup statment on query analyzer?

    b) when sysadmin role member executes backup statment through job?

    c) when regular user with (grant) backup permission member executes backup statment on query analyzer?

     

    2) sql server agent account will b used for backup when backup is scheduled through job?

    3) under what account restore statement executes ? 

     

    Unlike xp_cmdshell, BACKUP always uses the SQL Server service account regardless of the invoking user or how the backup command was issued. The permission to use the backup command are controlled througth the normal SQL Server permissions system.  When invoked by a SQL Server Agent job owned by a sysadmin role member, the job runs as sysadmin and will therefore have backup permissions.  If the job is owned by a non-sysadmin user, that user must be granted BACKUP permissions.  But again, SQL Server uses the service account to access the share.

     

     


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    • Proposed as answer by Uwe RickenMVP Tuesday, October 4, 2011 6:23 AM
    • Marked as answer by Peja Tao Thursday, October 6, 2011 9:30 AM
    Friday, September 30, 2011 12:21 PM
  • Hi,

    Is problably that You don't created xp_cmdshe_proxy_coount yet, To execute shells via sql server.

    1. Enable xp_cmdshell, in sql 2005 and sql 2008 this feature is disabled by default

    2. grant access to accounts that require execute xp_cmdshell

    2. create a proxy in sql server agent under Operating System (cmdExec)

    3. add to this proxy, the accounts that you need give this permission.

    4 under security - credentials: Create a new credential  and assign a domain account, (this is important to access network reosurces)

    Plese review this link

    http://almamunbd.wordpress.com/2009/05/28/how-to-create-sql-agent-proxy-accounts-in-sql-20052008-server/

    I hope this help You

     

    Regards,

    Carlos Augusto.


    If the answer was helpful, please mark it as useful. Si la respuesta le ayudó, por favor márquela como útil.
    Saturday, October 1, 2011 2:17 PM