Introduction
Oracle's Heterogeneous Services lets you query non-Oracle databases directly from an Oracle database through a database link, without a separate ETL pipeline. We covered the MySQL version of this setup in an earlier post; this guide covers connecting Oracle to Microsoft SQL Server specifically, which uses Microsoft's own ODBC driver and command-line tooling rather than MySQL's. While the overall Heterogeneous Services architecture is the same, the driver installation, connectivity testing tools, and a couple of configuration parameters differ enough that it's worth its own walkthrough.
Prerequisites
- Network connectivity established between the source and target environments. Verify with
pingortelnet. - The Microsoft SQL Server ODBC drivers and tools installed on the Oracle DB server:
msodbcsql17, the Microsoft ODBC Driver 17 for SQL Servermssql-tools, which providessqlcmd, Microsoft's native command-line query toolunixODBC-devel, the development headers unixODBC needs to build against the Microsoft driver
Environment Used in This Guide
| ## | Source | Target |
|---|---|---|
| Database | Oracle 19c | MSSQL Server |
| IP | 192.168.80.33 | 10.50.19.5 |
| DB Name | ORCLPRD | MSSQLDB |
| Port | 1521 | 1433 |
| Username | sys | ViewSQL |
Step 1: Install the Microsoft ODBC Driver and Tools
Unlike the MySQL connector, Microsoft's driver requires accepting a EULA during installation, and you'll typically install three separate packages rather than one. Run these as root.
[root@oracle01 ~]# yum install -y msodbcsql17
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package msodbcsql17.x86_64 0:17.9.1.1-1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
Do you accept the license terms? (Enter YES or NO)
YES
Installing : msodbcsql17-17.9.1.1-1.x86_64 1/1
Verifying : msodbcsql17-17.9.1.1-1.x86_64 1/1
Installed:
msodbcsql17.x86_64 0:17.9.1.1-1
Complete!
[root@oracle01 ~]# yum install -y mssql-tools
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
---> Package mssql-tools.x86_64 0:17.9.1.1-1 will be installed
--> Finished Dependency Resolution
Do you accept the license terms? (Enter YES or NO)
YES
Installing : mssql-tools-17.9.1.1-1.x86_64 1/1
Verifying : mssql-tools-17.9.1.1-1.x86_64 1/1
Installed:
mssql-tools.x86_64 0:17.9.1.1-1
Complete!
[root@oracle01 ~]# yum install -y unixODBC-devel
Loaded plugins: langpacks, ulninfo
Resolving Dependencies
--> Running transaction check
Installing : unixODBC-devel-2.3.7-1.rh.x86_64 2/3
Cleanup : unixODBC-2.3.1-14.0.1.el7.x86_64 3/3
Verifying : unixODBC-devel-2.3.7-1.rh.x86_64 1/3
Verifying : unixODBC-2.3.7-1.rh.x86_64 2/3
Verifying : unixODBC-2.3.1-14.0.1.el7.x86_64 3/3
Installed:
unixODBC-devel.x86_64 0:2.3.7-1.rh
Dependency Updated:
unixODBC.x86_64 0:2.3.7-1.rh
Complete!
[root@oracle01 ~]#
mssql-tools is worth calling out specifically, it installs sqlcmd, which we'll use in Step 6 as an independent way to verify connectivity using Microsoft's own client rather than relying solely on generic ODBC tools.
Step 2: Create the ODBC Data Source
Create or edit /etc/odbc.ini to define the SQL Server data source, pointing at the Microsoft driver's shared library rather than a generic ODBC one. If a file already exists, back it up first before adding these entries.
[oracle@oracle01 ~]$ cat /etc/odbc.ini
[MSSQL]
Description = MSSQL
Driver = /usr/lib64/libmsodbcsql-17.so
Server = 10.50.19.5
User = ViewSQL
Password = YourP@ssw0rd
Port = 1433
Database = MSSQLDB
[oracle@oracle01 ~]$
Step 3: Create the Heterogeneous Services Init File
Navigate to $ORACLE_HOME/hs/admin and create a file matching your data source name, following the initDATASOURCENAME.ora naming pattern. A couple of parameters here are worth noting specifically for the SQL Server driver: HS_FDS_TRACE_FILE_NAME gives the Heterogeneous Services agent a dedicated trace log, useful when troubleshooting connection issues with the Microsoft driver, and the explicit ODBCINI environment variable ensures the agent reads the correct odbc.ini file rather than a default system path.
[oracle@oracle01 admin]$ pwd
/u01/app/oracle/product/19.0.0/dbhome_1/hs/admin
[oracle@oracle01 admin]$ cat initMSSQL.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
# HS init parameters
HS_FDS_CONNECT_INFO = MSSQL
HS_FDS_TRACE_LEVEL = user
HS_FDS_SHAREABLE_NAME = /usr/lib64/libmsodbcsql-17.so
HS_FDS_TRACE_FILE_NAME = /tmp/ora_hs_trace.log
HS_NLS_NCHAR = UCS2
# ODBC specific environment variables
set ODBCINI=/etc/odbc.ini
# Environment variables required for the non-Oracle system
[oracle@oracle01 admin]
Step 4: Configure the Listener and TNS Entries
Add a new SID_DESC block to listener.ora using the dg4odbc program, routing this service through the heterogeneous gateway. The LD_LIBRARY_PATH here needs to include Microsoft's driver directory specifically, which differs from the MySQL connector's install location. Then add the matching TNS entry with (HS=OK) in tnsnames.ora.
[oracle@oracle01 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle01)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCLPRD)
(ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
(SID_NAME = ORCLPRD)
)
(SID_DESC=
(SID_NAME=MSSQL)
(ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1)
(PROGRAM=dg4odbc)
(ENV="LD_LIBRARY_PATH=/opt/microsoft/msodbcsql17/lib64:/u01/app/oracle/product/19.0.0/dbhome_1/lib")
)
)
[oracle@oracle01 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCLPRD =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracle01)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLPRD)
)
)
MSSQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST=192.168.80.33)(PORT=1521)) # IP Oracle Database
(CONNECT_DATA=(SID=MSSQL))
(HS=OK)
)
[oracle@oracle01 admin]$
Reload the listener afterward with lsnrctl reload LISTENER. As with any heterogeneous service, you can alternatively run a dedicated listener on a separate port for SQL Server traffic if you'd rather not restart the existing one.
Step 5: Verify the Listener and TNS Connectivity
Confirm the SQL Server service registered correctly, then use tnsping to verify the TNS alias resolves.
[oracle@oracle01 admin]$ lsnrctl status
LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 21-FEB-2025 11:50:17
Copyright (c) 1991, 2022, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date 10-FEB-2025 11:50:17
Uptime 1 days 8 hr. 10 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle01/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle01)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ORCLPRD" has 2 instance(s).
Instance "ORCLPRD", status UNKNOWN, has 1 handler(s) for this service...
Instance "ORCLPRD", status READY, has 1 handler(s) for this service...
Service "ORCLPRDXDB" has 1 instance(s).
Instance "ORCLPRD", status READY, has 1 handler(s) for this service...
Service "MSSQL" has 1 instance(s).
Instance "MSSQL", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracle01 admin]$
[oracle@oracle01 admin]$ tnsping "MSSQL"
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 21-FEB-2025 11:51:07
Copyright (c) 1997, 2022, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/19.0.0/dbhome_1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.80.33)(PORT=1521)) (CONNECT_DATA=(SID=MSSQL)) (HS=OK))
OK (0 msec)
Step 6: Test the Connection With sqlcmd and isql
This is the step where SQL Server's tooling differs most from the MySQL setup. Rather than testing with MySQL's own client, we have sqlcmd, Microsoft's native command-line tool installed with mssql-tools, alongside the generic isql utility. Testing with both gives you two independent confirmations that the driver and credentials work before touching Oracle at all.
[oracle@oracle01 lib64]$ /opt/mssql-tools/bin/sqlcmd -D -S MSSQL -U ViewSQL
Password:
1> select count(*) from users
2> go
-----------
1083
(1 rows affected)
1>
2> quit
[oracle@oracle01 lib64]$
[oracle@oracle01 lib64]$ isql -v MSSQL ViewSQL YourP@ssw0rd
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select count(*) from users
+------------+
| |
+------------+
| 1083 |
+------------+
SQLRowCount returns 0
1 rows fetched
SQL>
[oracle@oracle01 lib64]$
Quick reference for the sqlcmd flags used above: -U is the user, -D tells it to use quoted identifiers by default, -S specifies the SQL Server instance (matching your DSN or TNS-style alias), and -v (used with isql) enables verbose output.
Step 7: Create the Database Link
With connectivity confirmed through both tools, create the database link. As with any heterogeneous link, choose a private link (created while logged in as the specific owning user) if you want to restrict access, or a public link, shown here, if it should be available broadly with the right grants.
[oracle@oracle01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 20 12:07:08 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> CREATE PUBLIC DATABASE LINK ORACLE_TO_MSSQL_DBLINK CONNECT TO "ViewSQL" IDENTIFIED BY "YourP@ssw0rd" USING 'MSSQL';
Database link created.
SQL> set lines 333 pages 333
SQL> col OWNER for a20
SQL> col DB_LINK for a80
SQL> col USERNAME for a20
SQL> col HOST for a30
SQL> col CREATED for a10
SQL> select OWNER,DB_LINK,USERNAME,HOST,CREATED from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED
-------------------- ------------------------------------ -------------------- ------------------------------ ----------
SYS SYS_HUB SEEDDATA 17-APR-19
PUBLIC ORACLE_TO_MSSQL_DBLINK ViewSQL MSSQL 21-FEB-25
SQL> select sysdate from dual@ORACLE_TO_MSSQL_DBLINK;
SYSDATE
---------
21-FEB-25
SQL> select * from dual@ORACLE_TO_MSSQL_DBLINK;
D
-
X
SQL>
The successful dual@ORACLE_TO_MSSQL_DBLINK queries confirm the full chain works, Oracle Heterogeneous Services, through Microsoft's ODBC driver, to SQL Server, and back with a valid response.
What Next --> Oracle to MySQL DB Link
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment