Closure
Closure
Home » Databases » Oracle Database » How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux

How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux

How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux
, , , , , , Leave a comment
How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux
Latest posts by Stratos Matzouranis (see all)

In this article we will see how we can execute queries on remote SQL Server from Oracle Database on Oracle Linux / Red Hat Linux.

In previous article we saw how the reverse process is done to connect from SQL Server Instance to Oracle Database via Linked Server. This time we will install one Oracle Database Gateway through which we will make one Database Link to a database located on SQL Server.

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 the Linux Oracle Database.

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

On Oracle Database Linux Server

In Linux where the Oracle Database is located we download the ODBC driver for SQL Server and install it.

To install, run the following code as it is:

#Red Hat Enterprise Server 7 and Oracle Linux 7
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

exit
sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install -y msodbcsql18
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y yum install -y mssql-tools18
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo yum install -y unixODBC-devel

Then we open the file containing the list of drivers to see that there is a registration with ODBC for SQL Server and to keep the path where the driver is located:

vi /etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1
UsageCount=1

Then we go to add the DSN (Data Source Name) entry for ODBC:

vi /etc/odbc.ini

There we put the record like the example below changing its name, the name of the server, the name of the database and as a driver the path from the previous step:

[MIG_DB]
Description     = MSSQL Server
Trace           = No
Server          = SMATZOURANISLP
Database        = MIG_DB
Port            = 1433
TDS_Version     = 7.2
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1
TrustServerCertificate=Yes

To test that it works, we run the following by putting the DSN name, the username we created in SQL Server and the password:

isql MIG_DB oraclegw 'password' -v

If it works correctly it will display the following message:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+

As a next step, we should create the following file by putting the corresponding name with the DSN:

vi $ORACLE_HOME/hs/admin/initMIG_DB.ora

To this we add the following entries changing only the parameter HS_FDS_CONNECT_INFO with the name of the DSN we have made, as HS_LANGUAGE define the encoding of the Oracle Database that will be the destination:

HS_FDS_CONNECT_INFO = MIG_DB
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
set ODBCINI=/etc/odbc.ini
HS_KEEP_REMOTE_COLUMN_SIZE = LOCAL
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

Then we go to listener.ora and add the following record by putting as SID_NAME the name of the DSN (to keep it as simple as possible) and the corresponding ORACLE_HOME:

vi $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER=
(SID_DESC=
  (SID_NAME=MIG_DB)
  (ORACLE_HOME=/oracle/nfs/app/oracle/product/12.1.0.2/dbhome_1)
  (PROGRAM=dg4odbc)
)

After adding the registration to the listener, we reload it:

lsnrctl reload

Then we add this recording that we made to the listener tnsnames.ora:

vi $ORACLE_HOME/network/admin/tnsnames.ora

* Be careful to put the Linux Oracle Database Server as the host and not the SQL Server, with the door as it is.

MIG_DB =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=oracledev1)(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;
How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux
01

In the event that the following error appears to us:

ERROR at line 1:
ORA-02085: database link SQLDBLINK connects to HO.WORLD

Then we need to disable them global names with the following command:

alter system set global_names = false scope=both;

Sources:

Share it

Leave a reply