Can not give NT SERVICE\MSSQLSERVER permissions on network drive

All we need is an easy explanation of the problem, so here it is.

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 had 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 had created a test folder on Server B and made the SSIS package look there, I have then given 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 can not 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. Any assistance or advise is appreciated.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

NT SERVICE\MSSQLSERVER is a local service account – therefore it does not have access to network resources (like file shares)
Change to either a Network service account or a domain account using SQL Server Configuration Manager

The different service account types are described here :-
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply