XP_Cmdshell 'Access is denied'

  • Hi All,

    xp_cmdshell 'dir \\Server001\e$' - running in Server2

    xp_cmdshell 'dir \\Server002\e$' - running in server1

    OUTPUT : 'Access is denied.'

    But I'm able to access through RUN(WIN+R) command from server1 to server2 and vice versa.

    NOTE: I've given 'full control' permission on both folders in E\ drive in both servers.

    Please advice.

    Thanks and Regards,

    Ravi

  • ravisamigo (5/8/2012)


    Hi All,

    xp_cmdshell 'dir \\Server001\e$' - running in Server2

    xp_cmdshell 'dir \\Server002\e$' - running in server1

    OUTPUT : 'Access is denied.'

    But I'm able to access through RUN(WIN+R) command from server1 to server2 and vice versa.

    NOTE: I've given 'full control' permission on both folders in E\ drive in both servers.

    Please advice.

    Thanks and Regards,

    Ravi

    Please post the result of the foloowing query when executed against the sql server instance

    select value_in_use from sys.configurations

    where name = 'xp_cmdshell'

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • ravisamigo (5/8/2012)


    Hi All,

    xp_cmdshell 'dir \\Server001\e$' - running in Server2

    xp_cmdshell 'dir \\Server002\e$' - running in server1

    OUTPUT : 'Access is denied.'

    But I'm able to access through RUN(WIN+R) command from server1 to server2 and vice versa.

    NOTE: I've given 'full control' permission on both folders in E\ drive in both servers.

    Please advice.

    Thanks and Regards,

    Ravi

    if you touch anything outside of SQL server (local directories, UNC shares, mapped drives, your own credentials don't count and are not used.

    SQL WILL pass your credentials to a linked server, but anything else is using an account you did not intuitively expect it to use.

    SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:

    or if the above was blank, the account in services:

    That account is often an account which has never logged into the domain, and was never assigned permissions to get to the local disk or network share.

    As a result, you usually need to create a domain account in Active Directory, specifically grant it share access if it doesn't inherit it from Domain\Users or Domain\AuthenticatedUsers and change the account SQL Server starts with to that account.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • I know this is an old post, my apologies. I had this same issue... so I did the above suggestion and set the Logon account for the SQL Server Service in my config manager to use a domain account instead, so my sysadmin users on the sql server could use xp_cmdshell. This worked and fixed the problem, HOWEVER, in doing so it made the SQL Server instance only accessible to the local box. When trying to access through SSMS from a different box I would get a "Cant generate SSPI Context error".

    Any Suggestions?

    Thank you in advance

  • slash.young (5/8/2014)


    I know this is an old post, my apologies. I had this same issue... so I did the above suggestion and set the Logon account for the SQL Server Service in my config manager to use a domain account instead, so my sysadmin users on the sql server could use xp_cmdshell. This worked and fixed the problem, HOWEVER, in doing so it made the SQL Server instance only accessible to the local box. When trying to access through SSMS from a different box I would get a "Cant generate SSPI Context error".

    Any Suggestions?

    Thank you in advance

    It's an old topic again, but can be useful for somebody in the future.

    Sometimes when you change the service account (the one who runs the service through SQL Server Configuration Manager), the new account is not able to create the SPN's for the combination Service-Service Account, so Kerberos authentication (Windows) fails

    So, you can fix this by removing the old SPN's and creating new ones.

    To check that's the problem:

    setspn -l OldServiceAccount

    setspn -l NewServiceAccount

    You should see something like

    MSSQLSvc/<FullQualifiedName> or MSSQLSvc/<FullQualifiedName>:InstanceName

    MSSQLSvc/<FullQualifiedName>:ListeningPort

    if you see then only for the old one, remove them and create new

    To remove:

    setspn -d MSSQLSvc/<FullQualifiedName> OldServiceAccount

    setspn -d MSSQLSvc/<FullQualifiedName>:ListeningPort OldServiceAccount

    To add the new one

    setspn -s MSSQLSvc/<FullQualifiedName> NewServiceAccount

    setspn -s MSSQLSvc/<FullQualifiedName>:ListeningPort NewServiceAccount

    It's important to use -s to check for duplicates before adding new SPN's as that can make a bit of a mess.

    Regards

  • SQL Server uses either the account set up as the proxy account, or if that is left blank(the default) it uses account it starts with to try and access the resource:

    or if the above was blank, the account in services:

    Thnx for sharing this!!

    I just had this issue and I had forgot the solution.

    regards

    Michael

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

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