ORA-12154 : TNS Could not resolve the connect Identifier

Accessing an Oracle DB over a 64 bit system was a pain for many SQL Server DBAs.
The purpose of this document is to explain how to fix issues running a SSIS package, Linked Server and SQL Agent which are using Oracle OLEDB under 64bit SQL Server 2008.

The Following solution is based on Oracle 11g version. You can download Oracle 11g client from http://www.oracle.com/technology/software/products/database/index.html

You will need 32bit and 64bit both Oracle clients.

Oracle Client & OLEDB component Installation steps in 64bit Windows:

1. If you have old Oracle clients, copy tnsnames.ora and sqlnet.ora files for backup. 
   (They are under %ORACLE_HOME%/network/ADMIN/.)
2. Uninstall & Delete previous version of Oracle client include installed directory. 
   If you have or ever installed Oracle client, uninstall them and reboot the server. Then you can delete old Oracle directory. If you don’t reboot, you cannot delete them. 
3. Run setup.exe of Oracle 11g Client in 64bit SQL Server running machine.
   You have to install 32bit first.
4. Select “Custom” for OLEDB component installation in "Select Installation Type" step. 
   

   

5. Properly select Oracle Base and Path. 
   

   

6. You must select “Oracle Windows Interfaces 11.x.x” component for OLEDB in “Available Product Components” step. 
   

   

7. After installation, restore TNSNAMES.ORA backup file under the new location of %ORACLE_HOME%/network/admin folder.
8. Repeat above step #4-#7 with 64bit Oracle 11g Client setup.  
   Oracle "Base directory" is same as the 32bit, but Path is supposed to be different from the previous one in above step #5.  
   i.e) If Oracle 32bit Client Path was C:/Oracle/product/11.1/client_32, 
     Then 64bit Client Path is under C:/Oracle/product/11.1/client_64.
9. You will see an error message “OracleMTSRecoveryService already exists” as below screenshot during the 64bit client installation. You can “Ignore” it.

   

10. Modify the system registry settings as follows. And then reboot server! 
    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI     and 
    HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI 
    are correct: 
    a. OracleOciLib = oci.dll 
    b. OracleSqlLib = orasql11.dll   (old: SQLLib80.dll) 
    c. OracleXaLib = oraclient11.dll  (old: xa80.dll) 
11. Test sqlplus.exe or tnsping.exe in command console.

Now you can access Oracle OLEDB provider in BIDS (Business Intelligence Development Studio) & SSMS both places. What I tested was:

• Design SSIS package with Oracle data source (OraOLEDB) in BIDS
• SSIS package debug & run in BIDS
• C# & VB sample code test to access OraOLEDB.Oracle provider in VisualStudio
• Add the developed SSIS package & run on SSMS
• Add SSIS package job in SQL Agent & run on SSMS
• Add Linked Server to access Oracle Server through Oracle OLEDB in SSMS

One thing you have to keep in mind is that BIDS is based on Visual Studio 32bit modules. When you design & test a new SSIS package on it, actually it’s using 32bit Oracle OLEDB modules. 
After the SSIS package added in 64bit SQL Server, SQL Agent is using 64bit Microsoft & Oracle OLEDB modules.

NOTE1:

While you are developing or testing SSIS package, you might see the following ignorable messages:  “Warning: Cannot retrieve the column code page info from OLE DB…”.

NOTE2:

When you add new SSIS package in SQL server, you have to select “Rely on server storage and roles for access control” in Package Protection Level as below screenshot to avoid XML password encryption error. For some reason, saved Oracle password couldn’t be decrypted by SSIS service on my test.

NOTE3:

You might see the following error “Cannot obtain the required interface (“IID_IDBSchemaRowset”) from OLE DB Provider “OraOLEDB.Oracle” for linked server XXX (Error:7399)” in an Oracle Linked Servers as follows.

To resolve this, open properties of OraOLEDB.Oracle provider, and set “Enable” for “Allow inprocess” option as follows.

Instance>>Sever Objects>>Linked Servers>>Providers>>OraOLEDB.Oracle