Create a Database link in Oracle to access the MSSQL Server database on Windows

In my case, both SQL Server and Oracle database are on the same server. So I don’t need to set up the Oracle Database Gateway for ODBC. You need to download and install the database gateway software if your Oracle Database is not patched but now it is almost there so no need.

Following are the steps we are going to follow:

1. Configure the ODBC DNS in the windows ODBC Datasource(64-bit).
2. Configure the configuration files in HS.
3. Configure the Network folder files as listener.ora or tnsnames.ora.
4. Create the Database link with SQLServer.

Step 1: Configure the ODBC DNS in windows:

1. Open the ODBC Data Source.

2. Press add button to add SQL Server ODBC Connection as DNS. Choose SQL Server or SQL Server Native Client for establish SQL Server connection.

3. Enter the DNS name and Server detail in which your SQL Server is present as IP address, hostname or if local then localhost or . etc. Press Next.

4. Choose the SQL Server Authentication and type username and password to login in SQL Server. Press Next.

5. Choose the default database on which this DNS works. Press Next.

6. Click Finish.

7. Test the connection

8. After successful its created.

9. Finish

Step 2: Configure the configuration files in HS.

Go to the location %ORACLE_HOME%\hs\admin
Create new file from sample file already present initdg4odbc.ora to init<dnsname>.ora
Open the new file and edit like following:

HS_FDS_CONNECT_INFO = <DNS NAME>
HS_FDS_TRACE_LEVEL = OFF

Example:

1. Go to directory: C:\Oracle\dbhomeXE\hs\admin
2. Create new file by Copy file from initdg4odbc.ora to initSQLSERVER5.ora.
3. Open file in notepad and change the following entries:

HS_FDS_CONNECT_INFO = SQLSERVER5
HS_FDS_TRACE_LEVEL = OFF

Step 3: Configure the Network folder files

TNSNAMES.ORA File

-- Edit the TNSNAMES.ORA file:

Open the TNSnames.ora file and create a new entry for SQLSERVER DNS
Note: ODBC DNS name created in first step from windows system dns.

tnsentry =
   (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=addressoforacle)(PORT = portoforacle))
    (CONNECT_DATA=(SID=odbc_dns_name))
    (HS=OK)
 )

Example: I created SQLSERVER5 as DNS Name in SYSTEM tab of ODBC in windows:

SQLSERVER =
   (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT = 1521))
    (CONNECT_DATA=(SID=SQLSERVER5))
    (HS=OK)
 )

LISTENER.ORA

Edit the Listener.ora file:

-- Add a SID_DESC entry in existing file like:

  (SID_DESC =
     (SID_NAME = odbc_dns_name)
     (ORACLE_HOME = oracle_home_path)
     (PROGRAM = dg4odbc)
   )
   
Example: In my existing file i make a entry at last in SID_LIST_LISTENER section, not change anything else in listener.ora
  
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\Oracle\dbhomeXE)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\Oracle\dbhomeXE\bin\oraclr18.dll")
    )
    (SID_DESC =
      (GLOBAL_DBNAME = PDB1)
      (ORACLE_HOME = C:\Oracle\dbhomeXE)
      (SID_NAME = PDB1)
    )
    (SID_DESC =
     (SID_NAME = SQLSERVER5 )
     (ORACLE_HOME = C:\oracle\dbhomexe)
     (PROGRAM = dg4odbc)
    )
  )


Restart the Listener after adding SID entry:

Test the tnsping:

Test TNSPING:

tnsping sqlserver;
TNS Ping Utility for 64-bit Windows: Version 18.0.0.0.0 - Production on 06-APR-2022 17:58:16
Copyright (c) 1997, 2018, Oracle.  All rights reserved.
Used parameter files:
C:\Oracle\dbhomeXE\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT = 1521)) (CONNECT_DATA=(SID=SQLSERVER5)) (HS=OK))
OK (20 msec)

Step 4: Create the Database link with SQLServer

Go to SQLPLUS and try to create the database link:

SQLPLUS / as sysdba

CREATE DATABASE LINK sqlserver CONNECT TO "USERNAME" IDENTIFIED BY "PASSWORD" USING 'tnsentry';

Example:
SQL> CREATE DATABASE LINK sqlserver CONNECT TO "sa" IDENTIFIED BY "pass" USING 'SQLSERVER';
Database link created.

SQL> select * from test1@sqlserver;
name
------------------------------
RAM
SHAM