SQL Server – Grant NT SERVICE\MSSQLSERVER Permissions on Network Drive

jobspermissionsssis

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.

Best Answer

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

Related Solutions

Sql-server – Login failure when running a SSIS package from a SQL Server job

I had a similar problem while importing data from an Excel file using SSIS packages. I found this tutorial and solved the problem.

EDIT: Important steps:
1 - When saving the SSIS package to the file system, select the "Package protection level" to "Do not save sensitive data".

Then, follow these steps:
1 - Open the SSIS package in Visual Studio
2 - Select SSIS -> Package Configurations
3 - On the Package Configurations Organizer click Add
4 - On the window that'll open click Next
5 - Set the configuration type to XML configuration file
6 - On the Configuration file name click Browse, select the folder in which you want to store the configuration file, choose a name to this file and then hit Save
7 - When you're done here, click Next
8 - Choose the right connection manager and click on the + sign to expand it, then expand Properties and check the checkbox correspondent to Password. Then click Next.
9 - Choose a name to your configuration file and click Finish.

The following XML configuration file will be created on the location you specified previously (here I'm using the code from the tutorial as an example):

<?xml version="1.0"?>

<DTSConfiguration>

      <DTSConfigurationHeading>

            <DTSConfigurationFileInfo GeneratedBy="Domain\UserName" GeneratedFromPackageName="Package" GeneratedFromPackageID="{77FB98FB-E1AF-48D9-8A43-9FD6B1790837}" GeneratedDate="22-12-2009 16:12:59"/>

      </DTSConfigurationHeading>

      <Configuration ConfiguredType="Property" Path="\Package.Connections[runeet2k8.sa].Properties[Password]" ValueType="String">

                  <ConfiguredValue></ConfiguredValue>

      </Configuration>

</DTSConfiguration>

The only thing you'll have to do here is edit the XML file and change this:
<ConfiguredValue></ConfiguredValue>
into this:
<ConfiguredValue>YourPasswordGoesHere</ConfiguredValue>

Then save the package.

When creating your SQL job, don't forget to add the configuration file to the job in the tab Configurations.

Sql-server – SSIS Package Validation Error when Executed From Windows Task Scheduler

I could not resolve this issue and changed my approach to utilize a SQL Job instead of running the package directly from a powershell script.

Summary of solution:

SQL Job with one Job Step
The Job Step executes the SSIS package

To run a SQL Job with a custom you must also create:

an instance-level Credential
a Proxy Account which should be mapped to the Credential

I still wonder what's different about this approach versus the 'interactive' approach listed above. Both approaches run with the exact same identity and privileges!

For an in-depth description of the steps required to configure a SQL Job, look at this example code for Automated Deployment of a SQL Job and Job Step.

Hope this helps someone else in the same situation -

Related Question