CONNECTING TO MICROSOFT SQL SERVER DATABASE FROM ORACLE USING DATABASE GATEWAY USING SQL SERVER GATEWAY FOR ORACLE

Author:-SOUMEN KUMAR DAS

Connecting to Microsoft SQL Server database from Oracle using SQL SERVER GATEWAY FOR ORACLE

Following steps are involved..

1. Download Oracle Database Gateways CD if you have not already installed it
2. Install Oracle Database Gateway for Microsoft SQL Server
3. Configure Database Gateway for Microsoft SQL Server (DG4MSQL).

• Download Oracle Database Gateways from Oracle eDelivery site. (download the correct version)

• Install Oracle Database Gateway for Microsoft SQL server

-bash-3.00$ cd /software/11gR2-Gateways/gateways/
-bash-3.00$ ./runInstaller

Select PATH for the Oracle Home. The Path defaults to Oracle Home. No need to change the same

Once it prompts with above screen, open another shell and run the script as root

Click Close to Finish the installation.

• Check if port is opened in target server (SQL SERVER) from Source (Oracle) for sql server gateway for oracle .

esesslxXXXX.world:ORAPROD1:> telnet ESESSMW9999.world 1433
Trying 153.88.xxx.xx...
Connected to ESESSMW9999.world.

• Configure Oracle Database Gateway for SQL SERVER GATEWAY FOR ORACLE .

Make sure that the MS SQL database details are correct in the dg4msql parameter file.

esesslxXXXX.world:ORAPROD1:> cat initdg4msql1.ora
# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server
#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[ESESSMW9999.world]:1433//SQLDB_PROD

HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER

• Add below entry to /opt/oracle/12.2.0.1/db/network/admin/listener.ora

LISTENER_DG4MSQL =
  (ADDRESS_LIST =
        (ADDRESS= (PROTOCOL= TCP)(Host= esesslxXXXX.world)(Port= 1522))
  )

SID_LIST_LISTENER_DG4MSQL =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dg4msql)
      (ORACLE_HOME= /opt/oracle/12.2.0.1/db/dg4msql)
     (ENV=LD_LIBRARY_PATH=/opt/oracle/12.2.0.1/db/dg4msql/driver/lib:/opt/oracle/12.2.0.1/db/lib/)
      (PROGRAM = dg4msql)
    )
      (SID_DESC =
      (SID_NAME = dg4msql1)
      (ORACLE_HOME= /opt/oracle/12.2.0.1/db/dg4msql)
 (ENV=LD_LIBRARY_PATH=/opt/oracle/12.2.0.1/db/dg4msql/driver/lib:/opt/oracle/12.2.0.1/db/lib/)
      (PROGRAM = dg4msql)
    )
  )

esesslxXXXX.world:ORAPROD1:> lsnrctl stat LISTENER_dg4msql
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 29-JAN-2018 10:49:48
Copyright (c) 1991, 2016, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=esesslxXXXX.world)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_dg4msql
Version                   TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date                29-JAN-2018 10:41:11
Uptime                    0 days 0 hr. 8 min. 36 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/12.2.0.1/db/network/admin/listener.ora
Listener Log File         /opt/oracle/12.2.0.1/db/network/log/listener_dg4msql1.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=esesslxXXXX.world)(PORT=1522)))
Services Summary...
Service "dg4msql" has 1 instance(s).
  Instance "dg4msql", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

 dg4msql = 
(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(Host = esesslxXXXX.world)(Port = 1522))
(CONNECT_DATA= (SID=dg4msql))
   (HS=OK))

-bash-3.00$ tnsping dg4msql
TNS Ping Utility for Linux: Version 12.2.0.1.0 - Production on 16-MAR-2018 11:27:09
Copyright (c) 1997, 2016, Oracle.  All rights reserved.
Used parameter files:
/opt/oracle/12.2.0.1/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(Host = esesslxXXXX.world)(Port = 1522)) (CONNECT_DATA= (SID=dg4msql)) (HS=OK))
OK (10 msec)

•  Create a new database link pointing to this TNS using SQL Server login     credentials. This username must be already created in the Microsoft SQL Server database.

SQL> CREATE PUBLIC DATABASE LINK SQLDB_DBLINK CONNECT TO <username> IDENTIFIED BY <password> using 'dg4msql'; 

Database link created.

• Connect and check tables from target Database.

SQL> select count(*) from T_PA_OBJECTS@SQLDB_DBLINK;
  COUNT(*)
 --------
   16402

This concludes setup steps to read and write data of Microsoft SQL server tables from Oracle database.

About the Author

Soumen Kumar das