Steps to create a Database Link from Oracle to MSSQL Server

Jamsher Khan 22 mins1
()

Steps to create a Database Link from Oracle to MSSQL Server

Nowadays many companies are using a mix of RDBMS systems, for example, some instances are running on Oracle and other instances are running on MSSQL Server, and if we want to access data we need to do it using a heterogeneous connection. Heterogeneous connections allow us to query data from non-Oracle databases using SQL.

In the previous two blogs, we saw Steps to create a Database Link from Oracle to PostgreSQL Click here to read more. & Steps to create a Database Link from Oracle to MySQL Click here to read more. In this blog post, we will see How to create a database link from Oracle to MS-SQLSERVER via ODBC.


Below are the high-level steps we will follow to set up dblink.
1. Verify MSSQL Users access
2. Install MSSQL Server ODBC Drivers in Oracle Server
3. Edit the odbc.ini file & Test DSN’s connectivity in Oracle Server
4. Create initMSSQL.ora file in Oracle Server
5. Configure tnsname.ora & listener.ora file in Oracle Server
6. Create DB Link & Test Connectivity in Oracle Server


Below are setup details and the same will be used in this demonstration.

Sr. No.DatabaseVersionHostnameUserPort
1Oracle19.3test-machine011521
2MS-SQL SERVERStandard Edition 2017MSSQLDBCS01test_readonly1433


Step 1. Verify MSSQL Users access: We already have a running default instance of MSSQL Server on Server: MSSQLDBCS01 on Default Port: 1433. Also User: test_readonly has already been created and has access to DB: [bidding db] on Table: users. You can verify this using utility sqlcmd.

C:\Users\jkhan>
C:\Users\jkhan>sqlcmd -S MSSQLDBCS01,1433 -U test_readonly -P ucas123
1>
2> use [bidding db]
3> select count(*) from users
4> go
Changed database context to 'bidding db'.

-----------
       1083

(1 rows affected)
1>
2>quit
C:\Users\jkhan>


Step 2. Install MSSQL Server ODBC Drivers in Oracle Server: Verify that the file dg4odbc is present in your ORACLE_HOME environment. Import mssql yum repository using curl command as below. Once the repository is downloaded we can use commands like yum search, yum list, or yum whatprovides to verify required packages are available in the repository. Install packages msodbcsql17, mssql-tools, and unixODBC-devel using the below yum commands. Make sure file libmsodbcsql-17.so exist in location: /usr/lib64 after packages are installed.

[oracle@test-machine01 ~]$
[oracle@test-machine01 ~]$ . oraenv
ORACLE_SID = [oracle] ? test01
The Oracle base has been set to /u01/app/oracle
[oracle@test-machine01 ~]$
[oracle@test-machine01 ~]$
[oracle@test-machine01 ~]$ cd $ORACLE_HOME/bin
[oracle@test-machine01 bin]$
[oracle@test-machine01 bin]$ pwd
/u01/app/oracle/product/19.3.0/db_1/bin
[oracle@test-machine01 bin]$
[oracle@test-machine01 bin]$ ls -l dg4odbc
-rwxr-x--x. 1 oracle oinstall 738176 Nov  2  2020 dg4odbc
[oracle@test-machine01 bin]$


[root@test-machine01 ~]#
[root@test-machine01 ~]#
[root@test-machine01 ~]# curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100   193  100   193    0     0     70      0  0:00:02  0:00:02 --:--:--    70
[root@test-machine01 ~]#
[root@test-machine01 ~]# cat /etc/yum.repos.d/mssql-release.repo
[packages-microsoft-com-prod]
name=packages-microsoft-com-prod
baseurl=https://packages.microsoft.com/rhel/7/prod/
enabled=1
gpgcheck=1
gpgkey=https://packages.microsoft.com/keys/microsoft.asc
[root@test-machine01 ~]#
[root@test-machine01 ~]#

[root@test-machine01 ~]# yum search msodbc
==================================================================================== N/S matched: msodbc =====================================================================================
msodbcsql.x86_64 : ODBC Driver for Microsoft(R) SQL Server(R)
msodbcsql17.x86_64 : ODBC Driver for Microsoft(R) SQL Server(R)
msodbcsql18.x86_64 : ODBC Driver for Microsoft(R) SQL Server(R)

  Name and summary matches only, use "search all" for everything.
[root@test-machine01 ~]#
[root@test-machine01 ~]#
[root@test-machine01 ~]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# yum list msodbc*
Loaded plugins: langpacks, ulninfo
Available Packages
msodbcsql.x86_64                                                                     13.1.9.2-1                                                                    packages-microsoft-com-prod
msodbcsql17.x86_64                                                                   17.9.1.1-1                                                                    packages-microsoft-com-prod
msodbcsql18.x86_64                                                                   18.0.1.1-1                                                                    packages-microsoft-com-prod
[root@test-machine01 ~]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# yum whatprovides msodbc*
Loaded plugins: langpacks, ulninfo
msodbcsql-13.0.1.0-1.x86_64 : ODBC Driver for Microsoft(R) SQL Server(R)
Repo        : packages-microsoft-com-prod
Matched from:
Other       : msodbcsql(x86-64) = 13.0.1.0-1
Other       : msodbcsql = 13.0.1.0-1

msodbcsql-13.1.0.0-1.x86_64 : ODBC Driver for Microsoft(R) SQL Server(R)
Repo        : packages-microsoft-com-prod
Matched from:
Other       : msodbcsql = 13.1.0.0-1
Other       : msodbcsql(x86-64) = 13.1.0.0-1
msodbcsql18-18.0.1.1-1.x86_64 : ODBC Driver for Microsoft(R) SQL Server(R)
Repo        : packages-microsoft-com-prod
Matched from:
Other       : msodbcsql18 = 18.0.1.1-1
Other       : msodbcsql18(x86-64) = 18.0.1.1-1
[root@test-machine01 ~]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# 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@test-machine01 ~]#

[root@test-machine01 ~]# 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@test-machine01 ~]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# 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@test-machine01 ~]#

[root@test-machine01 ~]#
[root@test-machine01 ~]# cd /usr/lib64
[root@test-machine01 lib64]#
[root@test-machine01 lib64]# pwd
/usr/lib64
[root@test-machine01 lib64]#
[root@test-machine01 lib64]#  ls -l libmsodbcsql*
lrwxrwxrwx. 1 root root 57 Jun  4 14:56 libmsodbcsql-17.so -> /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1
[root@test-machine01 lib64]#
[root@test-machine01 lib64]#


Step 3. Edit odbc.ini file & Test DSN’s connectivity in Oracle Server: Edit the odbc files and fill MSSQL Server details under the [MSSQL] header. This header name we will use in the next steps. We can use tools like sqlcmd & isql received from package mssql-tools to test our DSN (Domain Source Name) connectivity. As per the below test connectivity is failed we can use tools like nc in Linux to check if SQL Server Port 1433 is reachable from the Linux server. We need to make sure Port 1433 is open in the firewall. Involve System and Network Admin to get it done.

[root@test-machine01 ~]#
[root@test-machine01 ~]# ls -l /etc/odbc.ini
-rw-r--r--. 1 root root 354 Sep 30  2021 /etc/odbc.ini
[root@test-machine01 ~]#
[root@test-machine01 ~]#

[root@test-machine01 lib64]# vi /etc/odbc.ini
[MSSQL]
Description = MSSQL
Driver = /usr/lib64/libmsodbcsql-17.so
Server = MSSQLDBCS01
User = test_readonly
Password = ucas123
Port = 1433
Database = bidding db
:wq!
[root@test-machine01 lib64]#
[root@test-machine01 lib64]#

[root@test-machine01 lib64]# cat /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.9.so.1.1
UsageCount=1
[root@test-machine01 lib64]#

[root@test-machine01 lib64]# /opt/mssql-tools/bin/sqlcmd -D -S MSSQL -U test_readonly
Password:
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x102.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..
[root@test-machine01 lib64]#

[root@test-machine01 lib64]# nc -v -w 1 MSSQLDBCS01 -z 1433
MSSQLDBCS01 [192.168.91.200] 1433 (ms-sql-s): Connection timed out
[root@test-machine01 lib64]#

[root@test-machine01 lib64]#
[root@test-machine01 lib64]#  nc -v -w 1 MSSQLDBCS01 -z 1433
MSSQLDBCS01 [192.168.91.200] 1433 (ms-sql-s) open
[root@test-machine01 lib64]#

[root@test-machine01 lib64]#
[root@test-machine01 lib64]# /opt/mssql-tools/bin/sqlcmd -D -S MSSQL -U test_readonly
Password:
1> select count(*) from users
2> go

-----------
       1083

(1 rows affected)
1>
2> quit
[root@test-machine01 lib64]#

[root@test-machine01 lib64]#
[root@test-machine01 lib64]# isql -v MSSQL test_readonly ucas123
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select count(*) from users
+------------+
|            |
+------------+
| 1083       |
+------------+
SQLRowCount returns 0
1 rows fetched
SQL>
[root@test-machine01 lib64]#


Step 4. Create initMSSQL.ora file in Oracle Server: Create file initMSSQL.ora under directory $ORACLE_HOME/hs/admin and add the below parameters. Please note we are using MSSQL as our SID name and in the parameter, HS_FDS_CONNECT_INFO = MSSQL is the same header we use in the file: odbc.ini.

root@test-machine01 lib64]#
[root@test-machine01 lib64]# su - oracle
Last login: Sat Jun  4 14:40:06 +03 2022 on pts/0
[oracle@test-machine01 ~]$
[oracle@test-machine01 ~]$
[oracle@test-machine01 ~]$ . oraenv
ORACLE_SID = [oracle] ? test01
The Oracle base has been set to /u01/app/oracle
[oracle@test-machine01 ~]$
[oracle@test-machine01 ~]$ cd $ORACLE_HOME/hs/admin
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ ls -l
total 24
-rw-r--r--. 1 oracle oinstall 1170 Apr 17  2019 extproc.ora
-rw-r--r--. 1 oracle oinstall  489 Apr 17  2019 initdg4odbc.ora
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ touch initMSSQL.ora
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ vi 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
#
:wq!
[oracle@test-machine01 admin]$


Step 5. Configure tnsname.ora & listener.ora file in Oracle Server: Configure tnsnames.ora & listener.ora same as below. Please note here SID = MSSQL is the SID name we created in Step 4. Once listener file changes are done reload the listener using the below command. Add tnsentry MSSQL in tnsnames.ora file or any other tnsname we will use this tnsname during dblink creation.

[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ cd $ORACLE_HOME/network/admin
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ ls -ltr
total 44
-rw-r--r--. 1 oracle oinstall 1536 Feb 14  2018 shrept.lst
drwxr-xr-x. 2 oracle oinstall   64 Apr 17  2019 samples
-rw-r-----. 1 oracle oinstall  671 Sep 30  2021 listener.ora
-rw-r-----. 1 oracle oinstall 1415 Feb  6 14:52 tnsnames.ora
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ cp listener.ora listener_bkp.ora
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (PROGRAM = dg4odbc)
      (SID_NAME = MSSQL)
      (ORACLE_HOME = /u01/app/oracle/product/19.3.0/db_1)
      (ENVS=LD_LIBRARY_PATH=/opt/microsoft/msodbcsql17/lib64)
     )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
USE_SID_AS_SERVICE_listener=on
:wq!
[oracle@test-machine01 admin]$

[oracle@test-machine01 admin]$  lsnrctl reload

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 05-JUN-2022 10:21:52

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-machine01)(PORT=1521)))
The command completed successfully
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 05-JUN-2022 10:21:55

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=test-machine01)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                05-JUN-2022 10:19:15
Uptime                    0 days 0 hr. 2 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/test-machine01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test-machine01)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "MSSQL" has 1 instance(s).
  Instance "MSSQL", status UNKNOWN, has 1 handler(s) for this service...
Service "MYSQL" has 1 instance(s).
  Instance "MYSQL", status UNKNOWN, has 1 handler(s) for this service...
Service "PG" has 1 instance(s).
  Instance "PG", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@test-machine01 admin]$

[oracle@test-machine01 admin]$ vi tnsnames.ora
MSSQL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = MSSQL)
    )
    (HS = OK)
  )
:wq!
[oracle@test-machine01 admin]$ 
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ tnsping MSSQL
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 05-JUN-2022 10:24:06
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = test-machine01)(PORT = 1521))) (CONNECT_DATA = (SID = MSSQL)) (HS = OK))
OK (0 msec)
[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$


Step 6. Create DB Link & Test Connectivity in Oracle Server: Once all the above steps are done login to Oracle DB and create dblink using the same SQLServer username test_readonly and tnsname MSSQL. Once DBLink is created try to access the MSSQL Server table: users.

[oracle@test-machine01 admin]$
[oracle@test-machine01 admin]$ sqlplus sys as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jun 5 10:28:50 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL>
SQL> 
SQL> create public database link  MSSQL_DBLINK connect to test_readonly identified by  ucas123 using 'MSSQL';

Database link created.

SQL>
SQL> select sysdate from dual@MSSQL_DBLINK;

SYSDATE
---------
05-JUN-22

SQL>  select count(*) from users@MSSQL_DBLINK;

  COUNT(*)
----------
      1083

SQL>


This document is just for learning purposes and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the most recent update. Click here to understand more about our pursuit.


Related Articles

, , , ,


Loading

How useful was this post?

Click on a star to rate it!

Average rating / 5. Vote count:

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Jamsher Khan

Hello and welcome to DBsGuru,I’m Jamsher Khan working as Senior Oracle DBA based in KSA-Jeddah, I have working experience in Oracle DBA, SQL Server, MySql, PostgreSQL, Linux, Golden Gate, ODA.Thanks for the visits!Share Learn Grow!

Related Articles