We are having an issue regarding the functionality of an SSIS package.
We have a SQL task that uses the OPENQUERY statement utilizing a linked server (DataSource = IBM teradata).
When we test it in BIDS it runs without a hitch - because it is using the credentials associated with the user executing the package. When we SCHEDULE the process it fails because it executes the package with the credentials of the domain user account of the SQL server.
Because we are a financial institution, our information security division is rightfully hesitant to assign rights to the domain service account. Their thought process is that they need a way to control access on a per user basis for the server because the many different users creating scheduled packages have varied access rights. I agree.
Is there a way to get a scheduled job to "emulate" or 'impersonate" a particular user, or pass users credentials with and OPENQUERY statement?
Any ideas to help circumvent this issue are welcome.