Server A: Database Server
Server B: App Server
1.Installed SQL Server 2017 RTM on Windows 2016 Server
2.SQL Server service running with default account "NT Service\MSSQLSERVER"
4.Trying to rename the file(which is on Server B another applicaiton server) using below command(with SSMS in Server A) and getting error "ACCESS IS DENIED"
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]
Answer by SQLDBA123 ·
Thanks Oleg for reply.
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>$ "
Answer by Oleg ·
If you connect to SSMS using credentials of the account who is a member of sysadmin server role (as you mentioned in your post) then the shell command spawns the windows process by using the SQL Server service account (NT Service\MSSQLSERVER in your case). Because of it, it will never have permissions to the share on the remote server. This explains the problem and can be confirmed by checking the account name which is running the shell via
The above should probably return NT Service\MSSQLSERVER.
I don't believe that you have to change the service account in order to resolve this issue. The shell command may be executed as a proxy account, you just need to create the proxy account credential. Something like this should do:
exec sp_xp_cmdshell_proxy_account 'someDomain\SomeUser', 'userPassword';
Once this is done, any NON-sysadmin login connected via SSMS will be able to run the shell command, provided that this login has been granted the permission to execute the shell:
grant exec on xp_cmdshell to 'someLoginHere';
So, to sum up: create a proxy account (AD account which has permissions to the share in question) and then grant some non-sysadmin login permission to run the shell command. To check: login to SSMS with sysadmin rights (so you can execute anything as anyone), and then run the shell command as though you are that other non-sysadmin login so that the windows process is spawned with the security context of the proxy account you just created:
execute as login = 'someLoginHere'; go -- now run this to ensure that the process runs using the proxy account: xp_cmdshell 'whoami.exe'; -- don't forget to return the context back to the original login: revert; go
According to documentation, if the login connected to SSMS IS the member of sysadmin role then the proxy account is not used, the service account is used instead (reproducing the problem). This implies that sysadmin will not be able to rename that file in question, but the lesser mortal will be. This is actually a good thing anyway because usually the app code is never connected as sysadmin anyway while if there is a need to run something manually from SSMS then the sysadmin always has the EXECUTE AS and then REVERT option.
Hope this helps.