September 7, 2014 at 4:48 am
Hello,
I am setting up jobs in a Availability group environment, and I've been tasked to make sure all jobs on the primary node for this project exist on the secondary node. One specific job requires the use of a proxy account. It runs an SSIS package (that lives in SSISDB in the primary) that writes a file to a shared drive (where the Proxy account has the allowed permissions).
When I run the Job from the SQL Agent on the server where SSISDB is the primary, it runs fine. When I run the job from the SQL Agent on the secondary - in the export to file task, I get "Access is Denied" when trying to write the file to the shared drive.
Here are the facts (double & triple checked):
-The Proxy/Credentials were setup correctly and verified on both servers
-Both jobs are identical (one was used to script the other)
-Both jobs reference the SSISDB Catalog through an Availability Group Listener (I tried referencing the SSISDB primary instance directly and still same result)
-When looking at the SSISDB execution report, both jobs show the Proxy account as the caller
-When connecting the SSIS package connects to the database, both jobs shows the Proxy account as the original login
I think the availability group part is irrelevant as far as diagnosing this issue.
Some other things I tried:
-Setting the "Run as" as the SQL Server Agent service account and giving that account permissions on the shared file drive; On the primary it worked as expected (succeded when given access, failed when access was removed); On the secondary it failed 100% of the time, similar to the Proxy account
It appears that the windows permissions only works when the SQL Agent job is local to the SSISDB instance (at least when trying to write an external file). I can't failover the SSISDB to the secondary in this environment because it is a shared dev environment, but hypothetically if I were to, and find that instance2 now runs and instance1 constaintly fails, then this would confirm this hypothesis.
September 8, 2014 at 12:35 am
It's impossible for me to know because I need a lot more info than can easily be provided on a forum, but you might be hitting a double-hop/fallback scenario.
Theory: when executing the job and SSIS package on the primary instance this sounds like a single-hop scenario and so your proxy account credential makes it through the SSIS and you can access the file share with no issues. When executing the job on the secondary, connecting to the primary, and then on to the file share, this is a double-hop scenario and while your proxy account can connect to the primary SQL Server to run the SSIS, from there the security context reverts to the SQL Server service account of the primary and authentication to the file share fails.
Just a theory, but granting the service account that runs SQL Server on the primary access to the file share for a moment would test it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
September 8, 2014 at 7:36 pm
I think this might be pointing me in the right direction. Thank you
September 8, 2014 at 9:01 pm
If you happen to get to the bottom of it please post back. It may help others cruising these forums, not to mention I would be curious to know the outcome. I am in a battle with Kerberos myself at the moment and can use all the reference points I can get.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply