Job that runs an SSIS Package security issue

Question

Job that runs an SSIS Package security issue

DamianC

SSCrazy Eights

Points: 8023
More actions
October 27, 2016 at 6:21 am

#314438

Hi
I feel like I am missing something obvious in my thought process here...
Using SQL Server 2008 R2, I have a job
This job performs 3 steps
Each step is of type SQL Server Integration Services Package with a package source of File system and a Package that corresponds to the appropriate dtsx file
The packages are all part of a SSIS Project
The first package uses a number of data flow tasks to move data from an OLE DB source to an OLE DB Destination
The Source is another SQL Server Instance (in this case 2012)
The destination is the local instance from which the job runs
The second and third packages manipulate this data (I don't this this is relevant for the question)
The issue:
If I run each package manually from Visual Studio, it works fine
Yet, when run as a job created via SSMS, it fails on the first step
If I bypass the first step, it works (so no issues with step 2 and 3)
I'm certain, it's security related and suspect it's connected to the SQL Server Agent running this step along with the data source connection used
It could be something else though - if so, any so suggestions?
If it is security, how should I be setting Agent security up?
Thanks
- Damian

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

FridayNightGiant SSCertifiable Points: 7151 More actions · Answer 1

The job is most likely being executed under the context of the SQL Agent Service user which, depending how setup, may not have permissions outside of the local machine. The best solution to this, rather than giving the SQL Agent Service user elevated permissions is to create a proxy user with the required permissions. You can then set the step in the job to execute in the context of this proxy user.

https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx

DamianC SSCrazy Eights Points: 8023 More actions · Answer 2

Perfect, pointed me in the right direction and now working

Also found this useful link as a result

https://www.simple-talk.com/sql/database-administration/setting-up-your-sql-server-agent-correctly/">

https://www.simple-talk.com/sql/database-administration/setting-up-your-sql-server-agent-correctly/

Thanks

- Damian

DamianC SSCrazy Eights Points: 8023 More actions · Answer 3

Hello

Just revisiting this as I now have a scenario where it does not work

I have an AD account with a password that does not expire

I've added credentials via Security -> Credentials and used this account

I've added a Proxy that uses this Credential name and is activated for SQL SSIS Packages

I've amended the job step to run as the new proxy account

Now, if I run the job, it fails on this step with the error:

Description: Login failed for user '...'

Think I'm missing something obvious but not sure what

Any thoughts?

Thanks

- Damian