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

Published 01-20-2021 11:19 AM 1,415 Views

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!

%3CLINGO-SUB%20id%3D%22lingo-sub-2079685%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23157%3A%20Connecting%20to%20Azure%20Managed%20Instance%20Using%20Oracle%20Database%20Gateway%20for%20ODBC%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2079685%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20we%20received%20a%20new%20service%20request%20that%20our%20customer%20wants%20to%20connect%20from%20Oracle%20to%20Azure%20SQL%20Managed%20Instance%20or%20Azure%20SQL%20Database%20using%20Oracle%20Database%20Gateway%20for%20ODBC%20using%20a%20Windows%20Operating%20system.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFollowing%2C%20I%20would%20like%20to%20share%20with%20you%20the%20steps%20that%20we've%20done%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId-150213183%22%20id%3D%22toc-hId-150211509%22%3EFirst%20Step%3A%20Installation%20and%20Configuration%20of%20the%20different%20components%3A%3C%2FH4%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E1)%26nbsp%3BOracle%20Database%20Gateway%20for%20ODBC%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EInstall%20it%20defining%20a%20new%20listener%20using%20the%20port%2C%20for%20example%2C%201528.%3C%2FLI%3E%0A%3CLI%3EI%20modified%20the%20listerner.ora%20adding%20the%20following%20text%3A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3ESID_LIST_LISTENER_ODBC%20%3D%0A%20%20(SID_LIST%20%3D%0A%20%20%20%20(SID_DESC%3D%0A%20%20%20%20%20%20%20(SID_NAME%3Ddg4odbc)%0A%20%20%20%20%20%20%20(ORACLE_HOME%3DC%3A%5Capp%5Ctg%5Cusername%5Cproduct%5C19.0.0%5Ctghome_2)%0A%20%20%20%20%20%20%20(PROGRAM%3Ddg4odbc)%0A%20%20%20%20)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EThe%20final%20result%20of%20listener.ora%20file%20looks%20like%3A%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%23%20listener.ora%20Network%20Configuration%20File%3A%20C%3A%5Capp%5Ctg%5Cusername%5Cproduct%5C19.0.0%5Ctghome_2%5CNETWORK%5CADMIN%5Clistener.ora%0A%23%20Generated%20by%20Oracle%20configuration%20tools.%0A%0ALISTENER_ODBC%20%3D%0A%20%20(DESCRIPTION_LIST%20%3D%0A%20%20%20%20(DESCRIPTION%20%3D%0A%20%20%20%20%20%20(ADDRESS%20%3D%20(PROTOCOL%20%3D%20TCP)(HOST%20%3D%20localhost)(PORT%20%3D%201528))%0A%20%20%20%20%20%20(ADDRESS%20%3D%20(PROTOCOL%20%3D%20IPC)(KEY%20%3D%20EXTPROC1528))%0A%20%20%20%20)%0A%20%20)%0A%0ASID_LIST_LISTENER_ODBC%20%3D%0A%20%20(SID_LIST%20%3D%0A%20%20%20%20(SID_DESC%3D%0A%20%20%20%20%20%20%20(SID_NAME%3Ddg4odbc)%0A%20%20%20%20%20%20%20(ORACLE_HOME%3DC%3A%5Capp%5Ctg%5Cusername%5Cproduct%5C19.0.0%5Ctghome_2)%0A%20%20%20%20%20%20%20(PROGRAM%3Ddg4odbc)%0A%20%20%20%20)%0A%20%20)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EI%20restarted%20the%20listener%20for%20this%20specific%20Oracle%20Instance.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSTRONG%3E2)%20I%20modified%20the%20tnsnames.ora%20adding%20the%20following%20text%3A%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3Edg4odbc%20%3D%0A%20%20(DESCRIPTION%3D%0A%20%20%20%20(ADDRESS%3D(PROTOCOL%3Dtcp)(HOST%3Dlocalhost)(PORT%3D1528))%0A%20%20%20%20(CONNECT_DATA%3D(SID%3Ddg4odbc))%0A%20%20%20%20(HS%3DOK)%0A%20%20)%20%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E3)%20I%20re-started%20the%20listener.%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E4)%20I%20modified%20the%20file%20placed%20on%20%3CORACLE_HOME_FOLDER%3E%5Cadmin%20subfolder%20on%20your%20Oracle%20Home%20Installation%20with%20the%20following%20parameters%3A%3C%2FORACLE_HOME_FOLDER%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-json%22%3E%3CCODE%3E%23%20This%20is%20a%20sample%20agent%20init%20file%20that%20contains%20the%20HS%20parameters%20that%20are%0A%23%20needed%20for%20the%20Database%20Gateway%20for%20ODBC%0A%0A%23%0A%23%20HS%20init%20parameters%0A%23%0AHS_FDS_CONNECT_INFO%20%3D%20dg4odbc%0AHS_FDS_TRACE_LEVEL%20%3D%20OFF%0A%0A%0A%23%0A%23%20Environment%20variables%20required%20for%20the%20non-Oracle%20system%0A%23%0A%23set%20%3CENVVAR%3E%3D%3CVALUE%3E%3C%2FVALUE%3E%3C%2FENVVAR%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E5)%20The%20parameter%20of%20the%20previous%20file%26nbsp%3BHS_FDS_CONNECT_INFO%20contains%20the%20name%20of%20the%20Data%20Source%20Name%20that%20we%20are%20going%20to%20use%20to%20connect.%20So%2C%20in%20this%20situation%2C%20using%20ODBC%20Data%20Source%20Administrator%20(64-Bit)%20I%20defined%20the%20following%20data%20source%20parameters%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture1.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247809i30C828439A02DA73%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture1.PNG%22%20alt%3D%22Capture1.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EName%3C%2FSTRONG%3E%3A%20dg4odbc%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EServer%3C%2FSTRONG%3E%3A%20The%20name%20of%20my%20public%20instance%20name%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture3.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247810i46FEC8C584106F05%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture3.PNG%22%20alt%3D%22Capture3.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESQL%20Server%20Authentication.%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture4.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247811iCCD59C6F19B5C30C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture4.PNG%22%20alt%3D%22Capture4.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EDatabase%20Name%20that%20I%20want%20to%20connect.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Capture5.PNG%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F247813i83D2F077C25F9A0C%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Capture5.PNG%22%20alt%3D%22Capture5.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%20class%3D%22lia-indent-padding-left-30px%22%3E%26nbsp%3B%3C%2FP%3E%0A%3CH4%20id%3D%22toc-hId--1657241280%22%20id%3D%22toc-hId--1657242954%22%3ESecond%20Step%3A%20Test%20the%20connectivity%20and%20run%20a%20sample%20query.%3C%2FH4%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EOpen%20a%20new%20Windows%20Command%20Prompt%2C%20I%20run%20sqlplus%20to%20connect%20to%20any%20instance%20of%20Oracle%20that%20I%20have%3A%26nbsp%3Bsqlplus%20system%2FMyPassword!%40OracleInstance%20as%20sysdba%3C%2FLI%3E%0A%3CLI%3EI%20created%20a%20database%20link%20to%20connect%20using%20ODBC%20to%20my%20Azure%20SQL%20Managed%20Instance%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Ecreate%20database%20link%20my4%20connect%20to%20%22myuserName%22%20identified%20by%20%22MyPassword!%22%20using%20'dg4odbc'%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EFinally%2C%20I%20executed%20the%20following%20query%20to%20obtain%20data%20from%20the%20table%20customers%20making%20a%20reference%20of%20this%20database%20link%3A%26nbsp%3B%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3Eselect%20*%20from%20customers%40my4%3B%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20I%20mentioned%20before%20this%20configuration%20process%20works%2C%20also%2C%20connecting%20to%20Azure%20SQL%20Database.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2079685%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20we%20received%20a%20new%20service%20request%20that%20our%20customer%20wants%20to%20connect%20from%20Oracle%20to%20Azure%20SQL%20Managed%20Instance%20or%20Azure%20SQL%20Database%20using%20Oracle%20Database%20Gateway%20for%20ODBC%20using%20a%20Windows%20Operating%20system.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOur%20customer%20wants%20to%20send%20data%20from%20Oracle%20database%20to%20Azure%20SQL%20Managed%20Instance.%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Version history
Last update:
‎Jan 20 2021 11:31 AM
Updated by: