This was a lot harder than I would have expected and took nearly 2 days of bashing my head against a wall to get it working, anyway here are the steps I ended up taking.

  1. Download the Oracle 11g R2 Client installers (Both the 32bit and 64bit versions).
  2. Run the 32bit installer
    1. For my installation path I specified c:\oracle
    2. For the software location I specified c:\oracle\product\11.2.0\client_32
    3. For the available product components I ticked the following
      • SQL Plus
      • Oracle Call Interface
      • Oracle Net
      • Oracle Connection Manager
      • 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
    4. When Net configuration runs click "Default configuration" and next through it
  3. Repeat the above steps for the 64bit installer with the following changes
    • Instead of _32 in your software location path put 64 e.g. c:\oracle\product\11.2.0\client_64
    • An error will most likely come up mid way through install saying "OracleMTSRecovaryService already exists", just click continue
  4. Modify the following registry keys
    • HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI\OracleSqlLib to orasql11.dll
    • HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTC\MTxOCI\OracleXaLib to oraclient11.dll
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI\OracleSqlLib to orasql11.dll
    • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI\OracleXaLib to oraclient11.dll
  5. In management studio expend the Linked Servers node, then expend the Providers node. Right click OraOledb.oracle and go in to the properties. Tick "Allow In Process" then click OK.
  6. Create or edit the following file C:\oracle\product\11.2.0\client_64\Network\Admin\tnsnames.ora with your database settings, it should look somthing like this...
    7. # tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools. 

GavsOracleDB =
   (DESCRIPTION =
      (ADDRESS_LIST =
         (ADDRESS =
            (PROTOCOL = TCP)
            (HOST = gavindraper.co.uk)
            (PORT = 1521)
         )
      )
      (CONNECT_DATA = (SERVICE_NAME = mytest.gavdraper))
   )
  7. Reboot the server
  8. You should then be able to create a linked server to Oracle using the Add Linked Server Wizard in SQL Server