Problem:
I have created an SSIS job in SQL Server 2012. Initially it runs fine with the SQL Server Agent account, which is NT SERVICE\SQLAgent$SQL2012.
For the package, I have two connection managers connecting to AdventureWorks2008 and AdventureWorksDW2008, respectively, using the Windows Authentication.
The job fails:
===================================================
Executed as user: NT Service\SQLAgent$SQL2012.
Code: 0xC0202009
Source: Dimcustomer Connection manager "AdventureWorks2008" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred.
Error code: 0x80040E4D. An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E4D Description: "Login failed for user 'NT SERVICE\SQLAgent$SQL2012'.
End Error
........................
error code: 0xC0202009 Description: "SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. ................
The AcquireConnection method call to the connection manager "AdventureWorksDW2008" failed with error code 0xC0202009.
..................
The package execution failed. The step failed.
==================================================
Reason:
The SQL Server 2012 Agent Service Account, which is the virtual account - NT SERVICE\SQLAgent$SQL2012, does not have permission to connect the AdventureWorks2008 and the AdventureWorksDW2008 databases.
Solution:
Create a credential from the windows login account (the one connects to the AdventureWorks2008 and the AdventureWorksDW2008 databases), then create a proxy account from the credential, and run the package under the proxy account.
