Post reply

xp_cmdshell --> "Access is denied."

  • June 1, 2015 at 3:50 pm

    #317884

    Hi all,

    I am trying to copy a backup file from sql server 2008R2 to sql server 2012 running:

    EXEC xp_cmdshell 'COPY \\Analytics2008\f$\Backup\xpm.20150531.BAK \\Analytics2012\e$\bk'

    I am receiving "Access is denied.". But is I run following code using cmd.exe, it works just fine.

    COPY \\Analytics2008\f$\Backup\xpm.20150531.BAK \\Analytics2012\e$\bk

    What can be wrong?

    Thanks,

  • June 1, 2015 at 7:49 pm

    #1802199

    The login for SQL server doesn't have the necessary privs on either the source, the target, or both. You do.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 2, 2015 at 12:05 pm

    #1802390

    Thanks Jeff,

    2008 server (A) runs under "local system" account

    2012 server (B) runs under "qa" account

    Do I need to add "local system" to the "administrator" group on server B?

    Thanks,

  • June 2, 2015 at 12:27 pm

    #1802402

    The command is being execute as the service account on the SQL Server that's running it. To have permission to both servers involved, the same account has to be able to read from the source server and write to the destination server file system.

    With a domain account, you just adjust the domain user permissions on the other machine. I don't know that it'll work if you're running under a local account.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • June 15, 2015 at 1:12 pm

    #1805557

    I came across this article saying xp_cmdshell isn't enabled by default on SQL Server ; Link[/url]

    Try running this before running your query and see if that fixes it:

    EXEC sp_configure 'xp_cmdshell', 1

    GO

  • June 15, 2015 at 1:14 pm

    #1805559

    Thanks, but it was/is enabled

  • June 15, 2015 at 1:26 pm

    #1805562

    Do you have access to GRANT access to a user in SQL?

    Try this:

    grant execute on xp_cmdshell to user

    You mentioned you login with different logins for each server; See if you can run this by logging in with the same windows credential on both. Ed Wagner's post explained that pretty well.

  • June 15, 2015 at 4:06 pm

    #1805603

    rightontarget (6/2/2015)

    Thanks Jeff,

    2008 server (A) runs under "local system" account

    2012 server (B) runs under "qa" account

    Do I need to add "local system" to the "administrator" group on server B?

    Thanks,

    Most of this isn't going to work on a server that uses a "local system" account. It really needs to be a proper Active Directory account. Trying to do any of this with a "local system" account is likely to open some security holes that you could drive a truck with twin side plows through.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 16, 2015 at 1:13 am

    #1805645

    rightontarget (6/2/2015)

    Thanks Jeff,

    2008 server (A) runs under "local system" account

    2012 server (B) runs under "qa" account

    Do I need to add "local system" to the "administrator" group on server B?

    Thanks,

    Hi there,

    I want to make sure I understand your scenario correctly.

    On Analytics2008 (which I think you're referring to as Server A, above), you're running the SQL Server service under the Local System account. On this server, you have enabled xp_cmdshell, have not changed any of the default xp_cmdshell configurations, and are attempting to run this command using TSQL: EXEC xp_cmdshell 'COPY \\Analytics2008\f$\Backup\xpm.20150531.BAK \\Analytics2012\e$\bk'.

    When you attempt to run this using xp_cmdshell on Analytics2008, you get an "Access denied" message. But when you run it in a Command Prompt with your own account (which I'll assume is a member of the local Administrators group, and the SQL server sysadmins role on both Analytics2008 (the source server) and Analytics2012 (the target server), it works. Is this correct?

    Jeff is right, this suggests an issue with permissions. The easy way to resolve it would be to add DOMAIN\Analytics2008$ to the local Administrators group on Analytics2012 , buuuuuuttttt... please don't. It gives Analytics2008 much more access to Analytics2012 than it truly needs, and is total overkill for resolving a permissions problem.

    So let's talk about why I think it's not working.

    1. xp_cmdshell has been left at its default configuration, which means that command execution happens in one of two ways (see here):

    a. If called by someone who is a member of the SQL sysadmins role (I'm guessing you're a member), it will run under the context of the SQL Server service account. Right now, that's Analytics2008\LOCAL SYSTEM (aka NT AUTHORITY\SYSTEM). The LOCAL SYSTEM account on Analytics2008 pretty much has the keys to the city when it comes to Analytics2008, but has absolutely zero permissions on any other machine.

    b. If called by someone who is not a member of the SQL sysadmins role (which I hope will be the case when your application is in production), it 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.

    2. You're copying files to the e$ share on Analytics2012. This is an administrative share, which by default is only accessible by accounts that are members of the local Administrators group on Analytics2012.

    Under scenario 1a above, this will fail because the SQL Server service account on Analytics2008 does not have rights to access Analytics2012.

    Under scenario 1b above, this will fail because you have not configured xp_cmdshell's proxy account.

    In other words, right now, what you've configured just won't work.

    Now, we *could* solve 1a by adding DOMAIN\Analytics2008$ (the LOCAL SYSTEM account) to the local Administrators group on Analytics2012, and by running your xp_cmdshell using a SQL login that's a member of the sysadmins server role. But there are some really good reasons not to do that. And they all boil down to security and vulnerability. We want to give xp_cmdshell the permissions to do what it needs to do, but nothing more. This is the concept of least privilege. Additionally, doing that does not address scenario 1b. We don't want your users or application accounts to be members of the SQL sysadmins role (or the windows Administrators group) in order for your applications to work!

    So how should we resolve this? Here's my suggestions:

    1. Create a new AD account that is a member of the Domain Users group, and nothing more. Give it an appropriately complex password. For the sake of this example, let's say the account is called DOMAIN\SVC-SQLAN2008.

    2. On Analytics2008, reconfigure the SQL Server service account to use DOMAIN\SVC-SQLAN2008. To change the SQL Server service account, use the SQL Server Configuration Manager tool (do not use the Windows Services management console). You'll need to restart the SQL Server service, so schedule an outage window to do this. See this article for information on how to use SQL Server Configuration Manager to change the account.

    Steps 1 and 2 are so that your SQL server's service account configuration is in line with Microsoft's published guidelines on service account selection (see here).

    3. Create a new AD account that is a member of the Domain Users group, and nothing more. Give it an appropriately complex password. For the sake of this example, let's say the account is called DOMAIN\SVC-SQLPROXY.

    4. On Analytics2008, modify the NTFS permissions on F:\BACKUP so that DOMAIN\SVC-SQLPROXY has Read permissions to the folder.

    5. On Analytics2012, share the E:\BK folder.

    6. On Analytics2012, change the Share permissions on this new share so that DOMAIN\SVC-SQLPROXY has Change permissions.

    7. On Analytics2012, change the NTFS permissions on E:\BK so that DOMAIN\SVC-SQLPROXY has Write permissions on the folder.

    8. On Analytics2008, configure xp_cmdshell to use a proxy account using TSQL like the following:

    use master

    go

    EXEC sp_xp_cmdshell_proxy_account 'DOMAIN\SVC-SQLPROXY','password'

    This probably seems a little more complicated than it needs to be, but it's done for good reasons:

    Steps 1 and 2 get your system set up according to best practices (and you'll see how it comes in handy shortly).

    Steps 3-8 create an unprivileged account (a proxy account) that xp_cmdshell uses to run the commands, and grants that account just enough permissions to do what it needs to do.

    One thing you'll note is that it will address scenario 1b, which is xp_cmdshell being run by someone who is not a member of the SQL sysadmins server role. But if you try to run xp_cmdshell as someone who IS a member of the sysadmins role (scenario 1a), it will run under the context of the SQL Server service account (which we set up as DOMAIN\SVC-SQLAN2008), and therefore fail because DOMAIN\SVC-SQLAN2008 does not have rights over the source or target folders. To fix that:

    9. On Analytics2008, modify the NTFS permissions on F:\BACKUP so that DOMAIN\SVC-SQLAN2008 has Read permissions to the folder.

    10. On Analytics2012, change the Share permissions on this new share so that DOMAIN\SVC-SQLAN2008 has Change permissions.

    11. On Analytics2012, change the NTFS permissions on E:\BK so that DOMAIN\SVC-SQLAN2008 has Write permissions on the folder.

    [Edit: Jeff made a very good point about giving users permission to run xp_cmdshell directly. I've edited my post to remove that particular instruction.]

    So why bother with 3-11? We could just make the SQL Server service account on Analytics2008 a member of the local Administrators group on Analytics2012, and that would fix your immediate problem. That's true. But it could create a big security problem for you. Rather than expound on it here, have a read of these blog posts I wrote a while back:

    1. xp_cmdshell: Baby did a bad, bad thing[/url] (in which a poorly-configured xp_cmdshell scenario results in data loss)

    2. xp_cmdshell: Some more evil[/url] (in which a poorly-configured xp_cmdshell scenario results in an unprivileged user granting themselves membership to the SQL server sysadmins role)

    3. xp_cmdshell + elevated accounts = a new kind of evil[/url] (the unholy union of an elevated SQL Server service account and a poorly-configured xp_cmdshell which uses a Domain Administrator account, resulting in deletion of objects in Active Directory)

    As you read through those blog posts, you might find the scenarios a bit far-fetched, but I have seen actual production systems set up in this way. The scope for damage of a badly-configured xp_cmdshell can be very large. So take the time to properly configure it, to prevent security headaches in the future.

    I will emphasise that changing your SQL Server service account might have unexpected results, especially if other code or applications in your SQL server inadvertently lean on the service account having elevated privileges. Please test these changes in a test/dev environment before putting them into production, and, as always, ensure you have backups of your systems before making any changes.

    I hope this helps!

    Max

  • June 16, 2015 at 7:44 am

    #1805738

    Maxxxie74 (6/16/2015)

    12. Finally, don't forget to grant non-sysadmins the right to run xp_cmdshell:

    grant execute on xp_cmdshell to [DOMAIN\username]

    Good post but you don't ever want to grant users to execute xp_CmdShell directly. Only the proxy login itself should be granted to execute xp_CmdShell and then when a user not having sysadmin privs wants to do something through xp_CmdShell, it should be through a stored procedure that uses "WITH EXECUTE AS OWNER" and the owner of the database should be "SA" even if it's disabled. That forces non-SA users to do everything using prescribed stored procedures only.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 16, 2015 at 3:58 pm

    #1805917

    Jeff, you make an excellent point. Thank you. I'll amend my post accordingly.

    Max

Viewing 11 posts - 1 through 10 (of 10 total)

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