How To Install Odac 64 Bit

Connecting to an Oracle database with various Microsoft (MS) products can be a challenge.

Whether you are using Microsoft Power Pivot, SQL Server Analysis Services, Power BI , SharePoint Excel Services or the Change Data Capture Service for Oracle by Attunity it can be a confusing process to install and configure the required software to enable this functionality.

In this post I will share some of the lessons that I have learned as well as provide some practical advice so that you can have a smooth, hassle free expereince when taking on this task.

Lesson 1 Pick the right Oracle client

In order to connect to an Oracle database using a MS product you must installl an Oracle client. There are several options available.

Oracle Data Access Components (ODAC)

Use this if you are trying to connect to Oracle with applications like Excel or Power BI. Do not use this if you are using server software such as SQL Server or Change Data Capture Service for Oracle by Attunity.

32 bit Oracle Data Access Components with Oracle Developer Tools for Visual Studio - Use this if you only have applications like Visual Studio, Excel or Power Pivot on your computer. Do not use this if you have any server software such as SQL Server installed on this computer. This is for 32 bit applications so be sure to check if Excel is 32 bit or 64 bit.

64 bit Oracle Data Access Components - Use this if you only have 64 bit applications like Excel or Power BI. Do not use this if you have any server software such as SQL Server installed on this computer. As the name implies this is for 64 bit applications so be sure to check your applications to confirm they are running in 64 bit mode.

Oracle Database Client for Microsoft Windows (32-bit) - This is required if you are doing local troubleshooting of various MS objects such as SSIS, SSRS or SSAS on the server. You may be tempted to use 32-bit ODAC however since you are working on the server you will already have a 64-bit version of the Oracle client installed so you need to have a separate Oracle home for the 32-bit version of the client, the simplest way to do this is to use this client rather than the 32-bit ODAC.

Oracle Database Client for Microsoft Windows (x64) - This is required for server side software such SQL Server Analysis Services, SQL Server Integration Services or SQL Server Reporting Services. This software runs in 64 bit mode so if you do not install this on the server where the software is running you will get the "The OraOLEDB.Oracle.1 provider is not registered on the local machine" error when you execute your process on the server.

Lesson 2 Install in the right order

If you want to do local troubleshooting/development of SSIS, SSAS or SSRS objects on the server you will need to install both the 32 bit and 64 bit versions of the Oracle client.

32 bit first

First you will need to install the Oracle Database Client for Microsoft Windows (32-bit). When prompted to select the installation type be sure to select Administrator.

It may be tempting to select something with a smaller footprint or to customize your install and only pick the components you need however in my experience this typically causes more grief than its worth because different MS products require different components and knowning exactly which each requires is very difficult and time consuming, its best to install everything.

Also be sure to install this client into a folder that clearly identifies this as the 32 bit software.

This will place to the 32 bit client in a seperate Oracle Home from the 64 bit client.

Once the client has been installed reboot the server. This is not required but in my experience when install Oracle clients on Windows its best to reboot often.

64 bit second

Once you have installed 32 bit and confirmed that it is running as expected install the 64 bit client.

As before select Administrator when prompted to select the installation type.

This time when you specify the installation location be sure to put the software into a different folder that clearly denotes which version of the Oracle client this is. I typically use the following pattern for the Oracle base.

C:\Oracle Client\32bit

C:\Oracle Client\64bit

Once the Oracle client has been installed reboot the server and confirm that it is up and running as expected.

Lesson 3 TNSNAMES and SQLNET

In order to make the management of the Oracle connection details easier I highly recommend putting the TNSNAME.ORA and SQLNET.ORA into a single spot that can be used by all Oracle clients.

Create a folder on the D: drive, or whatever drive you have called TNS Names. D:\TNS Names
Add the sqlnet.ora and tnsnames.ora files to this folder. You can copy these files out of either one of your Oracle client installs, just do a search for either one of these files and copy and paste them into the new folder.

3. Edit the system environment variables.

4. Add a new System Variable called TNS_ADMIN with the value D:\TNS Names

5. Reboot the server.

Tips

Tip 1 - EZCONNECT

Add the paramerter EZCONNECT to the sqlnet.ora file. This is required for Change Data Capture Service for Oracle by Attunity

Tip 2 - Permissions

Grant the account(s) running the various services such as SSAS, SSIS and SSRS access to the Oracle client 64 bit. Typically I give the account full control of the 64 bit Oracle base folder so in my example above that would be C:\Oracle Client\64bit. For developers I typically grant the AD group the developers are part of access to the 32 bit Oracle base folder.

That concludes this article on how to connect MS products to Oracle data sources. Hopefully you have found this to be another practical post. Until next time.

Anthony