SSIS error when trying to run using Windows Authentication

Question

I'm having issues running an SSIS package. There are several elements to this issue, and what I'm hoping for is the overall correct way to accomplish the goal.

This SSIS package imports from an Excel file on the network into a database (MyDb). It works properly in VS. Originally, it also works properly when deployed and I'm logged in as the sysadmin (mostly... I'll go into further detail further down), but it doesn't work when logged in as anybody else.

A key part of the problem is that most users who need to be able to run this package do not actually have direct SELECT, INSERT, UPDATE, or DELETE permissions to the tables that are being read or updated. This is on purpose and I need to keep it that way. I need them to be able to update the tables through the SSIS package, but not willy-nilly on demand.

To get around this problem, after much tweaking, I've created the following stored procedures:

USE [MyDb]
GO

CREATE PROCEDURE [impexp].[udp_ImportSpreadsheet_PersonActionsAndEvents]
    -- Add the parameters for the stored procedure here
    @ActionID int
    , @FileName nvarchar(250)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @execution_id bigint

    DELETE FROM [impexp].[PersonActionsAndEventsImport_CellNoMatch]
    WHERE ActionID = @ActionID;

    EXECUTE [SSISDB].[impexp].[udp_ImportSpreadsheet_PersonActionsAndEvents] 
        @ActionID
        ,@FileName
END

USE [SSISDB]
GO
CREATE PROCEDURE [impexp].[udp_ImportSpreadsheet_PersonActionsAndEvents]
    -- Add the parameters for the stored procedure here
    @ActionID int
    , @FileName nvarchar(250)
WITH EXECUTE AS 'MyDomain\SSISUser'
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    DECLARE @execution_id bigint

    EXEC [SSISDB].catalog.create_execution
        @package_name=N'EventsImport.dtsx',
        @execution_id=@execution_id OUTPUT,
        @folder_name=N'Allusr_ImportsAndExports',
        @project_name=N'Imports',
        @use32bitruntime=TRUE,
        @reference_id=Null
    SELECT @execution_id

    --run synchronously so the rest of the code doesn't run until after it's done.
    EXEC [SSISDB].[catalog].[set_execution_parameter_value] 
        @execution_id
        , @object_type = 50                     -- System parameter
        , @parameter_name = N'SYNCHRONIZED'
        , @parameter_value = 1

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

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

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

'MyDomain\SSISUser' DOES use Windows Authentication. It's a member of two user-defined roles in SSISDB that give it access to execute the package (other members of these roles also have Impersonate permissions to 'MyDomain\SSISUser' in SSISDB), and it's a member of the datareader and datawriter roles in MyDb.

(WITH EXECUTE AS 'MyDomain\SSISUser' is a recent change that I made earlier today - prior to that, I was trying to have it execute as a different, SQL-authenticated, user).

If I run this as-is, I get the following errors (whether I'm logged in as a sysadmin or not):

Msg 27123, Level 16, State 1, Line 3 The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Integrated Authentication.
Msg 6522, Level 16, State 1, Procedure internal.start_execution_internal, Line 0 [Batch Start Line 2]
A .NET Framework error occurred during execution of user-defined routine or aggregate "start_execution_internal": System.Data.SqlClient.SqlException: The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Integrated Authentication.
System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.DispatchMessages(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) at System.Data.SqlClient.SqlCommand.ExecuteToPipe(SmiContext pipeContext) at Microsoft.SqlServer.Server.SqlPipe.ExecuteAndSend(SqlCommand command) at Microsoft.SqlServer.IntegrationServices.Server.ServerConnectionControl.RaiseError(SysMessageId messageId, SysMessageSeverity severity, Object[] args) at Microsoft.SqlServer.IntegrationServices.Server.ServerApi.StartExecutionInternal(SqlInt64 projectId, SqlInt64 executionId, SqlInt64 versionId, SqlInt16 use32BitRuntime)

If I comment out "WITH EXECUTE AS 'MyDomain\SSISUser'", then it runs perfectly when logged in as a sysadmin/dbowner of MyDb, but when logged in (with Windows-Authenticated logins) as anybody else (i.e. people who don't have direct access to the tables), then the package runs with errors (the main error is, "The SELECT permission was denied on the object 'Mytablename' .... ").

So, the core issue is that I need users who have no direct access to the tables to be able to run SSIS packages that read and update tables based on an Excel file, and impersonation doesn't appear to be working. (I've also tried certificate signing the procedures - that didn't work, I'm guessing for much the same reason). Is there a way to get impersonation working, or is there another way to achieve the same goal?

Running SQL Server 2019 Standard edition.

Answer 1

Turned out to be quite simple all along. The solution was to edit the connection to the database in the original (VS) package, so that SSIS logs in as a (SQL-Authenticated, in my case) user with permissions to do everything it needed to do. Previously, the SSIS package connection to the SQL Server was using Windows authentication.

Once that was done, I just removed the "WITH EXECUTE AS..." line from the SSIS procedure, and it worked perfectly, whether I was logged in as the owner or not.