使用SQL身份验证执行SSIS程序包 [英] Executing SSIS Package with SQL Authentication

问题描述

我有一个SSIS包，可通过HTTP与远程服务器通信.我使用数据库中的存储过程(SQL Server 2012)执行SSIS包，该存储过程是从Web服务器调用的.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程序包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！