我正在尝试在 Azure 管道中执行使用 Microsoft.SqlServer.Management.IntegrationServices 的 PowerShell 脚本。成功调用 SSIS 包,但是尝试远程连接时包失败。我觉得这与双跳有关,但不知道如何解决这个问题。
构建服务器(域\服务用户)--> 集成服务器(域\服务用户)--> 数据库服务器(域\服务用户)
注意:Domain\ServiceUser 有权访问该实例上的 SSISDB 和远程数据库。
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'.".
为了帮助诊断问题是否与安全相关。我正在使用下面的过程来尝试调用包。当我这样做时,我收到以下错误:
--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:
当我取消注释 EXECUTE AS 时,我得到以下信息:
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.
我知道包可以与数据库服务器建立远程连接,因为当我创建一个任务来执行包并选择“运行为”SSIS 包执行代理用户“SSIS_Exec”时,它工作正常。我需要弄清楚如何让我的代码或存储过程执行类似于作为 SSIS_Exec 运行的操作。
配置目录以允许 Kerberos 约束委派工作或配置 SQL 代理作业以使用代理(或可能只是代理服务帐户)运行您的包,然后从您的应用程序中使用sp_start_job启动该作业。