SSIS package reading xlsx unexpected termination using proxy user

Question

SSIS package reading xlsx unexpected termination using proxy user

Guy De Backer 10

I have a SSIS package that needs to read an excel (xlsx) file stored on a share on a server. The package runs flawless in Visual Studio but fails when run with the Agent on SSIS service.

I created a proxy user linked it to a credential that uses a login of a user that has access to the file and folder and under which I installed the Office Runtime (for access to the Excel file, version 16). I found out that the scheduled package runs successful when the user linked to the credential is logged on to the server on which SSIS runs. But when I log off the user the schedule fails.

Why does this user need to be logged on (have an active session) on the server for the package to run? What do I overlook here, because I don't want to consume resources with an active session on the server and prevent issues when the server reboots and the session is no restarted.

Thank you for your feedback

Guy De Backer 10 Reputation points

2023-03-08T07:31:18.54+00:00

Thank you Zoe

But that is not my problem, I have similar packages using the same proxy user consuming csv files stored on the same location that always work flawless.

I only have problems with the package consuming an excel file and even then only when the user who's credentials I use for the proxy user is logged on to the server on which SSIS runs. The problem is that I don't know where to find more information on what exactly goes wrong when it fails (the report only shows unexpectd termination, and it terminates in the validation phase). Maybe there are logs that contain more info, but I don't know where to look for them and analyse further.

But I'll try to check if the user has been granted the "logon as a batch job" rights. It feels as if that might be the problem. I'll return once that is sorted out and tested.
Brad Clark 0 Reputation points

2023-03-17T14:29:52.0466667+00:00

I am experiencing this same issue. Can you advise if you have solved the problem? And if you did, what steps you took?

Thanks,

Brad
Guy De Backer 10 Reputation points

2023-03-18T15:31:13.3266667+00:00

Hi Brad

We're still investigating the issue. For the moment I still use the workaround of keeping an open session on the server with the user defined as the credentials for the proxy user.

But we're still investigating the route of granting the logon as batch job to the user. But for now no success.

Regards

Guy
Andy E 5 Reputation points

2023-05-12T00:34:36.6633333+00:00

Hi Guy,
We're also experiencing a similar issue using an SSIS package to read an MS Access file and write to SQL Server. Have you been able to resolve your use case?
We worked with our SQL Server sys admin to look at the system event log on the server and found a DistributedCOM error 10016. We tried steps suggested in https://dba.stackexchange.com/questions/292620/sql-server-2019-service-account-local-activation-permission but are still having the problem.
We're going to try granting 'Log on as a batch job' next, but I'm guessing that did not work for you. We got out the "big hammer" and granted the proxy account local admin on the server and the job succeeded, but we don't want to be over-privileged on a production server and are hoping to narrow permissions down to only what's needed.

Thanks,
Andy
Andy E 5 Reputation points

2023-05-12T00:43:11.57+00:00

PS. We're running 2019 SQL Server on 2019 OS. Package uses MS Office 16.0 Access Database Engine OLE DB Provider to read and Native OLE DB\Microsoft OLE DB Driver for SQL Server to write. I know Office 19.0 Provider is available but we have not tried it yet. Updating to it would mean a lot of retesting for us.
Guy De Backer 10 Reputation points

2023-05-12T13:21:31.9866667+00:00

Hi Andy

I have this issue at one of my customers and we have not taken the time to look for a long term solution (read: grant the proxy user logon as batch user rights on the server). For now the workaround still works, which means we keep a session active with the proxy user credentials on the server.
But you should certainly take a look at the link from Zoe Hui (Running a SSIS Package from SQL Server Agent Using a Proxy Account) as a first step.

I'll try to add the final solution once we worked out a solution at the customer.
Andy E 5 Reputation points

2023-05-13T05:12:30.1433333+00:00

Hi Guy

Thank you for your quick reply. We worked with our SQL Server sys admin and confirmed that steps in Zoe's link were followed. We were able to eliminate DistributedCOM error 10016, but as other web posters have pointed out, that error is benign. Fixing it did not resolve the erratic behavior where our SSIS packages sporadically fail with an "Unexpected Termination" error.
We did spend considerable time granting various discrete permissions on the SSIS server to the proxy account that looked like they might be pertinent, but the only way we were able to get packages running reliably was to grant the proxy local admin on the server.

If we work out a lower privileged solution I'll post it as well.

Thanks,
Andy
Guy De Backer 10 Reputation points

2024-02-18T12:26:38.75+00:00

The user executing the SSIS job should be granted the "logon as batch job" right on the server hosting the file-share where the xlsx-file is stored. I assume this is necessary because to process the file the user has top be able to open the xlsx-file on that server.

2 answers

Your answer

Guy De Backer 10 Reputation points

2023-03-08T07:31:18.54+00:00

Thank you Zoe

But that is not my problem, I have similar packages using the same proxy user consuming csv files stored on the same location that always work flawless.

I only have problems with the package consuming an excel file and even then only when the user who's credentials I use for the proxy user is logged on to the server on which SSIS runs. The problem is that I don't know where to find more information on what exactly goes wrong when it fails (the report only shows unexpectd termination, and it terminates in the validation phase). Maybe there are logs that contain more info, but I don't know where to look for them and analyse further.

But I'll try to check if the user has been granted the "logon as a batch job" rights. It feels as if that might be the problem. I'll return once that is sorted out and tested.
Brad Clark 0 Reputation points

2023-03-17T14:29:52.0466667+00:00

I am experiencing this same issue. Can you advise if you have solved the problem? And if you did, what steps you took?

Thanks,

Brad
Guy De Backer 10 Reputation points

2023-03-18T15:31:13.3266667+00:00

Hi Brad

We're still investigating the issue. For the moment I still use the workaround of keeping an open session on the server with the user defined as the credentials for the proxy user.

But we're still investigating the route of granting the logon as batch job to the user. But for now no success.

Regards

Guy
Andy E 5 Reputation points

2023-05-12T00:34:36.6633333+00:00

Hi Guy,
We're also experiencing a similar issue using an SSIS package to read an MS Access file and write to SQL Server. Have you been able to resolve your use case?
We worked with our SQL Server sys admin to look at the system event log on the server and found a DistributedCOM error 10016. We tried steps suggested in https://dba.stackexchange.com/questions/292620/sql-server-2019-service-account-local-activation-permission but are still having the problem.
We're going to try granting 'Log on as a batch job' next, but I'm guessing that did not work for you. We got out the "big hammer" and granted the proxy account local admin on the server and the job succeeded, but we don't want to be over-privileged on a production server and are hoping to narrow permissions down to only what's needed.

Thanks,
Andy
Andy E 5 Reputation points

2023-05-12T00:43:11.57+00:00

PS. We're running 2019 SQL Server on 2019 OS. Package uses MS Office 16.0 Access Database Engine OLE DB Provider to read and Native OLE DB\Microsoft OLE DB Driver for SQL Server to write. I know Office 19.0 Provider is available but we have not tried it yet. Updating to it would mean a lot of retesting for us.
Guy De Backer 10 Reputation points

2023-05-12T13:21:31.9866667+00:00

Hi Andy

I have this issue at one of my customers and we have not taken the time to look for a long term solution (read: grant the proxy user logon as batch user rights on the server). For now the workaround still works, which means we keep a session active with the proxy user credentials on the server.
But you should certainly take a look at the link from Zoe Hui (Running a SSIS Package from SQL Server Agent Using a Proxy Account) as a first step.

I'll try to add the final solution once we worked out a solution at the customer.
Andy E 5 Reputation points

2023-05-13T05:12:30.1433333+00:00

Hi Guy

Thank you for your quick reply. We worked with our SQL Server sys admin and confirmed that steps in Zoe's link were followed. We were able to eliminate DistributedCOM error 10016, but as other web posters have pointed out, that error is benign. Fixing it did not resolve the erratic behavior where our SSIS packages sporadically fail with an "Unexpected Termination" error.
We did spend considerable time granting various discrete permissions on the SSIS server to the proxy account that looked like they might be pertinent, but the only way we were able to get packages running reliably was to grant the proxy local admin on the server.

If we work out a lower privileged solution I'll post it as well.

Thanks,
Andy
Guy De Backer 10 Reputation points

2024-02-18T12:26:38.75+00:00

The user executing the SSIS job should be granted the "logon as batch job" right on the server hosting the file-share where the xlsx-file is stored. I assume this is necessary because to process the file the user has top be able to open the xlsx-file on that server.

Answer 1

ZoeHui-MSFT 41,481

Hi @Guy De Backer,

Here you have a great article about set the SSIS Proxy users:

Running a SSIS Package from SQL Server Agent Using a Proxy Account

You may have a check to see if you miss any steps.

Make sure that you have set the job to run with SSIS proxy account.

If this do not work, please refer to this thread which has the same issue.

Regards，

Zoe Hui

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Amira Bedhiafi 32,911 Volunteer Moderator

I am assuming that the SSIS package is failing because the user that is linked to the credential is not logged on to the server due to permissions issues. When a user X is logged on to the server, his credentials are used to access the shared Excel file and this is how the SSIS package can access the file without any issues. If not the SSIS package running under the context of the SQL Agent will end up failing because of insufficient permissions to access the file.

You can try granting the necessary permissions to the SQL Server Agent service account directly on the share where the Excel file is stored.

Guy De Backer 10 Reputation points

2023-03-08T14:50:31.9633333+00:00

Thank you Amira, what you describe is the reason why I created a proxy user. That proxy user is linked to a domainuser via a credential. And it is that domainuser that has to be logged on for the momen.

That user has the necessary privileges to open en read the file. As mentioned in my previous reply, I'll check if granting the user the "logon as batch job" will fix the issue. It's similar as if you want to schedule a powershell script form within the windows scheduler unattended. Then you need to do the same.
Anonimox-0483 0 Reputation points

2024-01-26T13:58:27.5466667+00:00

@Guy De Backer Did you ever find a solution? I have tried granting the proxy user "logon as a batch job" and the issue persists.

If you haven't found a long term solution, how did you keep a session open for that user as a workaround?
Anonimox-0483 0 Reputation points

2024-02-16T14:04:11.36+00:00

I am still waiting for an answer if anybody ever finds a solution for this. The time is appreciated.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Share via

SSIS package reading xlsx unexpected termination using proxy user

2 answers

Your answer