Step-by-step guide to create a database link from Oracle to Microsoft SQL Server

Guide to create a database link from Oracle to Microsoft SQL Server using Oracle Heterogeneous Services (HS) and ODBC Driver 17 for SQL Server. This guide includes driver installation, configuration files, DB link creation, and validation steps.

🔧 Environment Setup (Dummy Names Used)

Oracle SID: ORCL

MSSQL Host: mssql.example.com

MSSQL DB Name: HRDB

MSSQL User: hruser

MSSQL Password: Test@1234

DSN Name: MSSQL_DSN

Oracle DB Link Name: HRMSSQL_LINK

Gateway Identifier: MSSQL_DSN

1. Install Required Packages

a. Install ODBC Driver 17 for SQL Server


From root user
# Add Microsoft repo

curl https://packages.microsoft.com/config/rhel/7/prod.repo -o /etc/yum.repos.d/msprod.repo
exit

# Install tools

yum install -y msodbcsql17 unixODBC

2. Configure ODBC

a. Edit `/etc/odbcinst.ini`


[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.6.1
UsageCount=1

b. Edit `/etc/odbc.ini`


[MSSQL_DSN]
Description=SQL Server ODBC Data Source
Driver=ODBC Driver 17 for SQL Server
Server=mssql.example.com
Port=1433
Database=HRDB
User=hruser
Password=Test@1234

3. Validate ODBC Connectivity


# Use isql to validate
isql -v MSSQL_DSN hruser Test@1234

Expected output includes:


+---------------------------------------+
| Connected!                            |
| SQL>                                  |

4. Configure Oracle Gateway (HS)

a. Create `initMSSQL_DSN.ora`

Location: $ORACLE_HOME/hs/admin/initMSSQL_DSN.ora


HS_FDS_CONNECT_INFO = MSSQL_DSN
HS_LANGUAGE = AMERICAN_AMERICA.WE8ISO8859P1
HS_FDS_RECOVERY_ACCOUNT = RECOVER
HS_FDS_RECOVERY_PWD = RECOVER
HS_FDS_ENCRYPT_SESSION = SSL
HS_FDS_VALIDATE_SERVER_CERT = 0
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

5. Listener Configuration

a. Edit `$ORACLE_HOME/network/admin/listener.ora`

`I created a new listener you can use exiting one as well`


LISTENER_MSSQL =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL=TCP)(HOST=<hostname>)(PORT=1522))
    )
  )

SID_LIST_LISTENER_MSSQL =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = MSSQL_DSN)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0.0/dbhome_1)
      (PROGRAM = dg4odbc)
    )
  )

Replace ORACLE_HOME path with your actual installation.

b. Restart Listener


lsnrctl stop LISTENER_MSSQL
lsnrctl start LISTENER_MSSQL
lsnrctl status LISTENER_MSSQL

Ensure MSSQL_DSN is listed under services.

Services Summary...

Service "MSSQL_DSN" has 1 instance(s).

Instance "MSSQL_DSN", status UNKNOWN, has 1 handler(s) for this service...

6. Configure Oracle TNS

a. Edit `$ORACLE_HOME/network/admin/tnsnames.ora`


MSSQL_DSN =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=host)(PORT=1522))
    (CONNECT_DATA=(SID=MSSQL_DSN))
    (HS=OK)
  )

7. Create Database Link in Oracle

Connect to Oracle DB as a user with appropriate privileges:


CREATE DATABASE LINK HRMSSQL_LINK
  CONNECT TO "hruser" IDENTIFIED BY "Test@1234"
  USING 'MSSQL_DSN';

8. Test the Database Link


SELECT * FROM dual@HRMSSQL_LINK;

-- Test table query
SELECT * FROM all_users@HRMSSQL_LINK;

9. Troubleshooting

a. Enable Gateway Tracing

In initMSSQL_DSN.ora, uncomment and modify:


HS_FDS_TRACE_LEVEL = 4

b. Check Gateway Logs


cd $ORACLE_HOME/hs/log
cat listener.log or MSSQL_DSN_*.log

10. 🧹 Summary of Files Created

File	Purpose
`/etc/odbcinst.ini`	Defines ODBC driver path
`/etc/odbc.ini`	DSN config to MS SQL Server
`$ORACLE_HOME/hs/admin/initMSSQL_DSN.ora`	HS gateway config
`$ORACLE_HOME/network/admin/listener.ora`	Oracle listener with gateway SID
`$ORACLE_HOME/network/admin/tnsnames.ora`	TNS alias to the gateway
Oracle DB Link	Created via SQL command

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Step-by-step guide to create a database link from Oracle to Microsoft SQL Server