SSIS Login Error When Scheduling Package

Question

I created a SSIS package in Visual Studio 2010 (BIDS). It's using an OLE DB Source and an OLE DB Destination. Both the source and destination use Windows authentication (a domain user) to connect and both are local to the Package.

In VS the connection is successful and I'm able to execute the package.

Now, I'm trying to schedule it in SSMS using SQL Server Agent.

I first connected to SSIS in SSMS and imported the package from my file system into MSDB. Then, I created a new job with one step to execute the SSIS package. I see my package level connections, so everything appears ok. I also used Windows Authentication for this step as well

However when I try to run the I got login errors.

1. First, it said the login 'Domain\InstanceName$' failed.
2. So I switched the windows authentication in the step within the job to sql server authentication. Now I get another error (it's slightly different) stating the login 'Domain\UserName' failed.

How do I get this to work as a schedule?

Answer 1

The answer was creating a SSIS proxy and then running the job under that proxy account instead of the SQL Server Agent account.

First need to create a credential tied to a Windows domain account. Then create a proxy and tie that proxy to the credential. Last, apply that proxy to job instead of using the SQL Server Agent account.