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:
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
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:
Server: The name of my public instance name
SQL Server Authentication.
Database Name that I want to connect.
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.