debugsqlMinor
Cannot give NT SERVICE\MSSQLSERVER permissions on network drive
Viewed 0 times
cannotpermissionsgiveservicedrivenetworkmssqlserver
Problem
I have an SSIS package that I would like to run as a job. The flat file source sits on Server A while the package and job sit on Server B.
When I run the job that executes the SSIS package I get an error:
Error code 0xC020200E Cannot open the datafile
but I can manually execute the deployed package in SSMS and it executes successfully. I found out that this was caused by the Server B's NT SERVICE\MSSQLSERVER service account not having access to the folder on Server A.
To test this I created a test folder on Server B and made the SSIS package look there. I then gave NT SERVICE\MSSQLSERVER full access to the folder on Server B. The job executed successfully and the package ran, however when I try to give NT SERVICE\MSSQLSERVER permissions to the folder on server A, I cannot find the server in the locations tab and I cannot access the NT SERVICE\MSSQLSERVER service account.
I have tried mapping the network drive, however that did not help. The files are automatically uploaded onto server A every morning, the folder has to stay on Server B.
When I run the job that executes the SSIS package I get an error:
Error code 0xC020200E Cannot open the datafile
but I can manually execute the deployed package in SSMS and it executes successfully. I found out that this was caused by the Server B's NT SERVICE\MSSQLSERVER service account not having access to the folder on Server A.
To test this I created a test folder on Server B and made the SSIS package look there. I then gave NT SERVICE\MSSQLSERVER full access to the folder on Server B. The job executed successfully and the package ran, however when I try to give NT SERVICE\MSSQLSERVER permissions to the folder on server A, I cannot find the server in the locations tab and I cannot access the NT SERVICE\MSSQLSERVER service account.
I have tried mapping the network drive, however that did not help. The files are automatically uploaded onto server A every morning, the folder has to stay on Server B.
Solution
The accepted answer is wrong. The 'NT SERVICE\MSSQLSERVER' is not a local service account, it is virtual account.
Virtual Accounts are running tasks in the context of the computer they are installed to.
So if you want to access your SQL Server Service a network share, you have to grant access to the computer the SQL Server is running at.
Assuming, your machines name is SQLSERVER you have to grant access to the share to the DOMAIN\SQLSERVER$ account so that your SQL service can access it.
From Microsoft Learn:
Virtual accounts (beginning with Windows Server 2008 R2 and Windows 7) are managed local accounts that provide the following features to simplify service administration. The virtual account is auto-managed, and the virtual account can access the network in a domain environment. 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. Services that run as virtual accounts access network resources by using the credentials of the computer account in the format
(Bold emphasis mine)
Virtual Accounts are running tasks in the context of the computer they are installed to.
So if you want to access your SQL Server Service a network share, you have to grant access to the computer the SQL Server is running at.
Assuming, your machines name is SQLSERVER you have to grant access to the share to the DOMAIN\SQLSERVER$ account so that your SQL service can access it.
From Microsoft Learn:
Virtual accounts (beginning with Windows Server 2008 R2 and Windows 7) are managed local accounts that provide the following features to simplify service administration. The virtual account is auto-managed, and the virtual account can access the network in a domain environment. 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. Services that run as virtual accounts access network resources by using the credentials of the computer account in the format
\$.(Bold emphasis mine)
Context
StackExchange Database Administrators Q#290178, answer score: 5
Revisions (0)
No revisions yet.