2

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.

2 Answers 2

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://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15

1
  • Thank you. I had a second package and file with the same issue and I had created a proxy using the account I connect to the IS database with and that was successful as well. Apr 23, 2021 at 12:33
1

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<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>$.

(Bold emphasis mine)

2
  • It is not that the accepted answer is wrong, per se (at least not where it counts); it is just suggesting an alternate path to solving the problem. You can run a stored procedure using a network/domain account using Execute As [...] in the stored procedure code; you don't need to run it as the MSSQLSERVER virtual account.
    – TylerH
    Nov 30 at 16:11
  • It's also worth noting that to add your machine account to folder permissions, you may need to go into advanced permissions settings and enable searching for computer entities, since the default is just users and groups.
    – TylerH
    Nov 30 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.