SQL Server 2008R2 Linked Server to Oracle 11.2.0.2 Instance

My notes on doing this particular setup using SQL Server 2008R2 and Oracle 11.2.0.2 (64bit versions) on Windows Server 2008R2 64bit Enterprise edition.

Install Oracle 11.2.02 64 bit client

Install the oracle client on the SQL Server machine where you want to add the linked server. Install the following components only using the custom installation option.

• Oracle Database Utilities
• SQL*Plus
• Oracle Net
• Oracle Provider for OLE DB
• Oracle Data Provider for .NET (I dont think this is needed, but I always install it out of habit!)

Reboot the SQL Server box

Reboot the server so that SQL Server 2008R2 can pick up the installed Oracle provider. When the server comes back up, you should see the new oracle provider appear in the linked server providers section in SQL Server Management Studio.

Configure Oracle Link Provider Properties

Right click on the OraOLEDB.Oracle provider and select properties. Check the following options and save changes.

• Dynamic parameter
• Allow inprocess

Configure TNS Names

At this point, you need to configure TNS entries for connecting to your Oracle instance. The easiest thing to do is take an existing tnsnames.ora file from a working server and copy it to the "network\admin" directory in your root oracle install path. ( in my case, my TNS names file is in the path "c:\app\product\11.2.0\client_1\network\admin\tnsnames.org")

Create Oracle Linked Server

Go back into SQL Server Management Studio and browse to the Linked Servers folder. Right click that folder and select "New Linked Server...".

In the general tab...

• Type in the name of the linked server (whatever you want) into the "Linked server:" field
• Select "Oracle Provider for OLE DB" as the Provider
• Put in any value you want for the Product name - something like "Oracle" will do.
• Set the Data source: to the TNS name of the connection you want to use from your TNS file.
• Leave the provider string empty

In the security tab...

• Select the last radio button "Be made using this security context"
• Enter the oracle account username/password in the fields under the checked radio button

...and click "OK" to finish up.