Job that runs an SSIS Package security issue

Question

Post reply

Job that runs an SSIS Package security issue

DamianC

SSCrazy Eights

Points: 8006
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