Lesson Learned #157: Connecting to Azure Managed Instance Using Oracle Database Gateway for ODBC

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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:\app\tg\username\product\19.0.0\tghome_2) (PROGRAM=dg4odbc) ) )

 

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

 

# listener.ora Network Configuration File: C:\app\tg\username\product\19.0.0\tghome_2\NETWORK\ADMIN\listener.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:\app\tg\username\product\19.0.0\tghome_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!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.