SQL OLEDB Connection with provided windows service account credentials
Posted on 2013-05-14
SQL Server 2008
I need to provide a specific windows service acccount to make a connection to a SQL OLEDB source. The source SQL Server is 2008 and set at Integrated Security. The service account has all the proper read credentials as I can "run as" SSMS and see the table we need to pull.
Package protection level is set to "Encrypt Sensitive with Password"
The problem is that when the package runs as the SQL Proxy, the agent is attempting to make the connection using the Proxy credentials, not the saved service account credentials.
We cannot have a service account with credentials to multiple datasources. This seems like it should be so easy, yet I'm having a bugger of a time trying to source data from a SQL Server.