SQL 2005: SSIS: Error using SQL Server credentials
I'm having difficulty getting an SSIS package to work using SQL Server Authentication. I receive the following error:
"Package Validation Error
Error at Export Audit data [OLE DB Source ]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "The server principal "username" is not able to access the database "foo" under the current security context."
The package works if I switch it back to Windows Authentication and I am able to sign into SSMS directly using the same SQL credentials .
I've tried setting DelayValidation to 'True' but it made no difference.
Any insight/suggestions would be greatly appreciated.