SQL Server - Linked server against Oracle

Download the Oracle Data Access Component for Oracle Client.

(http://www.oracle.com/technetwork/database/windows/downloads)
and install on the server where the SQL Server instance resides.

Update tnsnames.ora with the information about the Oracle database you will be connecting to.

net_service_name = 

  (DESCRIPTION = 

    (ADDRESS_LIST = 

      (ADDRESS = 

        (PROTOCOL = TCP)

        (HOST = host1)

        (PORT = 1630)

      )

    )

    (CONNECT_DATA = 

      (SERVICE_NAME = service_name)

    )

  )

You can find tnsnames.ora under $ORACLE_HOME/network/admin.

In some cases we have seen that the instance needs to be restarted for the ODAC to work and show up in the provider list of linked servers.

After that, just create the linked server and use Oracle Provider for OLE DB as provider and enter net_service_name from tnsnames.ora as data source, Oracle as product name and OraOLEDB.Oracle as provider string and user and password in the Security tab.

You must also check the Allow inprocess box under the properties of the provider.

Keep in mind that if you want to use any 32-bit client on the server such as SSMS/Datatools etc, you must also install 32-bit ODAC and copy tnsnames.ora into the installation path for it.

/Björn