sql server - 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: serverwinuser 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 serverwinuser to be able to call SSIS packages or procedures:

EXECUTE AS LOGIN = 'serverwinuser';
-- 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