title: Executing SSIS Package with SQL Authentication
body: <p>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.</p>
  
  <p>The SSIS package has complex variables that are passed.  The stored procedure that runs the package looks something like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>From what I've been reading, it is not possible to run SSIS packages with users authenticated using SQL Server Authentication.</p>
  
  <p>My questions are:</p>
  
  <ol>
  <li>Is it possible to somehow elevate the SQL user to a Windows-auth user, and then execute the stored procedure.</li>
  <li>Are there typical approaches in dealing with this problem (e.g. CLR, a queue table, command line call to package)?</li>
  </ol>