How to install Oracle OLE drivers for use in SQL Server 2008 R2 x64

Solution 1:

After hours of research, I managed to cobble together some working instructions for Oracle 11g R2. As it turns out, you will likely need both the 32 and 64 bit clients installed to have things working in BIDS/Visual Studio/SSMS. I may have installed more Oracle components than I needed, but here's what worked for me:

1. Download 32 and 64-bit clients from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html (click "see all" to see different versions, otherwise, you'll be downloading the complete Oracle program. Your downloads should be about 600 meg each)
2. Run 32-Bit install on the SQL Server. Select "Custom"
3. For Oracle Base, enter "C:\Oracle"
4. For "Software Location", change to C:\Oracle\product\11.2.0\client_32
5. Choose the following components:
   • SQL*Plus
   • Oracle Call Interface (OCI)
   • Oracle Net
   • Oracle Services for Microsoft Transaction Server
   • Oracle Administration Assistant for Windows
   • Oracle Provider for OLE DB
   • Oracle Data Provider for .NET
   • Oracle Providers for ASP.NET
6. Repeat above steps with the 64-bit installer. HOWEVER, change the "Software Location" to C:\Oracle\product\11.2.0\client_64
7. I got a memory error at this point, but chose to ignore it since I know there is enough
8. Ignore the error "OracleMTSRecoveryService already exists"

Now that the install is done, just need to tweak a few things.

9. Make registry changes to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI
   • OracleOciLib should be oci.dll
   • OracleSqlLib should be orasql11.dll
   • OracleXaLib should be oraclient11.dll
10. Make same registry changes to HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI
11. Create or copy a new tnsnames.ora in C:\Oracle\product\11.2.0\client_32\network\admin
12. Create or copy a new tnsnames.ora in C:\Oracle\product\11.2.0\client_64\network\admin
13. Reboot!
14. You should now see OraOLEDB.Oracle as a provider under Linked Servers\Providers in SSMS
15. Right-click this provider, then Properties, then tick the box for "Allow Inprocess"
16. You can now create linked server through the GUI or T-SQL

Good luck!