For many of our tools we routinely need to pull data from an Oracle server.  In order to connect; SQL Server needs to understand how to talk to Oracle. This is true whether you are using a linked server to connect via Transact-SQL or if you are using SSIS.

Sites that have not connected to Oracle via a SQL Server Linked Server before may struggle with getting a linked server setup. Here is a summary for getting the Linked server communicating.

Microsoft OLE DB Driver for Oracle

Microsoft created a driver for Oracle many years ago, and it is slated for deprecation. If you see the “Microsoft OLE DB Driver for Oracle” you can try to setup your linked server to use it, but more than likely it will not work. This is a 32 bit driver Microsoft created to communicate with Oracle 6 / 7. Most sites are now on 10g or higher and require a new driver be installed.

Identification

Start by identifying some key information that will help you configure the system.

  • What version of Oracle is the site running? They should be able to tell you what version and release. Examples: 10g, 11.2 Release 6, 12 c, etc.
  • What version is your SQL Server environment running under?
    • You can run “SELECT @@VERSION” in a new query window
    • OD2
    • You can also check the Product label under the Server Properties in SSMS

If your SQL Server says “Intel X86” or “32-bit” you are going to need a 32 bit oracle driver. If it says “X64” or “64 bit” you will need the 64 bit driver. The driver you need will be based on the version of SQL server and not the Operating System. A 32 bit SQL Server running on a 64 bit OS will need the 32 bit Oracle driver.

Getting a Driver

In order to download a driver you will have to create a free oracle account. Once that is done…

32 Bit Drivers

You will want to get the ODAC xCopy download for the appropriate Oracle Version and SQL Server version.

For example: Oracle 11.2 Release 6 ODAC XCOPY for Windows 32 bit (x86)

ODAC Downloads 32 bit: http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html

The 32 bit drivers have a dependency on the Oracle Instant Client. This was specified in the ReadMe file in the ODAC download. Download the Instant Client corresponding to the Oracle and SQL versions you are working with. You only need the “Basic Lite” version.

For example: Version 11.2.0.4.0 Instant Client Package – Basic Lite

Instant Client Downloads 32 bit: http://www.oracle.com/technetwork/topics/winsoft-085727.html

64 Bit Drivers

You will want to get the ODAC xCopy download for the appropriate Oracle Version and SQL Server version.

For example: Oracle 11.2 Release 6 ODAC XCOPY for Windows 64 bit (x64)

ODAC Downloads 64 bit: http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html

The 64 bit drivers have a dependency on the Oracle Instant Client. This was specified in the ReadMe file in the ODAC download. Download the instant client corresponding to the Oracle and SQL versions you are working with. You only need the “Basic Lite” version.

For example: Version 11.2.0.4.0 Instant Client Package – Basic Lite

Instant Client Downloads 64 bit: http://www.oracle.com/technetwork/topics/winx64soft-089540.html

Installing the Driver & Instant Client

Note: the end of this process will require a reboot of the SQL Server machine and you need to plan accordingly.

Install the Instant Client

  1. Unzip the Instant Client download contents to a folder such as C:\oracle_InstantClient
  2. Add the folder the environment variable (covered below)
    Note: The installation instructions for the instant client can be found at the bottom of the download page.

Install the ODAC Driver

These are summarized notes. Review the ReadMe files associated with the downloads before continuing.

  1. Unzip the ODAC Driver to a folder
  2. Open the CMD console with administrator access
  3. Change the directory to the location of the download install.bat file
  4. Run the following:  “install.bat oledb c:\oracle odac64 true”
  5. Add 2 folders to the environment variables (covered below)

Add the Folders to the Environment Variables

  1. Search for Environment Variables
  2. On System Properties select Advanced
  3. Click on the Environment Variables button
  4. On the lower part of the screen scroll through the window until you find the “PATH” variable.
  5. Select the PATH variable and click Edit
  6. OD3
  7. You may want to paste the string to notepad. You want to add 3 folders to the beginning of the string: the instant client folder, the odac folder and the odac folder/Bin.  If you used the options above then the addition would be “C:\oracle_InstantClient;C:\oracle;C:\oracle\bin;”
  8. Once the folders are appended to the beginning of the PATH variable you need to restart the machine before they will be visible by services such as SQL Server.

Update the Driver Properties

Now we can confirm the driver is installed and set a couple properties on it.

  1. Open SSMS and connect to the SQL Server instance
  2. Open “Server Objects”
  3. Open “Linked Servers”
  4. Open “Providers”
  5. If everything above worked we should see the Oracle Provider for OLE DB driver listed
  6. OD4
  7. Right Click on it and choose Properties
  8. Enable “Allow inprocess” and “Dynamic Parameters” (I do not think we technically need these right now, but it will not hurt turning them on)
  9. Click “OK” to accept the change

Create the Linked Server

Now that we have the driver configured we can create the linked server.

  1. Right Click on “Linked Servers” and select “New Linked Server”
  2. On the General Page populate the fields as such:
    Linked Server: UserFriendlyLinkedServerName
    Server Type: Select “Other data source”
    Provider: select “Oracle Provider for OLEDB”
    Product Name: enter “Oracle”
    Data Source: //IPAddressOrOracleServerName:PORT/SERVICE_NAME
    * You should be able to get this information from an .ora file if one was provided.
    * The SERVICE_NAME above may also be called a SID
    Provider String: leave this empty
  3. Go to the “Security” page and fill in the “Be made using this security context” Remote Login and With Password information.

Test Connection

You should now be able to test the connection by right clicking on the linked server and browsing the catalog, or by querying your desired table using the 4 part naming.

Example: SELECT * FROM [LinkedServerName]..[Workspace].[TableOrView]

Trouble Shooting References

These articles had very good information that I pieced together to get this working. If the above does not work you may want to read through them for some additional insight.

Overview: https://blogs.msdn.microsoft.com/dbrowne/2013/10/02/creating-a-linked-server-for-oracle-in-64bit-sql-server/

More Details: https://blogs.msdn.microsoft.com/psssql/2015/07/31/how-to-get-up-and-running-with-oracle-and-linked-servers/

Bitness: https://powerbi.microsoft.com/en-us/blog/39-oraoledb-oracle-39-provider-is-not-registered/

Context:  http://sqlmag.com/sql-server/connecting-sql-server-and-oracle-using-linked-servers

 

Have you had to do something special to get this to work?

Let us know in the comments below.