How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows
Certifications:
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024
In a previous article we saw how we can run queries on a remote SQL Server from an Oracle Database located on Linux via Oracle Database Gateway. In this article we will see how to install Oracle Gateway respectively on Windows.
Oracle Gateway can be installed either on one of the database servers or on a third party machine that will act as an intermediary. In this example we will proceed with the installation on Windows SQL Server.
Prerequisites
We should have downloaded the Oracle Gateway software for Windows from the official link to the Windows Server here.
The footsteps
On the SQL Server Instance
To begin with, we should create a login in the SQL Server Instance that will have read access to the database we want to read from the Oracle Database. In our example we will create a user named oraclepw and will have access to the base mig_db:
USE [master] GO CREATE LOGIN [oraclegw] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [mig_db] GO CREATE USER [oraclegw] FOR LOGIN [oraclegw] GO USE [mig_db] GO ALTER ROLE [db_datareader] ADD MEMBER [oraclegw] GO
Then we go to the Windows Server where Oracle Gateway will be installed and create one ODBC connection to the Oracle Database Server. To do this we are looking from the beginning ODBC Data Sources (64-bit) and select –> System DSN –> Add… .
In the tab System DSN we define a name and the server we will connect to. In the example, Oracle Gateway will be installed on the same machine as the target SQL Server, so we write in the Server field, localhost:
Then we fill in the login credentials we created at the beginning to connect to SQL Server:
After completing the ODBC creation, we can start installing the software. During the installation it will ask us to define the location where the installation will take place and with which components, in the components we choose Oracle Database Gateway for ODBC:
When the installation is complete it will ask us to fix it listener, there we will define the port that will communicate between Oracle Gateway and Oracle Database:
After the creation of the listener is complete, we should go to the path where we installed the Oracle Gateway and create an empty file init which contains the name of the ODBC we will call it like below:
C:\app\tg\user\product\19.0.0\tghome_1\hs\admin\initMIG_DB.ora
In this file we fill in the following where HS_FDS_CONNECT_INFO we set the name of the ODBC we made and as HS_LANGUAGE the encoding of the destination Oracle Database:
# 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=MIG_DB
HS_FDS_TRACE_LEVEL=off
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8
HS_FDS_FETCH_ROWS=1
Then we need to modify the listener.ora which is located in the corresponding path where we did the installation:
C:\app\tg\user\product\19.0.0\tghome_1\network\admin\listener.ora
And let's add the following entry defining as SID_NAME the ODBC name we set to be the same as the Gateway init and as ORACLE_HOME the corresponding path where the initial installation was made:
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=MIG_DB)
(ORACLE_HOME=C:\app\tg\user\product\19.0.0\tghome_1)
(PROGRAM=dg4odbc)
)
)On Oracle Database Linux Server
Then on the Linux where the Oracle Database is located we need to add to tnsnames.ora record that looks at the Oracle Gateway we built, setting as HOST its name or IP and as CONNECT_DATA the name of the gateway we set in the listener:
vi $ORACLE_HOME/network/admin/tnsnames.ora
* Be careful to put the name / IP where the Oracle Gateway is installed as host.
MIG_DB =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=SMATZOURANISLP)(PORT=1521))
(CONNECT_DATA=(SID=MIG_DB))
(HS=OK)
)Finally we make it Database Link, specifying a name, the SQL Server login, the password and the name of the record we added to tnsnames.ora:
sqlplus / as sysdba CREATE DATABASE LINK SQLDBLINK CONNECT TO "oraclegw" IDENTIFIED BY "password" USING 'MIG_DB';
The test
After creating the Database Link, we can query the Oracle Database to call the database tables in the SQL Server as below:
select * from dbo.customers@sqldblink;
In the event that the following error appears to us:
ERROR at line 1:
ORA-02085: database link SQLDBLINK connects to HO.WORLDThen we need to disable them global names with the following command:
alter system set global_names = false scope=both;
In the event that the error appears to us:
ERROR at line 1:
ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
ORA-02063: preceding 2 lines from <DBLINK>
Then we add the following line to the SQLNET.ora Oracle Gateway file:
C:\app\tg\user\product\19.0.0\tghome_1\network\admin\SQLNET.oraSQLNET.NO_NTLM=FALSE
