How to setup Oracle heterogeneous connection to SQL Server on Windows 64 bit

Oracle Heterogeneous Services (HS) is a component of Oracle database that allows querying of non-oracle databases from the Oracle database server. Oracle HS is integrated into Oracle database and non-oracle database is queried via database link.

1 - Create ODBC Data Source Name (SQLSRV1)

Oracle heterogeneous services require that ODBC Data Source Name be setup to connect to SQL Server database. The data source name needs to be setup on server that hosts the database. On RAC it needs to be setup on each database node.

2 - Create connection initialization file ($ORACLE_HOME/hs/admin/initSQLSRV1.ora)

The file needs to have following contents: 

HS_FDS_CONNECT_INFO = SQLSRV1
HS_FDS_TRACE_LEVEL = OFF

3 - Create TNS alias (SQLSRV1)

Add following entry to tnsnames.ora file: 

SQLSRV1 =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
      (CONNECT_DATA=(SID=SQLSRV1))
      (HS=OK)
    )

4 - Add new SID (SQLSRV1) to HS listener

Add following to listener.ora: 

# From 11g
    (SID_DESC=
      (SID_NAME=SQLSRV1)
      (ORACLE_HOME=E:\app\oracle\product\12.2.0\dbhome_1)
      (PROGRAM=dg4odbc)
    )

5 - Reload the listener

C:\> lsnrctl reload LISTENERHS

6 - Create SALES_SQL_SERVER database link


CREATE DATABASE LINK SALES_SQL_SERVER CONNECT TO "SALES" IDENTIFIED BY "secret" USING 'SQLSRV1';

7 - Access data in Sales SQL Server database from Oracle


select sum(ORDER_VAL) from orders@SALES_SQL_SERVER;

8 - What to do if connection does not work

To see how to trace DG2ODBC click here