Accessing Oracle database via Linked Server through 64bit SQL Server

How to resolve OraOLEDB issue in 64bit SQL Server SSIS or SQL Agent

0 1,812
Reading Time: 2 minutes

Installing Oracle 11g 32bit & 64bit clients would be a resolution of the famous 64bit OraOLEDB issue.
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.
The Following solution is based on Oracle 11g and above versions. You can download Oracle 11g client and above versions from http://www.oracle.com/technology/software/products/database/index.html

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 purpose. (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 and also reset below registry keys to their default values :
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
    • HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI
      • OracleOciLib = oci_temp.dll
      • OracleSqlLib = SQLLib80.dll
      • OracleXaLib = xa80.dll
  3. Put setup.exe file(s) on short file name folder path!
  4. Run setup.exe of Oracle 11g Client in 32bit SQL Server running machine. You have to install 32bit first (In Oracle Client Setup 11.2 is a BUG so you have to install 32bit first !!).
  5. Select Custom under Select Installation Type and choose below products :
    • SQL Plus
    • Oracle Call Interface
    • Oracle Net
    • Oracle Connection Manager
    • Oracle ODBC Driver
    • Oracle Services For Microsoft Transaction Server
    • Oracle Administration Assistant For Windows
    • Oracle Counters for Windows Performance Monitor
    • Oracle Provider for OLE DB
    • Oracle Data Provider for .Net
    • Oracle Providers for ASP.Net
  6. After installation, restore TNSNAMES.ORA backup file under the new location of %ORACLE_HOME%/network/admin folder.
  7. Now Restart the server and Repeat steps 4 to 5 with 64bit Oracle 11g Client setup file.
    Oracle Base directory is same as the 32bit, but Path is supposed to be different from the previous one mentioned in step 5.
    i.e) If Oracle 32bit Client path was C:/Oracle/product/11.1/client_32, Then 64bit Client path could be placed under C:/Oracle/product/11.1/client_64.
  8. You will see an error message OracleMTSRecoveryService already exists during the 64bit client installation. You can Ignore it.
  9. Restart the server
  10. Modify the system registry settings as follows. And then reboot server! (You don’t need to apply below step for Oracle 18c drivers)
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
    • HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI
      • OracleOciLib =dll
      • OracleSqlLib = dll (old: SQLLib80.dll)
      • OracleXaLib = dll (old: xa80.dll)
  11. Test sqlplus.exe or tnsping.exe in command console.
  12. Install Oracle Release 2 64-bit ODAC software (Client path could be C:/Oracle/product/11.1/client_odac64)
  13. Restart the server
  14. Open SQL Server Management Studio under Providers node inside Linked Server, Set the Allow InProcess option for one of your providers in the list (Right Click Provider Options, and then click to select the Allow InProcess check box, this will apply for all other providers)
  15. After creating a Linked Server, if you encountered with this error : ORA-12154 or SQL Error 7303 you should remove Allow InProcess checkbox option

Congratulations! Now you can access Oracle OLEDB & ODBC provider in BIDS (Business Intelligence Development Studio) & SSMS both places.

PS:
In SSIS for using new 32 & 64 bit drivers of Oracle Provider for OLED DB instead of deprecated 32 bit driver of Microsoft OLE DB provider for oracle, install ODAC 32, after installing all above drivers

Author Profile

Database administration is an exciting job combining knowledge, art and experience; you must live with it to love it, despite all the difficulties.
Currently I'm working as a Microsoft SQL Server DBA and BI consultant.

Author Profile

I make SQL Server faster and more reliable. Database administration isn’t about passing a certified exam or about pointing and clicking your way through a crisis. Database administration is about applying the right solution at the right time, avoiding risk, and making robust choices that get you home each night in time for dinner with your family.

Leave A Reply

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More