xp_CmdShell getting access denied error from DBServer to AppServer
Question
Tuesday, April 23, 2019 1:50 PM
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
All replies (3)
Tuesday, April 23, 2019 9:29 PM ✅Answered
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
Monday, April 29, 2019 2:09 PM ✅Answered
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>$ "
Wednesday, April 24, 2019 7:47 AM
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:
Andreas Wolter (Blog | Twitter)
Senior Program Manager SQL Server & Azure Security
MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012