This article is contributed. See the original author and article here.

Today, we received a new service request that our customer wants to connect from Oracle to Azure SQL Managed Instance or Azure SQL Database using Oracle Database Gateway for ODBC using a Windows Operating system.


 


Following, I would like to share with you the steps that we’ve done:


 


First Step: Installation and Configuration of the different components:


 


1) Oracle Database Gateway for ODBC



  • Install it defining a new listener using the port, for example, 1528.

  • I modified the listerner.ora adding the following text:


 

SID_LIST_LISTENER_ODBC =
  (SID_LIST =
    (SID_DESC=
       (SID_NAME=dg4odbc)
       (ORACLE_HOME=C:apptgusernameproduct19.0.0tghome_2)
       (PROGRAM=dg4odbc)
    )
  )

 



  • The final result of listener.ora file looks like:


 

# listener.ora Network Configuration File: C:apptgusernameproduct19.0.0tghome_2NETWORKADMINlistener.ora
# Generated by Oracle configuration tools.

LISTENER_ODBC =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1528))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1528))
    )
  )

SID_LIST_LISTENER_ODBC =
  (SID_LIST =
    (SID_DESC=
       (SID_NAME=dg4odbc)
       (ORACLE_HOME=C:apptgusernameproduct19.0.0tghome_2)
       (PROGRAM=dg4odbc)
    )
  )

 



  • I restarted the listener for this specific Oracle Instance. 


2) I modified the tnsnames.ora adding the following text: 


 

dg4odbc =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1528))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
  ) 

 


3) I re-started the listener.


4) I modified the file placed on <oracle_home_folder>admin subfolder on your Oracle Home Installation with the following parameters:


 


 

# 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 = dg4odbc
HS_FDS_TRACE_LEVEL = OFF


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

 


 


5) The parameter of the previous file HS_FDS_CONNECT_INFO contains the name of the Data Source Name that we are going to use to connect. So, in this situation, using ODBC Data Source Administrator (64-Bit) I defined the following data source parameters:


 


Capture1.PNG




  • Name: dg4odbc

  • Server: The name of my public instance name


 


Capture3.PNG




  • SQL Server Authentication. 


Capture4.PNG




  • Database Name that I want to connect.


Capture5.PNG


 


Second Step: Test the connectivity and run a sample query.


 



  • Open a new Windows Command Prompt, I run sqlplus to connect to any instance of Oracle that I have: sqlplus system/MyPassword!@OracleInstance as sysdba

  • I created a database link to connect using ODBC to my Azure SQL Managed Instance


 

create database link my4 connect to "myuserName" identified by "MyPassword!" using 'dg4odbc';

 



  • Finally, I executed the following query to obtain data from the table customers making a reference of this database link: 


 

select * from customers@my4;

 


As I mentioned before this configuration process works, also, connecting to Azure SQL Database.


 


Enjoy!

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.