Connectivity Issue with Oracle in x64 Environment “ORA-12154: TNS:could not resolve the connect identifier specified”
When you try to develop an SSIS package in x64 Environment in BIDS that connects to a Oracle Database, you will invariably run into connectivity issues if the installed Oracle Client tools version is 10.2.0.1 (x86). You will obviously need to install the 32-BIT version of Oracle Client to communicate with BIDS which is a 32-BIT application!
Subject :
ORA-12154 or ORA-6413 Running 32-bit Oracle Software on 64-bit Windows OS.
Applies to:
Oracle Net Services - Version: 8.1.7.0 to 10.2.0.1
Oracle Data Provider for .NET - Version: 8.1.7.0 to 10.2.0.1
Oracle Objects for OLE - Version: 8.1.7.0 to 10.2.0.1
Oracle Provider for OLE DB - Version: 8.1.7.0 to 10.2.0.1
Oracle ODBC Driver - Version: 8.1.7.0 to 10.2.0.1
Microsoft Windows XP (64-bit Itanium)
Microsoft Windows Server 2003 (64-bit Itanium)
Microsoft Windows XP (64-bit AMD64 and Intel EM64T)
Microsoft Windows Server 2003 (64-bit AMD64 and Intel EM64T)
Symptoms:
You are attempting to connect to the Oracle database from a Windows platform using one of the following programmatic interfaces
· ODBC
· OLEDB
· OO4O
· ODP.NET
After installing 32-bit Oracle client software on a 64-bit Windows operating system (OS) you receive one of the following errors:
ORA-12154: TNS:could not resolve the connect identifier specified
or
ORA-6413: Connection not open.
Cause:
64-bit Microsoft OS's install 32-bit applications into the following location
"C:\Program Files (x86)\..."
rather than the typical location of
"C:\Program Files\..."
This causes an existing networking bug to occur 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.
The following bug has been filed to correct this behavior:
Bug 3807408 < https://metalink.oracle.com/metalink/plsql/showdoc?db=Bug&id=3807408 >
NOTE: You will be able to view this bug details in the Metalinks site only if you have a Metalink login ID which comes with a support agreement with Oracle
Additional Information:
The reason you receive an ORA-12154 vs. an ORA-6413 is generally due to which programmatic interface you have chosen to use to connect to Oracle.
The ORA-12154 is the typical error seen when connecting with up-to-date interfaces using the latest version of the Oracle Call Interface (OCI):
· Oracle ODBC Driver
· Oracle Provider for OLE DB
· Oracle Objects for OLE
· Oracle Data Provider for .NET (ODP.NET)
· Microsoft's .NET Managed Provider for Oracle
The ORA-6413 is typical of using older interfaces which make legacy API calls such as Oracle's OCI Version 7 API:
· Microsoft ODBC Driver for Oracle
· Microsoft OLE DB Provider for Oracle
Solution:
To resolve this problem try one of the following solutions:
The Best possible solution is to upgrade to Oracle 11G Release 1 version of Client tools (Build: 11.1.0.6.0) rather than applying the series of patches over the 10G client which may not produce desired results. The 11G version of the Client tools are tested and confirmed to include a FIX for the above error. If it is absolutely necessary to stick to the 10G version of the client (it shouldn't be), refer to the following:
For 32-BIT Oracle Client:
1. Install 10.2.0.2 upgrade - You need to select the correct Oracle Home path of previous 10.2.0.1 install.
2. After that you need to install a patch (5383042) for 10.2.0.2 client tools.
For 64-BIT Oracle Client:
1. Install 64bit 10.2.02 upgrade - Select the correct Oracle Home.
2. Install 64bit 10.2.03 upgrade - Select the correct Oracle Home.
However, It is strongly recommended to upgrade to Oracle 11G Release 1 version of Client tools (Build: 11.1.0.6.0) rather than applying the series of patches over the 10G client which may not produce desired results. The 11G version of the Client tools are tested and confirmed to include a FIX for the above error.
Moreover, if you need to patch the Oracle 10G drivers as above, it is recommended to engage Oracle support to get the correct upgrade packages and have them applied successfully.
Important: Oracle InstantClient will not work. I have seen people across trying to make this work after installing InstantClient. Only “Administrator” or a “Custom” installation with all the required modules selected would work.
NOTE: This will allow you to create and run the package successfully from BIDS, but not as a Sql Job (in an x64 version of Sql Server). The reason being x64 version of Sql Server will always invoke x64 version of the DTEXEC.exe to run a job which will cause it to fail. We can confirm that by running the job successfully from command line using the 32-BIT DTEXEC.exe. (Running it from the C:\Program Files (x86) directory). So, to workaround this problem, we can choose one of the following solutions:
Solution 1:
Run the Job under the cmdExec subsystem and modify the command line so that the 32-BIT DTEXEC.exe is invoked to execute the job.
Solution 2:
Install the 64-BIT Oracle Client tools side by side with the 32-BIT one (Make sure you either apply the patch if you install 10G version or STRONGLY recommended to use the 11G Release 1 version)
Solution 3:
Modify the following Registry key to run the job under the Integration Services subsystem but to invoke the 32-BIT DTEXEC.exe to execute the package:
HKLM ->SOFTWARE->MICROSOFT->MSDTS->SETUP->DTSPATH. Change the value of the DEFAULT key to point to the 32-BIT DTEXEC.exe. Typically, under Default installation, that path will be C:\Program Files (x86)\Microsoft SQL Server\90\DTS\
NOTE: You will need to restart the Sql Server Agent Service for the registry change to take effect.
Important: As a side note, Microsoft OLE DB provider for Oracle acts only as a wrapper over Oracle’s native data access modules. Under the hoods, it uses the ODAC (Oracle Data Access components) API calls to connect to Oracle. So make sure that while installing Oracle Client tools you choose the appropriate ODAC components to install for Microsoft Oracle OLE DB provider to work correctly. If required, you should do a custom installation of Oracle Client tools and select the appropriate Windows Interface as in the figure below:
IF the required ODAC components are not installed you would get the following error when connecting to Oracle:
Remember, this error is not specific to SSIS, if ODAC components are not installed, any client app making connection to Oracle using Microsoft OLE DB provider would encounter this error.
Author : Debarchan(MSFT) , SQL Developer Engineer , Microsoft
Reviewed by : Smat (MSFT) , SQL Escalation Services, Microsoft