MSSQL – Creating an Oracle Linked Server

  • Install the Oracle client software by running D:\Install-Staging\Oracle-Client-sw-12102\000\DB_106896.msi
  • Copy the tnsnames.ora from D:\Install-Staging\Oracle-Client-sw-12102 to C:\APPS\ORACLE\PRODUCT\12.1.0\CLIENT_64\network\admin
  • Restart the SQL Server
  • Under Server Objects-Linked Servers Expand Providers select properties for OraOLEDB.Oracle and tick Allow inprocess.
  • In SQL Server run the following:
    sp_addlinkedserver @server = ‘DBIBUxx’, @srvproduct = ‘Oracle’, @provider = ‘OraOLEDB.Oracle’, @datasrc = ‘DBIBUxx’
    go
    EXEC sp_addlinkedsrvlogin ‘DBIBUxx’, ‘FALSE’, NULL, ‘DBIBxx_026_USER’, ‘passxxxx’
    go
  • That should be it, you can verify by selecting Test connection or by expanding catalogs and viewing the tables and doing a select from them.