Can’t connect to Oracle from SSIS/DTS packages or Linked Server on x64 environment
Today I came across a very interesting issue where my customer was not able to connect to Oracle 10G from legacy DTS packages or through linked server from SQL server 2005 32bit version installed on Windows 2003 x64 machine.
Environment details:-
Windows 2003 ENT x64 OS, SQL server 2005 ENT SP3 32bit , Oracle 10G client drivers 32bit
Error Messages:-
ORA-12154: TNS:could not resolve the connect identifier specified.
ORA-06413: Connection not open.
Resolution:-
This is a very unique issue and is related to the bug available in the oracle 10g client drivers but before i jump to the resolution I must say that we still need do the basic troubleshooting regarding oracle connectivity like creating a UDL file or using the ODBC utility to test the oracle connectivity and rule out any mismatched/or faulty oracle client driver installation issue.
In my case I could connect to the remote Oracle database using a UDL file and from the ODBC 32bit utility, it is only from legacy DTS packages and through the linked server from SQL server 2005 I was not able to connect to the Oracle database.
While I didn’t have lot of information on why the error messages are coming up while connecting Oracle database from SQL server, I went to the Oracle support site to see if there were any known issues.
There I found this link which to my luck has the resolution to this issue.
https://forums.oracle.com/forums/thread.jspa?messageID=1943221
According to oracle forum mentioned in the above link there is bug in oracle 10g client driver which is causing this issue.
There is a networking bug in the oracle 10g client driver where the networking layer is unable to parse program locations that contain parenthesis in the path to the executable which is attempting to connect to Oracle.
So in my case the location of SQL server 2005 installation is under “C:\Program Files (x86)\..." and as we can see that the path of the SQL binaries has parenthesis , this would cause oracle client driver to fail to connect to oracle database.
Currently the following patches have been released on Windows by Oracle to fix this issue:
Windows 32-bit
9.2.0.7.0 Patch 6: Apply Patch 4928723 or later
10.2.0.1.0 Patch 4: Apply Patch 4923768 or later
10.2.0.2.0 Patch 5: Apply Patch 5383042 or later
10.2.0.3.0: Apply Patch 5337014 or later
Windows 64-bit AMD64 and INTEL EM64T
10.2.0.1.0 Patch 4: Apply Patch 4923787 or later
10.2.0.2.0 Patch 5: Apply Patch 5388871 or later
10.2.0.3.0: Apply Patch 5337014 or later
But while I was on call with the Oracle support professional to get a confirmation regarding this bug, he suggested me to install the following version of oracle 10g client drivers.
10.2.0.4 ( latest Oracle 10G client driver version)
After installing 10.2.0.4 version of Oracle client 10G driver, we were able to connect to oracle from legacy DTS packages and also from the linked servers using SQL server 2005.
Hope this help!!!