以不带xp_cmdshell的代理用户身份从存储过程执行SSIS程序包 [英] Execute SSIS package from stored procedure as proxy user without xp_cmdshell

问题描述

我正在尝试通过存储过程运行SSIS程序包，但是在尝试导入CSV时出现Access is denied错误.

I am trying to run an SSIS package through a stored procedure, but I am getting an Access is denied error when I try to import a CSV.

我将程序包放入作业中，然后运行它，只要我使用了代理帐户，它就可以正常工作.我正在尝试使用xp_cmdshell将该代理帐户复制到存储过程调用而没有的情况下.我也在Visual Studio中运行了该程序包，并且运行顺利.

I put the package inside a job and ran it and it worked as long as I used a proxy account. I am trying to replicate that proxy account to the stored procedure call without using xp_cmdshell. I also ran this package inside Visual Studio and it ran smoothly.

我的SSIS包很简单:它从网络导入CSV文件，将数据转换为varchar，然后将数据存储到表中.

My SSIS package is simple: It imports a CSV file from the network, converts the data to varchar, and stores the data into a table.

即使我的系统管理员也无法成功运行存储过程.

Even my sysadmin was not able to successfully run the stored procedure.

我的存储过程如下:

ALTER PROCEDURE [dbo].[spImportFile] 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @execution_id bigint
    EXEC SSISDB.CATALOG.create_execution
        @folder_name = 'folder_name',
        @project_name = 'project_name',
        @package_name = 'package_name.dtsx',
        @use32bitruntime = 1,
        @execution_id = @execution_id output

    EXEC SSISDB.CATALOG.start_execution @execution_id
END

我的问题是，如何在不使用xp_cmdshell的情况下以编程方式在此存储过程中使用代理用户?

My question is, how can I programmatically use a proxy user inside this stored procedure without using xp_cmdshell?

更新:

由于 billinkc ，我现在正试图模拟我的代理用户，但现在我遇到此错误执行SSIS包:

I am now trying to impersonate my proxy user thanks to billinkc, but now I am running into this error when I execute the SSIS package:

无法还原当前的安全上下文.请切换到调用"Execute As"的原始数据库，然后重试.

The current security context cannot be reverted. Please switch to the original database where 'Execute As' was called and try it again.

这是我更改的代码:

ALTER PROCEDURE [dbo].[spImportFile] 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    EXECUTE AS LOGIN = 'domain\credentials'

    DECLARE @execution_id bigint
    EXEC SSISDB.CATALOG.create_execution
        @folder_name = 'folder_name',
        @project_name = 'project_name',
        @package_name = 'package_name.dtsx',
        @use32bitruntime = 1,
        @execution_id = @execution_id output

    EXEC SSISDB.CATALOG.start_execution @execution_id -- <<<< ERROR HERE!

    REVERT

END

通过查看我通常无权访问的系统表，成功地测试了EXECUTE AS LOGIN和REVERT而没有start_execution.

I successfully tested EXECUTE AS LOGIN and REVERT without start_execution by looking into a system table I wouldn't usually have access to.

推荐答案

我已经意识到，由于我要冒充用户并鼓励我使用一份工作，因此制作一个作业以使用代理帐户在服务器上运行此SSIS包.

I have come into a realization that since I am going to impersonate a user and that I am encouraged to use a job, it will be much easier to make a job to run this SSIS package on the server with a proxy account.

这是我的包含运行作业的解决方案:

Here is my solution that includes running a job:

ALTER PROCEDURE [dbo].[spImportFile] 
    @intStatus int output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT user_name() -- test before execute

    EXECUTE AS LOGIN = 'domain\credentials'

    SELECT user_name() -- test after execute

    -- Start job
    DECLARE @job_name VARCHAR(100) = 'JobName'
    EXEC msdb.dbo.sp_start_job @job_name = @job_name

    -- Wait for job to finish
    DECLARE @job_history_id AS INT = NULL
    DECLARE @intLimit AS INT = 10
    DECLARE @intAttempt AS INT = 1

    WHILE @intAttempt < @intLimit
    BEGIN
        SELECT TOP 1 @job_history_id = activity.job_history_id
        FROM msdb.dbo.sysjobs jobs
        INNER JOIN msdb.dbo.sysjobactivity activity ON activity.job_id = jobs.job_id
        WHERE jobs.name = @job_name
        ORDER BY activity.start_execution_date DESC

        IF @job_history_id IS NULL
        BEGIN
            WAITFOR DELAY '00:00:01'
            CONTINUE
        END
        ELSE
        BEGIN
            BREAK
        END

        SET @intAttempt = @intAttempt + 1
    END

    -- Check exit code
    SELECT @intStatus = history.run_status
    FROM msdb.dbo.sysjobhistory history
    WHERE history.instance_id = @job_history_id

    REVERT

    SELECT user_name() -- test after revert

END

此工作代码基于以下问题，"

This job code was based on this question, "Executing SQL Server Agent Job from a stored procedure and returning job result"

发现:
我了解到您需要从 MSDN源代码中GRANT IMPERSONATE ON LOGIN::[domain\ProxyUser] to [domain\credentials].

Findings:
I have learned that you need to GRANT IMPERSONATE ON LOGIN::[domain\ProxyUser] to [domain\credentials] from this MSDN source.

ALTER DATABASE database_name SET TRUSTWORTHY ON是实现所需的sysadmin的另一个设置，此 MSDN来源有助于说明用法.

ALTER DATABASE database_name SET TRUSTWORTHY ON is another setting the sysadmin needed to implement and this MSDN source helps explain the usage.

备注:
该解决方案基于以下事实:我是数据库的dbo，并且我对Windows安全组具有代理帐户的sysadmin授予模拟.我也在使用Windows身份验证.

Remarks:
This solution is based on the fact that I am the dbo of the database and I had a sysadmin grant impersonation of the proxy account to my windows security group. I am using Windows authentication as well.

我已经更新了问题，以不限制最初使用此问题的任何人使用作业.如果有不需要工作的解决方案，我将非常乐意看一下，甚至在这个问题上更改已接受的解决方案.

I have updated the question to not restrict the use of jobs for anyone that initially was working on this question. If there is a solution that doesn't require jobs, I will be more than happy to take a look and even change the accepted solution on this question.

这篇关于以不带xp_cmdshell的代理用户身份从存储过程执行SSIS程序包的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！