使用 SQL 身份验证执行 SSIS 包

Executing SSIS Package with SQL Authentication(使用 SQL 身份验证执行 SSIS 包)

问题描述

我有一个通过 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 目录内部程序的问题

这篇关于使用 SQL 身份验证执行 SSIS 包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！