What are the minimum permissions to resolve the 'Unexpected termination' error when executing SSIS package via agent job (run as proxy/credential)?

1

My SSIS package creates folder on the D:, copies files from network path into this folder and creates new files into this folder. Package is deployed to the sql server ssis catalog.

To configure the package to run via sql agent job, I have connected to the SQL sever as sysadmin and:

  1. Under security, configured a credential with username as domainname\user1 and the correct password.

  2. Under SQL server agent\proxies I have then configured a proxy pointing to the credential, active to the following subsystem: SSIS Package.

  3. Then I have created a Job with a step to run a SSIS package with RUN AS pointing to the above proxy.

When I right click on the job and hit start, the job fails with error. I see an 'Unexpected termination' error in the SSIS execution report. The execution report shows the error (on the task where it is trying to create Log provider file) as: The SSIS logging provider has failed to open the log. Error code 0x80070003. The system cannot find the path specified.

Suppose I add the credential user (domainname\user1) to the computer's administrators group (in computer management) and then right click on job and hit start then it runs without any errors..

Then suppose I remove this user from the administrator group, and run the job, it again gives the same 'Unexpected termination' error but there is no error description.

I don't want to add this user to the computer's administrators group and I am not able to pin-point on what permission is needed for this account. What is the minimum permission required by the credential user for my scenario?

It is something to do with folder level permissions. The user not being able to create/access folders on the D drive. (Same issue if I try to use C:). I tried adding the user to the D drive security tab with Full Control. But the error persists.

sql-server
windows
ssis
asked on Stack Overflow Apr 5, 2021 by variable • edited Apr 5, 2021 by variable

0 Answers

Nobody has answered this question yet.


User contributions licensed under CC BY-SA 3.0