Sql-server – SSIS Package execution fails when started by user logging in with FreeTDS

freetdssql serversql-server-2016ssisUbuntu

I am attempting to let a login kick off an SSIS package via the built-in SSISDB stored procedures instead of doing so through SQL Agent.

The login will be connecting through an Airflow DAG on a Ubuntu box, so we're utilizing FreeTDS / pyodbc to allow it to authenticate as a Windows Login, which is working fine.

However the following happens when launching the package:

  1. Package is launched using [SSISDB].[catalog].[start_execution] @execution_id. Tried running both synchronously and asynchronously.
  2. An execution ID is generated, along with an entry in [SSISDB].[catalog].[executions] with a status of 5 ("Pending")
  3. ~1 minute later the the execution "completes" and the execution ID is wiped from all SSISDB tables and does not show up in any of the built-in reports.

Even when adding exec parameters to create dump files, none are created during the package execution.

Bonus problem: During the ~1 minute timeframe, other packages being launched from SQL Agent that generally work fine often fail with this error message:

The operation failed because the execution timed out.

Notes:

  • The SQL Server installation is on Windows and is SQL Server 2016 Ent.
  • This process works fine when using my Windows Authentication login from a Windows box.
  • The Windows account being used by the DAG is the agent service account, so has all necessary permissions.
  • The two SID fields in [SSISDB].[catalog].[executions] for the pending execution match the SIDs of other working executions being handled by SQL Agent for the same service account.
  • We are using TDS version 7.2 and the latest FreeTDS version.
  • No difference when using autocommit True/False
  • No blocking seen during execution

I am guessing the login is being re-authenticated when it is used to launch the external SSIS process, but am hoping there may be some way to make this work still.

Calling via CLR is an undesirable option due to the added complexity and security implications.

I am also currently testing launching them via dtexec via xp_cmdshell (looking promising) but it is unclear to me how/why that would work and using the stored procedures does not.

Best Answer

SSIS catalog executions impersonate the connected login, so probably correct that it's an auth issue. Easy workaround is to provision a SQL Agent Proxy for the identity you want to run the package, and configure an Agent Job to run the package, invoking the job with sp_start_job.

This is similar to, but more secure than, using xp_cmdshell to run dtexec directly, or call the package execution procedures from powershell. In both cases a local identity is used to execute the package.

Related Question