A Step-by-Step Guide to Setting Up Oracle Database Gateway (DG4ODBC) from Oracle 19c to SQL Server

وحید نوروزی database administration 29 August 2026 Hits: 1

<p dir="ltr" lang="en">This guide details the step-by-step process of establishing bidirectional communication and executing queries between <strong>Oracle Database 19c</strong> (running on Oracle Linux 9 / RHEL 9) and <strong>Microsoft SQL Server 2022</strong> (running on Windows Server 2022) using <strong>Oracle Database Gateway for ODBC (DG4ODBC)</strong> and <strong>Database Link</strong>.</p>

<h2 dir="ltr" lang="en">1. Architecture and Environment Details</h2>
<ul dir="ltr" lang="en" style="list-style-type: disc; padding-left: 25px;">
<li><strong>Oracle Server (Oracle Linux 9.x):</strong>
<ul dir="ltr" style="list-style-type: circle; padding-left: 20px;">
<li><strong>Hostname:</strong> <span style="font-family: monospace; color: inherit;">dc1.vahiddb.com</span></li>
<li><strong>IP Address:</strong> <span style="font-family: monospace; color: inherit;">192.168.56.31</span></li>
<li><strong>Oracle Base:</strong> <span style="font-family: monospace; color: inherit;">/u01/app/oracle</span></li>
<li><strong>Oracle Home:</strong> <span style="font-family: monospace; color: inherit;">/u01/app/oracle/product/19c/dbhome</span></li>
<li><strong>Grid Home (if using RAC/GI):</strong> <span style="font-family: monospace; color: inherit;">/u01/app/19c/grid</span></li>
<li><strong>DB / PDB Name:</strong> <span style="font-family: monospace; color: inherit;">vahiddb</span> / <span style="font-family: monospace; color: inherit;">vahidpdb</span></li>
</ul>
</li>
<li><strong>Target Server (SQL Server 2022 on Windows Server):</strong>
<ul dir="ltr" style="list-style-type: circle; padding-left: 20px;">
<li><strong>IP Address:</strong> <span style="font-family: monospace; color: inherit;">192.168.56.19</span></li>
<li><strong>Port:</strong> <span style="font-family: monospace; color: inherit;">1433</span></li>
<li><strong>Database Name:</strong> <span style="font-family: monospace; color: inherit;">testdb</span></li>
<li><strong>SQL User:</strong> <span style="font-family: monospace; color: inherit;">ora_link / OraPassword#123</span></li>
</ul>
</li>
</ul>

<h2 dir="ltr" lang="en">2. Microsoft SQL Server Preparation</h2>
<h3 dir="ltr" lang="en">2.1. Enable Mixed Mode Authentication and Open Firewall Port</h3>
<ol dir="ltr" lang="en" style="padding-left: 25px;">
<li>In <strong>SSMS (SQL Server Management Studio)</strong>, right-click the Instance, navigate to <span style="font-family: monospace; color: inherit;">Properties > Security</span>, and select <strong>SQL Server and Windows Authentication mode</strong>.</li>
<li>In <strong>SQL Server Configuration Manager</strong>, ensure <span style="font-family: monospace; color: inherit;">TCP/IP</span> is enabled and set the default listening port to <span style="font-family: monospace; color: inherit;">1433</span>.</li>
<li>Open port <span style="font-family: monospace; color: inherit;">1433</span> in Windows Defender Firewall via PowerShell:</li>
</ol>

<pre dir="ltr" style="text-align: left; background-color: #f6f8fa; color: #24292e; padding: 12px; border-radius: 6px; border: 1px solid #e1e4e8; font-family: 'Courier New', Courier, monospace; overflow-x: auto;">New-NetFirewallRule -DisplayName "SQL Server Port 1433" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow</pre>

<h3 dir="ltr" lang="en">2.2. Create Database, Login, and Test Table</h3>
<p dir="ltr" lang="en">Execute the following script in SSMS:</p>

USE testdb;
GO

-- 2. Create dedicated user for Oracle Gateway
CREATE LOGIN ora_link WITH PASSWORD = 'OraPassword#123', CHECK_POLICY = OFF;
CREATE USER ora_link FOR LOGIN ora_link;
ALTER ROLE db_datareader ADD MEMBER ora_link;
ALTER ROLE db_datawriter ADD MEMBER ora_link;
GO

-- 3. Create test table and insert sample records
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1, 'Ali Rezaei', 'IT', 75000),
(2, 'Sara Mohammadi', 'HR', 62000),
(3, 'Vahid Karimi', 'Database', 90000);
GO</pre>

<h2 dir="ltr" lang="en">3. Linux Configuration (Oracle Server: dc1)</h2>
<h3 dir="ltr" lang="en">3.1. Install unixODBC and Microsoft ODBC Driver</h3>
<p dir="ltr" lang="en">Run the following commands as the <span style="font-family: monospace; color: inherit;">root</span> user:</p>

<p dir="ltr" lang="en">Install the Microsoft ODBC driver RPM package and accept the license terms by entering <span style="font-family: monospace; color: inherit;">yes</span>:</p>

<h3 dir="ltr" lang="en">3.2. Configure ODBC Files</h3>
<p dir="ltr" lang="en">Configure the DSN entry in <span style="font-family: monospace; color: inherit;">/etc/odbc.ini</span>:</p>

<pre dir="ltr" style="text-align: left; background-color: #f6f8fa; color: #24292e; padding: 12px; border-radius: 6px; border: 1px solid #e1e4e8; font-family: 'Courier New', Courier, monospace; overflow-x: auto;">cat << "EOF" > /etc/odbc.ini
[MSSQLTEST]
Driver=ODBC Driver 18 for SQL Server
Server=192.168.56.19,1433
Database=testdb
TrustServerCertificate=yes
Encrypt=no
EOF</pre>

<h3 dir="ltr" lang="en">3.3. Test ODBC Connectivity with isql</h3>
<p dir="ltr" lang="en">Switch to the <span style="font-family: monospace; color: inherit;">oracle</span> user and verify the connection using the <span style="font-family: monospace; color: inherit;">isql</span> utility:</p>

<p dir="ltr" lang="en">Expected output and query verification:</p>

<h2 dir="ltr" lang="en">4. Configure Oracle Database Gateway (DG4ODBC)</h2>
<h3 dir="ltr" lang="en">4.1. Create Gateway Initialization File (<span style="font-family: monospace; color: inherit;">init<SID>.ora</span>)</h3>
<p dir="ltr" lang="en">As the <span style="font-family: monospace; color: inherit;">oracle</span> user, create <span style="font-family: monospace; color: inherit;">$ORACLE_HOME/hs/admin/initMSSQLTEST.ora</span>:</p>

<pre dir="ltr" style="text-align: left; background-color: #f6f8fa; color: #24292e; padding: 12px; border-radius: 6px; border: 1px solid #e1e4e8; font-family: 'Courier New', Courier, monospace; overflow-x: auto;">cat << "EOF" > $ORACLE_HOME/hs/admin/initMSSQLTEST.ora
HS_FDS_CONNECT_INFO = MSSQLTEST
HS_FDS_TRACE_LEVEL = OFF
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so.2
HS_FDS_SQLLEN_INTERPRETATION = 64
HS_FDS_SUPPORT_STATISTICS = FALSE
HS_KEEP_REMOTE_MM_AND_SEC = TRUE
HS_NLS_NCHAR = UCS2
set ODBCINI=/etc/odbc.ini
set ODBCSYSINI=/etc
HS_LANGUAGE = AMERICAN_AMERICA.AL32UTF8
EOF</pre>

<h3 dir="ltr" lang="en">4.2. Configure listener.ora</h3>
<p dir="ltr" lang="en">Edit <span style="font-family: monospace; color: inherit;">listener.ora</span> (located at <span style="font-family: monospace; color: inherit;">$GRID_HOME/network/admin/listener.ora</span> for Grid/ASM environments, or <span style="font-family: monospace; color: inherit;">$ORACLE_HOME/network/admin/listener.ora</span> for Standalone) and append the <span style="font-family: monospace; color: inherit;">SID_DESC</span> entry for <span style="font-family: monospace; color: inherit;">dg4odbc</span>:</p>

<pre dir="ltr" style="text-align: left; background-color: #f6f8fa; color: #24292e; padding: 12px; border-radius: 6px; border: 1px solid #e1e4e8; font-family: 'Courier New', Courier, monospace; overflow-x: auto;">SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = vahiddb)
(ORACLE_HOME = /u01/app/oracle/product/19c/dbhome)
(SID_NAME = vahiddb)
)
(SID_DESC =
(PROGRAM = dg4odbc)
(SID_NAME = MSSQLTEST)
(ORACLE_HOME = /u01/app/oracle/product/19c/dbhome)
(ENVS = "LD_LIBRARY_PATH=/usr/lib64:/opt/microsoft/msodbcsql18/lib64:/u01/app/oracle/product/19c/dbhome/lib,ODBCINI=/etc/odbc.ini,ODBCSYSINI=/etc")
)
)</pre>

<p dir="ltr" lang="en">Reload the listener to apply the changes:</p>

<h3 dir="ltr" lang="en">4.3. Configure tnsnames.ora</h3>
<p dir="ltr" lang="en">Edit <span style="font-family: monospace; color: inherit;">$ORACLE_HOME/network/admin/tnsnames.ora</span> and append the following entry:</p>

<pre dir="ltr" style="text-align: left; background-color: #f6f8fa; color: #24292e; padding: 12px; border-radius: 6px; border: 1px solid #e1e4e8; font-family: 'Courier New', Courier, monospace; overflow-x: auto;">MSSQLTEST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dc1.vahiddb.com)(PORT = 1521))
(CONNECT_DATA =
(SID = MSSQLTEST)
)
(HS = OK)
)</pre>

<p dir="ltr" lang="en">Verify TNS resolution:</p>

<h2 dir="ltr" lang="en">5. Create Database Link and Verify in Oracle</h2>
<p dir="ltr" lang="en">Log in to SQL*Plus as SYSDBA:</p>

<p dir="ltr" lang="en">Create the Database Link and execute a SELECT query:</p>

-- Create Database Link pointing to SQL Server
CREATE PUBLIC DATABASE LINK mssql_link
CONNECT TO "ora_link" IDENTIFIED BY "OraPassword#123"
USING 'MSSQLTEST';

-- Test SELECT query
SELECT * FROM "employees"@mssql_link;</pre>

<p dir="ltr" lang="en">Successful query output:</p>

<pre dir="ltr" style="text-align: left; background-color: #f6f8fa; color: #24292e; padding: 12px; border-radius: 6px; border: 1px solid #e1e4e8; font-family: 'Courier New', Courier, monospace; overflow-x: auto;"> emp_id emp_name department salary
---------- -------------------- ------------ --------
1 Ali Rezaei IT 75000
2 Sara Mohammadi HR 62000
3 Vahid Karimi Database 90000</pre>

<h3 dir="ltr" lang="en">DML Testing (INSERT) and Synonym Creation:</h3>
<p dir="ltr" lang="en">To allow DML operations, ensure write permissions are granted in SQL Server:</p>

<p dir="ltr" lang="en">Next, create a synonym in Oracle, insert a new record, and commit:</p>

<pre dir="ltr" style="text-align: left; background-color: #f6f8fa; color: #24292e; padding: 12px; border-radius: 6px; border: 1px solid #e1e4e8; font-family: 'Courier New', Courier, monospace; overflow-x: auto;">-- Create Synonym for cleaner SQL syntax
CREATE OR REPLACE SYNONYM mssql_emp FOR "employees"@mssql_link;

-- Insert new record
INSERT INTO mssql_emp ("emp_id", "emp_name", "department", "salary")
VALUES (4, 'Reza Ahmadi', 'DevOps', 88000);

COMMIT;

-- Verify final data
SELECT * FROM mssql_emp;</pre>