1

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.

Improve this question
10
  • Are you using the same take user account? Sounds like the user needs delegation to be switched on in AD.
    – eckes
    Feb 26, 2020 at 20:12
  • Have you checked for blocking during the time between starting the job and it failing?
    – Mr.Brownstone
    Feb 26, 2020 at 20:55
  • Typo.. it meant target
    – eckes
    Feb 26, 2020 at 21:01
  • @Mr.Brownstone No blocking this package performs BCP off a remote server as its first few steps and does not touch any tables of concurrently running executions. It is a locked down instance so outside interference is unlikely.
    – LowlyDBA - John M
    Feb 26, 2020 at 21:01
  • @eckes Yeah same account through all testing scenarios.
    – LowlyDBA - John M
    Feb 26, 2020 at 21:02

1 Answer 1

Reset to default
1

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.

Improve this answer
2
  • I hoped this was not the answer. The whole point of using Airflow, in our case, is to unify various job scheduling and avoid using SQL Agent, so hitching this back up to an Agent job defeats the point. Thanks for the response.
    – LowlyDBA - John M
    Feb 27, 2020 at 14:55
  • 1
    It's pretty common to define jobs in SQL Agent, but then schedule, execute, and monitor them in an external scheduler. SQL Agent has built-in support for configuring SSIS packages, for instance. So you put all the details in the Agent Job steps, and then simply kick off the job in the external scheduler.
    – David Browne - Microsoft
    Feb 27, 2020 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.