Executing SSIS Package with SQL Authentication

Question

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.

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

From what I've been reading, it is not possible to run SSIS packages with users authenticated using SQL Server Authentication.

My questions are:

1. Is it possible to somehow elevate the SQL user to a Windows-auth user, and then execute the stored procedure.
2. Are there typical approaches in dealing with this problem (e.g. CLR, a queue table, command line call to package)?

Answer 1

This is what I did, maybe it can help someone.

First step, I created an SQL Authentication login as sysadmin and I allowed this login to impersonate other logins.

And then, I inpersonate a Windows Authentication login that is sysadmin too before calling SSIS packages. To execute scripts as another login, I used TSQL EXECUTE AS. (check the example below)

For instance: server\winuser with Windows Authentication as sysadmin, and a SQL Authentication login sqlauthuser as sysadmin too.

Enable the "Inpersonate Any Login" for sqlauthuser. Login (or use a connection string to connect) as sqlauthuser then impersonate server\winuser to be able to call SSIS packages or procedures:

EXECUTE AS LOGIN = 'server\winuser';
-- call ssis packages
-- call procedures that uses ssis packages

Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-transact-sql?view=sql-server-ver15

Answer 2

I don't think you can execute this using an SQL Server authentication, you will receive the following exception:

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:

• The operation cannot be started by an account that uses SQL Server Authentication. SSIS Package
• Issues with module signing and SSIS catalog internal procedures