January 4, 2021 at 9:20 pm
Hello, I have a SSIS Package deployed to the SSISDB Catalog. I have a stored procedure setup to create an execution, pass in a few variables/parameters and then start the execution.
I created a tsql jobstep to call the stored procedure.
This works as long as the agent job is owned by an SA user.
I'm trying to reduce permissions and allow non SA admins to manage some jobs. The user in question has , MSDB-SQLAgentOperator, SSISDB-ssis_admin roles and appropriate permissions to execute the procedure and access all data.
If I run the procedure from SSMS it runs fine, the package executes and all is well.
If I run the package from an Agent Job owned by my limited permission user the procedure is called, and begins to execute as intended, the ExecutionID is created, paramters are set, but when executing EXEC [SSISDB].[catalog].[start_execution] @executionID = #### to actually start the SSIS package running, the following error is thrown.
SQLSTATE 01000] (Message 0) The current security context is non-revertible. The "Revert" statement failed. [SQLSTATE 42000] (Error 15196). The step failed.
I believe this is because the SQL Agent executes the jobstep using the following: EXECUTE AS LOGIN = N'domain\user' WITH NO REVERT. and the MS SSISDB code contains a number of REVERTS.
Does anyone have any ideas on how to work around this? Basically I want a non-sa user to manage a job which runs this code. I made them the owner of the job, but now the code will not run correctly.
Thanks in advance for your help.
-LukeL.
January 5, 2021 at 10:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
January 8, 2021 at 7:40 am
This was removed by the editor as SPAM
January 8, 2021 at 2:27 pm
I'm not certain if this info is pertinent to your question, but thought I would tell you anyway. If this isn't pertinent or you already know about this then please disregard.
In general, you'll want to set up the job to run with an SSIS package execution proxy, rather than letting the job run as whomever the current user is (e.g. you). A proxy gives you better control over who runs the package, it essentially runs the package with credentials of the principals that back the proxy. The general idea is to use an account with sufficient privileges as the principal of the proxy, then in your SSIS job step, set the job step to run as the proxy. If you look in SSMS, under SQL Server Agent/Proxies you'll see an "SSIS Package Execution" section...this is where you'd add an SSIS execution proxy. Where I work, we usually will set up a network domain "service account" and give it database access it needs to run things (so, server login, user in database, assign the account whatever privs it needs, database roles etc.), then use that as the principal of an SSIS execution proxy, then set the SQL Agent job to run as that proxy. Anyway, it's the access privileges of that service account that are important.
I'm not sure why you would need SA unless your job does work that actually requires that, that should be pretty darn rare. If you go the proxy route, then maybe first try slimming down your proxy principal (I would not try running as you...don't fiddle with your own access unless you're just testing to see what you need to run the package properly) to "dbo" (role) access to just the database where your proc lives. "dbo" is still pretty high-level and might be a lot more than you actually need but it's not server-wide like SA is at least. Or, you may be able to get away with more limited access, e.g. data_reader + data_writer (roles again) if you need to only read/write data (or if you need something more fine-grained or don't want to use the built-in database roles), create your own database role, grant it the privs you need, then make your proxy principal a member of the role). Fiddling with permissions is a lot easier if you're using a proxy and a separate pricipal though.
Also, don't make a user an "owner" of the job, I'd make the "owner" "sa". That doesn't establish privileges or access for the job, but it does protect you from hassles that occur should the "owner" of the job leave your company or whatever, in which case you would have a job hanging around with a user that technically doesn't exist anymore. Not good.
Not sure if my suggestions are any good or not, please someone else jump in here if I'm off base.
Google for
January 15, 2021 at 1:55 pm
@dmbaker, thanks for your reply.
The idea behind making a user an owner of a job follows from the want/need to have users who are not SA's manage their own jobs, including editing them as needed. Non-SA's do not have the ability to create a job owned by SA. Even as members of the AgentOperator role where they can execute any job, they can still only edit their own.
Everything else follows from there. Yes, we could use SSIS Jobsteps with the SSIS Proxy account, and that works, however most of our jobs utilize a sproc to pass in parameters pulled from a config table to set Connection strings and other execution context variables. In order to switch all of that code to be contained within the SSIS packages will be a very large undertaking that we're not looking to perform at this time. It's the TSQL Job step calling a sproc that in turns executes the appropriate packages from the SSISDB Catalog which are causing the problem...
Microsoft's sp_start_Exectuion procedure runs a number of Execute as Caller/Revert statements which is what I believe is limiting this ability to execute correctly.
Everything works correctly when the same TSQL from the same non-SA user runs the stored procedure from any connection other than from a TSQL Agent Job Step.
We would like the agent jobsteps to run successfully with a limited permission set while we work on re-coding all of our packages to encapsulate the parameter logic within the SSIS Package itself.
Thanks,
-Luke.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply