使用 SQL 身份验证执行 SSIS 包

2021-12-30 00:00:00 sql etl sql-server ssis sql-server-data-tools

我有一个通过 HTTP 与远程服务器通信的 SSIS 包.我使用从 Web 服务器调用的数据库 (SQL Server 2012) 中的存储过程执行 SSIS 包.Web 服务器使用 Windows 身份验证连接到数据库.我现在需要从不支持 Windows 身份验证的客户端运行存储过程(以及 SSIS 包).SSIS 包非常复杂，无法迁移到不同的解决方案.

I have a SSIS package that talks to a remote server over HTTP. I execute the SSIS package using a stored procedure in my database (SQL Server 2012), which is called from a web server. The web server connects to the database using Windows Authentication. I now have a need to run the stored procedure (and therefore, the SSIS package) from a client which does not support Windows Authentication. The SSIS package is complicated enough that migrating to a different solution is not feasible.

SSIS 包具有传递的复杂变量.运行包的存储过程类似于:

The SSIS package has complex variables that are passed. The stored procedure that runs the package looks something like:

CREATE PROCEDURE [dbo].[SSISPackage]
    @Parameter1 XML
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @execution_id BIGINT
    EXEC [SSISDB].[catalog].[create_execution] 
      @package_name=N'Package.dtsx',
      @execution_id=@execution_id OUTPUT,
      @folder_name=N'API',
      @project_name=N'APIProject',
      @use32bitruntime=False,
      @reference_id=Null

    EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
      @execution_id,
      @object_type=30,
      @parameter_name=N'Parameter1',
      @parameter_value=@Parameter1

    EXEC [SSISDB].[catalog].[start_execution] @execution_id     
END

据我所知，使用 SQL Server 身份验证进行身份验证的用户无法运行 SSIS 包.

From what I've been reading, it is not possible to run SSIS packages with users authenticated using SQL Server Authentication.

我的问题是:

  1. 能否以某种方式将 SQL 用户提升为 Windows 身份验证用户，然后执行存储过程.
  2. 是否有处理此问题的典型方法(例如 CLR、队列表、对包的命令行调用)?

推荐答案

我不认为您可以使用 SQL Server 身份验证执行此操作，您将收到以下异常:

I don't think you can execute this using an SQL Server authentication, you will receive the following exception:

使用 SQL Server 身份验证的帐户无法启动该操作.使用使用 Windows 身份验证的帐户开始操作

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication

您可以采取多种解决方法，请查看以下链接:

There are many workaround that you can do, check the following links:

  • 操作无法由使用 SQL Server 身份验证的帐户启动.SSIS 包
  • 模块签名和 SSIS 目录内部程序的问题

相关文章