Permissions Issue running SSIS package from Sql Jo

Question

I have an SSIS package deployed to Sql Server 2008. This package runs fine in BIDS as well as from the server within the stored packages directory. As soon as i tie this package to a sql job i start to see permission issues accessing a specific file from within the SSIS package. So through my research i see that using a proxy account may be the solution to my problems. These are the steps i have taken so far:

1. Setup an Active Directory User Account
2. Under Security - Credentials - Created a Credential Tied to the account in step 1.
3. Under Sql Server Agent - Proxies - SSIS Package Execution - Created a proxy tied to the credential above and selected the "SQL Server Integration Services Package" check box.
4. Created a login with roles "SQLAgentOperatorRole", "SQLAgentReaderRole", "SQLAgentUserRole" and "Public" on the msdb database.
5. Create job to run my SSIS package. Job owner is tied to the login created in step 4. Under the step properties of my job i set the type to "Sql Server Integration Services Package" and the Run as option to my proxy account created in step 3 and pointed it to my SSIS package.
6. Gave Full control to the account from step 1 access to the folder/file that the SSIS package is pointing to.

When i kick the job off i continually get and error:

Executed as user: "Account from step 1" - Could not load package. Login Failed for user: "Account from step 1". The package could not be loaded.

What step could i be missing to accomplish this task?

Thank you for you time, Billy

Answer 1

Figured this one out. Resolved by setting up the same AD account as a login on sql sever with the same login roles as those above in step 4.

Hope this helps someone.

Answer 2

Have you looked at the windows services to see the account that runs the SQL Server Agent? If that login (the one that runs the service) has sufficient rights to run a query on the database then you could just change the service login to yours (for testing purpose). Once thats done, restart the service and give it a try. Let us know what you find.