none
xp_CmdShell getting access denied error from DBServer to AppServer

    Question

  • Server A: Database Server
    Server B: App Server

    Server A:
    1.Installed SQL Server 2017 RTM on Windows 2016 Server
    2.SQL Server service running with default account "NT Service\MSSQLSERVER"
    3.xp_cmdshell enabled 
    4.Trying to rename the file(which is on Server B another applicaiton server) using below command(with SSMS in ServerA)  and getting error "ACCESS IS DENIED"
    command:   exec xp_cmdshell 'RENAME \\ServerB\Test\file1.txt File2.txt'
       Note: Executing this command with SYSAdmin privileges on SQL Server.

    5.Firewalls opened and not an issue. 
       If we run from Server A using command line(DOS) prompt, its working and not working with SSMS.
        (DOS: RENAME \\ServerB\Test\file1.txt File2.txt ) 

    Any suggestion and advise on this "ACCESS IS DENIED" issue?
    [I know if we create with separate service account for SQL Server service then it will work after granting the permissions on share, but client do not want separate id, want to go with default "NT Service\MSSQLSERVER" service account]


    thanks





    • Edited by SQLDBA321 Tuesday, April 23, 2019 2:48 PM modified
    Tuesday, April 23, 2019 1:50 PM

Answers

  • Your permissions inside SQL Server does of course not carry any weight whatsoever outside SQL Server.

    One way out is to grant the machine account, that is, DOMAIN\MACHINE$ (note the trailing dollar sign) permission on the share.

    Another way out is think of SQL Server as something that manages data in tables and not a general file-operation environment...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, April 23, 2019 9:29 PM
  • Hi Oleg thanks for reply. I just saw your reply and we resolved with same resolution as you mentioned above. 

    Solutions:
    1.Initially granted "everyone" on folder level share on APP server then it worked, but this is not best 
    practice to do. 
    2.Then searched on online and fond below link, with Virtual Account topic saying "domain_name\computer_name$" which is solved our issue. 

    After adding the "<domain_name>\<computer_name>$"(here sql server host name) on appserver folder then it resolved the issue. 

    Virtual Account: "If the default value is used for the service accounts during SQL Server setup, a virtual account using the instance name as the service name is used, in the format NT SERVICE\<SERVICENAME>. Services that run as virtual accounts access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$ "


    • Marked as answer by SQLDBA321 Monday, April 29, 2019 2:09 PM
    • Edited by SQLDBA321 Monday, April 29, 2019 2:10 PM modified
    Monday, April 29, 2019 2:09 PM

All replies

  • Your permissions inside SQL Server does of course not carry any weight whatsoever outside SQL Server.

    One way out is to grant the machine account, that is, DOMAIN\MACHINE$ (note the trailing dollar sign) permission on the share.

    Another way out is think of SQL Server as something that manages data in tables and not a general file-operation environment...


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Tuesday, April 23, 2019 9:29 PM
  • Please not that it is not recommended to use xp_cmdshell with a sysadmin account.

    You can create a proxy which is based on a low privileged Windows User with just the needed permissions on windows and grant access to the proxy to a chosen non-sysadmin account.

    This is described here:

    xp_cmdshell

    sp_xp_cmdshell_proxy_account


    Andreas Wolter (Blog | Twitter)
    Senior Program Manager SQL Server & Azure Security

    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012

    Wednesday, April 24, 2019 7:47 AM
  • Hi Oleg thanks for reply. I just saw your reply and we resolved with same resolution as you mentioned above. 

    Solutions:
    1.Initially granted "everyone" on folder level share on APP server then it worked, but this is not best 
    practice to do. 
    2.Then searched on online and fond below link, with Virtual Account topic saying "domain_name\computer_name$" which is solved our issue. 

    After adding the "<domain_name>\<computer_name>$"(here sql server host name) on appserver folder then it resolved the issue. 

    Virtual Account: "If the default value is used for the service accounts during SQL Server setup, a virtual account using the instance name as the service name is used, in the format NT SERVICE\<SERVICENAME>. Services that run as virtual accounts access network resources by using the credentials of the computer account in the format <domain_name>\<computer_name>$ "


    • Marked as answer by SQLDBA321 Monday, April 29, 2019 2:09 PM
    • Edited by SQLDBA321 Monday, April 29, 2019 2:10 PM modified
    Monday, April 29, 2019 2:09 PM