I am attempting to execute a PowerShell script that uses Microsoft.SqlServer.Management.IntegrationServices in an Azure pipeline. The SSIS package is successfully invoked, however, the package fails when a remote connection is attempted. I feel this is double-hop related but can't figure out how to resolve the issue.
Build Server(Domain\ServiceUser) --> Integrations Server(Domain\ServiceUser) --> Database Server(Domain\ServiceUser)
NOTE: Domain\ServiceUser has rights to the SSISDB on that instance and to the remote database.
My Project Import:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.".
In order to help diagnose if the problem is security related. I am using the procedure below to attempt to invoke the package. When I do that, I get the following error:
--EXECUTE AS LOGIN = 'Domain\ServiceUser' Declare @execution_id bigint EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name='ProjectFolder', @project_name='ProjectFolderImport', @use32bitruntime=False, @reference_id=Null EXEC [SSISDB].[catalog].[start_execution] @execution_id The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication. Msg 6522, Level 16, State 1, Procedure start_execution_internal, Line 0 [Batch Start Line 0] A .NET Framework error occurred during execution of user-defined routine or aggregate "start_execution_internal": System.Data.SqlClient.SqlException: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication. System.Data.SqlClient.SqlException:
And when I uncomment the EXECUTE AS, I get the following:
Cannot execute as the server principal because the principal "Domain\ServiceUser" does not exist, this type of principal cannot be impersonated, or you do not have permission.
I know the package can make a remote connection to the db server because when I create a task to execute the package and select "Run as" SSIS Package Execution Proxy User "SSIS_Exec" it works fine. I need to figure out how to have my code or stored procedure do something similar to run as SSIS_Exec.