Recently ive had to do a lot of work concerning the setup and configuration of service accounts for SQL 2005 and 2008. An interesting issue surfaced when trying to get SSIS to connect to an Oracle server using the Microsoft OLEDB provider for Oracle (MSDORA.1).
Our DEV and TEST environments were working fine, but I kept on getting the “ORA-12170: TNS: Connect timeout occurred” error in the PROD environment. The error indicated that the Oracle client was successfully installed and that SSIS was able to talk to it but that something else was wrong. After a few days were spent checking logs, firewalls, re-installing the Oracle clients etc we stumbled upon a breakthrough – When an administrator account was used to execute the package in PROD it worked fine!
The service account we had setup as a credential/proxy to run the job step had all the right permissions as far as sql server was concerned.. however the issue proved to be that the service account had no rights on the machine itself. Adding the account as a user on the machine running the SSIS package solved the issue.