[Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed

Question

I created a SSIS package to pull data from Oracle database to SQL server database. I have set up ODBC connection successfully on my local machine and on the server, I can connect to Oracle database using sqlplus on both environments. And my package run fine on my local machine. it also executed successfully when I run it from Package store on the server but the problem is when I run the package as sql job I got this error:

Description:

System.Data.Odbc.OdbcException: ERROR [HY000] [Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred ERROR [IM006] [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed ERROR [HY000] [Oracle][ODBC][Ora]ORA-12170: TNS:Connect timeout occurred at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection OdbcConnectionString constr OdbcEnvironmentHandle environmentHandle) at System.Data.Odbc.OdbcConnectionOpen..ctor(OdbcConnection outerConnection OdbcConnectionString connectionOptions) at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options Object poolGroupProviderInfo DbConnectionPool pool DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection DbConnectionPoolGroup poolGroup) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection DbConnectionFactory connectionFactory) at System.Data.Odbc.OdbcConnection.Open()

Does it make any senses?

Solution 1

ORA-12170 indicates that the TCP connection to the database listener failed.

If this is an error that happens all the time, then check your connection strings (particularly port numbers), firewalls and network ACLs.

If this is a transient error that doesn't always occur, or it the firewalls and network ACLs seen okay, then check for correct ARP behaviour. I've recently had this problem myself, and have blogged about it at http://distracted-it.blogspot.co.nz/2014/04/ora-12170-tnsconnect-timeout-resolved.html

Hope it helps.

Solution 2

ORA-12170 can result due to a lack of permissions. The Sql Server Agent Service Account must have access to the Oracle Client folder. You can test if this is the problem by logging in to the server as an administrator and trying to run the job manually, if it works this way, then it could be permissions. To change the permissions:

• Browse to the Oracle folder, e.g. C:\Oracle64
• Right Click the folder, click Properties, Security Tab, Click Edit, Click Add
• Change "Locations" to be the machine name that you are on.
• Enter the name “NT Service\SqlServerAgent” (this must be exactly right) and click Check Names.
• Click OK, Apply, OK to get back to the security tab, you should see that “SQLSERVERAGENT” has a tick for Read & Allow, Read, List folder contents .

Solution 3

Sometimes this happens when you are accesing to a DBLINK in one of your querys, and the database pointed on the dblink is down, so you have to check that too.