Execute SSIS package from stored procedure as proxy user without xp_cmdshell

Question

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.

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.

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.

My stored procedure looks like this:

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

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

---

UPDATE:

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:

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

Here is my altered code:

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

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

Solution 1

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"

---

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 is another setting the sysadmin needed to implement and this MSDN source helps explain the usage.

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.

Solution 2

I've never tried it against a set of credentials, but you could look at EXECUTE AS

ALTER PROCEDURE [dbo].[spImportFile] 
WITH EXECUTE AS 'domain\credentials'
AS
BEGIN
    ...
END

Comments

[Simple Sandman]

I updated the question with my current issue. Thank you for leading me into the right direction

[billinkc]

For those coming to the answer for an SSIS context, the EXECUTE AS is right but wrong. Until recent version(s) of SSISDB, the first thing the procedures do is check whether they are already impersonating another entity and if so, it bails out. So, Execute AS should eventually work but for 2012-2016 (at least) it's a viable option.