Unfortunately, setting up a linked server to Oracle is not as easy as it should be. In fact, you’ll have to install some software on your server to make things work. I’ve put together a short guide on connecting to Oracle from SQL Server. The process doesn’t take very long but there are several hoops to jump through.

1. Install Oracle Database 10g Client Release 2
   1. Install using the InstantClient option
2. Install Oracle 10g Release 2 ODAC (64-bit, 32-bit)
   1. Select the Oracle Data Access Components option (not .NET!)
3. Edit TNSNAMES.ora
   1. <install directory>Product10.2.0ClientnetworkADMINTNSNAMES.ora (i.e. C:OracleProduct10.2.0ClientnetworkADMINTNSNAMES.ora)
   2. There is a very specific format to the network configuration file. Here is an example to get you started (just copy & paste multiple entries if necessary):

      DMDEV =
        (DESCRIPTION =
          (ADDRESS_LIST =
            (ADDRESS = (PROTOCOL = TCP)(HOST = SERVERNAME)(PORT = 1521))
          )
          (CONNECT_DATA =
            (SERVICE_NAME = FRIENDLYNAME)
          )
        )

      There are a couple of things you need to change:

      • HOST = SERVERNAME. The value SERVERNAME should be changed to reflect the actual address or hostname of the target system.
      • SERVICE_NAME = FRIENDLYNAME. FRIENDLYNAME is the name by which you refer to this actual connection.
4. REBOOT!
5. Configure provider in SQL Server
   

   OraOLEDB.Oracle Provider Menu Item

   1. Databases→DBName→Server Objects→Linked Servers→Providers→OraOLEDB.Oracle→Properties→Enable “Allow inprocess”

   

6. Create a linked server to the Oracle Database
   1. General
      1. Linked Server: A name of your choosing which you will use when querying using four-part naming conventions.
      2. Provider: Oracle Provider for OLE DB
      3. Product Name: “Oracle” is fine here
      4. Data Source: This should match the HOST you defined in TNSNAMES.ora
      

      Create a Linked Server

   2. Security
      1. Select Be made using this security context and supply the remote login and password
7. Query the linked server:

   SELECT TOP 10 * FROM LINKEDSERVERNAME..SYS.HELP