Freigeben über

Connectivity Issue with Oracle in x64 Environment “ORA-12154: TNS:could not resolve the connect identifier specified”

  • Artikel

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:

 

clip_image001

 

IF the required ODAC components are not installed you would get the following error when connecting to Oracle:

clip_image002

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

Comments

  • Anonymous
    February 02, 2010
    We ran into this trying to get Crystal reports and a custom app to reach the database. The short term fix that got us through our release weekend was to just move CR and our app out of that directory and into a place that the client could parse. Since the server isn't going to be hosting a variety of apps this may remain in place for a while. Don't reject the very simple options when faced with challenges just because they're easy. They could be perfectly adequate. Cris E St Paul, MN

  • Anonymous
    February 02, 2010
    The comment has been removed

  • Anonymous
    November 18, 2011
    Resolveu minha vida com a utilização do windows 7 x64. 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 <metalink.oracle.com/.../showdoc

  • Anonymous
    March 28, 2012
    uninstalled the 10.x clients and ODAC, installed both 64 and 32 bit 11.2 clients and it worked like a charm.  No ODAC is needed.  The size of 32 bit client is over a gig which seems ridiculous.

  • Anonymous
    April 10, 2012
    I copied the client program from the Program Files (x86) folder to the Program Files folder, launched the executable and the "TNS could not resolve connect identified" error is gone. Thanks for the tip!

  • Anonymous
    July 04, 2012
    I had the same error with the Oracle Client 10.2.04 64bit on Windows 7 64bit. I installed Oracle Client 10.2.04 64bit, then tried 10.2.01 32bit and although the Oracle-Connect- and ODBC-Test were successful (32+64bit), I couldn't connect via ODBC from MS Excel to the database. Then I tried to install the Oracle Client 11.2 64bit, but it crashed after clicking on "finish". Then I tried the Oracle Client 11.2 32bit and then was successful installing it, testing the connection and establishing a ODBC-connection from MS Excel. That doesn't make sense for me, but it works. Maybe this might help someone else... installing the Oracle Client 11.2 32bit on Windows 7 64bit.

  • Anonymous
    September 16, 2013
    The comment has been removed

  • Anonymous
    October 07, 2013
    I was facing the same issue onto server... We installed oracle 11g Client into the C:/ drive. But No luck ,still facing same issue... Is it mandatory to uninstall the previous version,which was 32bit oracle 10g installed on 64bit machine ? Can Anybody Suggest ?? Thanks SK..

  • Anonymous
    June 11, 2014
    Very simple solution find below, solutions-ever.blogspot.com/.../ora-06413.html

  • Anonymous
    October 07, 2015
    Just start Visual Studio with short cut name for program files on x86: "C:Progra~2Microsoft Visual Studio... stackoverflow.com/.../33007863