SQL Service Account permission considerations

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: