2

Building an ETL framework with many packages and a "controlfile" table that orchestrates running them in the right order and managing errors. In order to control the overall operation of the data load I would like to do this:

  1. Make a job(s) in SQL Agent
  2. In the job steps invoke a custom stored procedure
  3. In the stored procedure, call SSISDB.catalog.create_execution and related procs to execute a package
  4. Run this under a dedicated ETL Windows user account that is NOT a sysadmin and is not the SQL Agent service account, for security reasons.

Everything in steps 1, 2, and 3 works, but if I try to configure the agent job step to run as another account or as a proxy it doesn't work.

Background

Running a single package from an Agent job with the job step type "Integration Services" works, because there's an option to set the step to run as a proxy user. (New Job > Steps > New Step > Type = Integration Services > RUN AS). The problem is that we need the "wrapper" logic to control all these individual packages.

Running a package from a stored procedure directly, from T-SQL, works.

Running a package from a stored procedure in a T-SQL job STEP in Agent, if the job is owned by a sysadmin works, but it runs as the SQL Server Agent Service Account which is a security problem.

Running a package from a stored procedure in a T-SQL job step in Agent, if the job is owned by a NON sysadmin user fails because of a problem in the Agent's EXECUTE AS code, where the SSIS procedures throw an error like 'could not revert' - which leads me to believe there's a problem in the Agent's EXECUTE AS logic running the T-SQL under the job owner account, then calling the SSIS procedures. I have found some information online which I hope I can re-locate an link to.

Running a package from a T-SQL Agent job step has no RUN AS capability to set the code to run under a proxy account. This is a connect item :-).

Running a package from a T-SQL Agent job where I manually put in EXECUTE AS logic in the job step code also doesn't work.

Stumper?

  • Is it necessary that the SSIS jobs run through SQL Agent? I've done similar things, but used SQL Agent to launch my app, which in turn launched dtexec, called sprocs, performed file operations, etc. – Jon of All Trades Feb 20 '15 at 21:24
  • I'm sort of headed toward calling this from PowerShell or SQLCMD - I think Agent could proxy the user for those correctly (but I have to test). It just feels clunky because in this case there's no other logic needed that would go in an "extra" layer of script like that. It'd just be a workaround for this specific issue. – onupdatecascade Feb 20 '15 at 22:39
  • Here's an example of the "Can't revert" / Execute As issue stackoverflow.com/questions/17463044/… – onupdatecascade Feb 20 '15 at 22:39
  • 1
    I have tried to run a stored procedure using specific credentials and have failed too. But in your case you might be helped with creating the stored procedure with the WITH EXECUTE AS [username] clause. Then the SQL Agent user only needs to be able to execute the stored procedure. – MWillemse Feb 20 '15 at 22:44
  • I assume you don't want to create N SQL Agent jobs that use the windows ETL credentialed/proxy user to run the packages that then get invoked from the stored proc call? Or to state differently, you're trying to do all the same steps that invoking an SSIS package stored in the SSISDB as a credentialed user but it doesn't work from tsql? (that parses in my head, not sure if it comes through properly) – billinkc Feb 21 '15 at 23:30
0

I ended up using a PowerShell step in SQL Agent to work around this: job step type "PowerShell" with a short script, which uses Invoke-Sqlcmd to call the stored procedure. Feels awkward, but it works under the alternate credentials.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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