Reputation: 21
We use a C# .NET application that makes a SQL Server database call using the ssisdb.catalog.start_execution
procedure to trigger SSIS to execute our packages. This service is installed on a Windows 2016 Server and runs under a specific user account that has access to the databases and the file share locations for the Excel and other flat files we interact with. This has been running successfully for several years.
We are trying to retire the 2016 server and move the service to a new 2022 server. After migrating the service to the new server all the ETL are behaving as though an anonymous user is calling them. They can't find the files and SQL Server login fails due to anonymous login. The service on the 2022 server is configured to run under the same service account as it was on the 2016 server.
The service appears to be running under the correct credentials because it is able to read our SQL Server database to get the tasks that need to be executed and it is also successfully logging into the SSIS server because the ETL packages are being executed, but the package itself does not appear to be running under the context of the account that was used to call it.
We copied the directory containing the service executable files from the 2016 to the 2022 server. Used Powershell to install the service on the new 2022 server and configured the service to run using our dedicated service account. When we started the service on the new server, we saw it picking up and processing the tasks as expected and we expected the SSIS packages to execute the same as they previously had. But they all failed and behaved as though the account they were running under did not have access to the files or the database.
Upvotes: 0
Views: 91