1

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.

2

Either configure the directory to allow Kerberos Constrained Delegation to work or configure a SQL Agent job to run your package with a proxy (or the perhaps just Agent Service Account), and kick off the job with sp_start_job from your applciation.

2
  • I am currently looking into the Kerberos Constrained Delegation. – Ross Bush Feb 2 at 0:58
  • 1
    I was able to create a job set to run a powershell script as "SSIS_Exec". That script calls a SP that sets the ssis execution parameters to a lookup result and then calls the [SSISDB].[catalog].[start_execution] @execution_id targeting a remote db and that worked, so I may use that as a fallback. – Ross Bush Feb 3 at 14:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.