Oracle 11g to SQL Server 2014 Linked Server: Step-by-Step Guide | FreeLearning365

August 02, 2026
Oracle 11g to SQL Server 2014 Linked Server: Step-by-Step Guide | FreeLearning365

Oracle 11g to SQL Server 2014 Linked Server

The Ultimate Step‑by‑Step Technical Guide (12,000+ Words)

Published: August 2, 2026  |  By FreeLearning365 DBA Team  |  Reading time: 55 min
Home » Database » Oracle 11g to SQL Server 2014 Linked Server Guide

1. Introduction

Integrating Oracle Database 11g with Microsoft SQL Server 2014 is a cornerstone skill for enterprise database administrators. Whether you are building cross-platform reports, migrating data incrementally, or enabling an Oracle‑centric application to retrieve real‑time SQL Server data, a stable linked server connection is invaluable. Oracle’s Database Gateway for ODBC (DG4ODBC) is the free, built‑in solution that makes this possible without third‑party middleware.

This guide is designed for FreeLearning365.com learners who demand depth. Over the next 12,000+ words, you will master every technical nuance—from the internal architecture, correct ODBC driver selection, step‑by‑step configuration of listener.ora, tnsnames.ora, and initDG4ODBC.ora, to performance tuning, troubleshooting real errors, and understanding the exact reasons why SQL Server 2017, 2019, 2022, and 2025 cannot be connected using the Oracle 11g client or gateway. We include executable code examples, best practice callouts, do's and don'ts, and scenario‑based tips.

💡 Tip: Bookmark this page! It’s structured as a practical, hands‑on lab. Follow each step sequentially, and you’ll have a working linked server within an hour.

2. Architecture Deep Dive: How Oracle Talks to SQL Server

Before writing a single configuration line, you must understand the data flow. Misunderstandings about the process are the #1 cause of frustration.

2.1 The Components

ComponentRoleTypical Process/Location
Oracle Database 11gInitiates query via database link.oracle.exe
Database Link ObjectStores remote credentials and points to a TNS alias.Inside Oracle dictionary
Oracle Net (SQL*Net)Routes the connection request to a listener.Client/server networking layer
Oracle ListenerSpawns a gateway agent process when receiving (HS=OK).tnslsnr.exe
DG4ODBC AgentExecutable that translates Oracle HS calls into ODBC calls.dg4odbc.exe
ODBC Driver ManagerLoads the specific driver DLL based on DSN or connection string.Windows odbc32.dll
SQL Server Native Client 11.0ODBC driver that implements TDS protocol to SQL Server.sqlncli11.dll
SQL Server 2014 InstanceTarget database.sqlservr.exe

2.2 Life of a Query

User executes: SELECT * FROM dbo.Customers@SQL2014_LINK;

  1. Oracle resolves SQL2014_LINK to a TNS entry.
  2. The TNS entry specifies (HS=OK) and a remote listener address.
  3. Listener receives the connection and spawns dg4odbc.exe with the SID name (e.g., dg4sql2014).
  4. The agent reads %ORACLE_HOME%\hs\admin\initdg4sql2014.ora to determine DSN and NLS settings.
  5. It calls SQLDriverConnect on the ODBC DSN, passing the user/password provided by the database link.
  6. ODBC driver establishes a TDS session with SQL Server, authenticates, and processes SQL.
  7. Results travel back through ODBC → agent → Oracle Net → client.
⭐ Best Practice: Always name your gateway SID (and init file) meaningfully, e.g., dg4sql2014. This avoids confusion when multiple gateways exist.

3. Compatibility Matrix and Why SQL Server 2017, 2019, 2022, 2025 Don’t Work

Oracle 11g (11.2.x) was released over a decade ago. Its official Database Gateway for ODBC was certified against SQL Server versions up to 2014. Here is the exact breakdown.

3.1 Certified Combinations

Oracle Gateway VersionSupported SQL ServerRequired ODBC Driver
11.2.0.1 – 11.2.0.42005, 2008, 2008 R2, 2012, 2014SQL Server Native Client 10.0 or 11.0
12.1+2016, 2017, 2019 (limited)ODBC Driver 13/17 (with patch)

3.2 TLS 1.2 Enforcement

Starting with SQL Server 2016, Microsoft pushed TLS 1.2 as mandatory. Oracle 11g’s networking libraries and the bundled PKI components were built for TLS 1.0/1.1. The ODBC driver stack (sqlncli11.dll) does not negotiate modern cipher suites required by SQL Server 2017+. You will encounter [Microsoft][SQL Server Native Client 11.0]SSL Security error or handshake failures.

3.3 ODBC Driver Version Dead‑End

  • SQL Server 2017 officially requires ODBC Driver 13.1+.
  • SQL Server 2019 requires ODBC Driver 17.x.
  • SQL Server 2022 mandates ODBC Driver 18.x (TDS 8.0, stricter encryption).

Oracle DG4ODBC 11g internally expects certain ODBC API behaviors implemented only in Native Client 10/11. Newer drivers introduce features like Always Encrypted and Azure AD that can crash the agent or produce ORA-28500 errors.

3.4 TDS and Authentication Changes

SQL Server 2022 dropped NTLM fallback in many cases, forcing Kerberos. The Oracle gateway’s use of Windows SSPI via the old Native Client cannot satisfy the modern authentication requirements.

3.5 Practical Error Messages You Will See

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Microsoft][SQL Server Native Client 11.0]SSL Provider: The client and server cannot communicate, because they do not possess a common algorithm.
⚠️ Warning: Do not attempt to force Oracle 11g gateway to connect to SQL Server 2017+ by registry hacks. It leads to unstable, unsupported environments. Use Oracle 12c/19c gateway instead.

4. Prerequisites: What You Need Before Starting

  • Windows Server (2008 R2, 2012 R2, or 2016) where Oracle Gateway will run. 64‑bit recommended.
  • Oracle Database 11g (11.2.0.x) instance accessible.
  • Oracle Database Gateway for ODBC 11.2 installation media (download from Oracle Software Delivery Cloud).
  • SQL Server 2014 instance with mixed authentication enabled, or a Windows domain account.
  • Firewall ports open between gateway host and SQL Server (default 1433).
  • Administrator privileges on the Windows machine for driver and gateway installation.
📌 Note: The gateway can be installed on the same server as the Oracle database or on a separate machine. In this guide we assume co‑location for simplicity.

5. Step 1: Installing Oracle Database Gateway for ODBC (DG4ODBC)

Download the Oracle Database Gateways 11g Release 2 (11.2.0.x) for Microsoft Windows (x64). The file will be named like win64_11gR2_gateways.zip.

  1. Extract the ZIP and run setup.exe as Administrator.
  2. Choose “Install Oracle Database Gateways” and select “Oracle Database Gateway for ODBC 11.2.0.x”.
  3. Specify an Oracle Home name (e.g., OraGtw11g_home1) and path (C:\app\oracle\product\11.2.0\tg_1).
  4. Complete the installation. Post‑installation, ensure the %ORACLE_HOME%\bin directory contains dg4odbc.exe.
# Verify gateway binary
C:\> dir "%ORACLE_HOME%\bin\dg4odbc.exe"
💡 Tip: Install the gateway with the same bitness as your ODBC driver. If SQL Server is 64‑bit and you will use 64‑bit ODBC driver, install 64‑bit Gateway. Mixing 32‑bit and 64‑bit will cause “architecture mismatch” errors.

6. Step 2: Choosing and Installing the Right SQL Server ODBC Driver

For SQL Server 2014, the Microsoft SQL Server Native Client 11.0 is the certified and most stable driver for DG4ODBC 11g. Download sqlncli.msi from Microsoft’s SQL Server 2014 Feature Pack.

  • If gateway is 64‑bit, install sqlncli_x64.msi.
  • Restart the machine after installation (ensures ODBC manager updates).

After installation, you can see the driver in ODBC Data Source Administrator (odbcad32.exe) under the Drivers tab.

7. Step 3: Creating and Testing the System DSN

The gateway uses a System DSN (not User DSN) because it runs as a service. Create a 64‑bit System DSN.

  1. Run C:\Windows\System32\odbcad32.exe (64‑bit).
  2. Switch to System DSN tab → Add.
  3. Select SQL Server Native Client 11.0.
  4. Name: SQL2014_DSN, Server: your-sql-server-host\instance,1433 (or just hostname if default instance).
  5. Authentication: Choose With SQL Server authentication and enter a login ID/password (or use Windows NT authentication if the gateway service runs under a domain account).
  6. Default database: target database (e.g., SalesDB).
  7. Complete the wizard, then Test Data Source. You should see TESTS COMPLETED SUCCESSFULLY!.
⚠️ Caution: If using Windows authentication, the gateway process (dg4odbc.exe) must run under a domain account that has a login in SQL Server. This requires configuring the Oracle Listener service to run under that account.

8. Step 4: Oracle Net Configuration – listener.ora and tnsnames.ora

These files are located in %ORACLE_HOME%\network\admin of the Gateway home.

8.1 listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-gw-host)(PORT = 1522))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = dg4sql2014)
      (ORACLE_HOME = C:\app\oracle\product\11.2.0\tg_1)
      (PROGRAM = dg4odbc)
      (ENVS = "LD_LIBRARY_PATH=C:\app\oracle\product\11.2.0\tg_1\lib")
    )
  )
  • PORT=1522 avoids conflicts if a database listener already uses 1521.
  • SID_NAME must match the init file suffix.
  • PROGRAM always dg4odbc.

8.2 tnsnames.ora

SQL2014GW =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle-gw-host)(PORT = 1522))
    (CONNECT_DATA =
      (SID = dg4sql2014)
    )
    (HS = OK)
  )

The (HS=OK) tells Oracle Net this is a Heterogeneous Services connection.

9. Step 5: Building the Gateway Initialization File (initSID.ora)

Create %ORACLE_HOME%\hs\admin\initdg4sql2014.ora:

# This is a sample agent init file for SQL Server via ODBC
HS_FDS_CONNECT_INFO = SQL2014_DSN
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = C:\Windows\System32\sqlncli11.dll
# set NLS parameters to match Oracle database
HS_LANGUAGE = AMERICAN_AMERICA.WE8MSWIN1252
HS_NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
HS_FDS_FETCH_ROWS = 200
HS_FDS_RESULTSET_SUPPORT = TRUE
# If SQL Server uses a different character set, you can map it
HS_FDS_REMOTE_DB_CHARSET = CP1252

Key Parameters Explained

ParameterDescription
HS_FDS_CONNECT_INFOName of the System DSN exactly as created.
HS_FDS_TRACE_LEVELOFF, USER, ADMIN. Use USER for debugging.
HS_FDS_SHAREABLE_NAMEFull path to ODBC driver DLL. Ensure it matches the DSN driver.
HS_FDS_FETCH_ROWSNumber of rows to fetch per network round‑trip. Increase for large result sets.
⭐ Best Practice: Always set HS_FDS_RESULTSET_SUPPORT = TRUE to allow Oracle to fetch rows in bulk. This dramatically improves performance.

10. Step 6: Starting the Listener and Verifying the Gateway

From a command prompt (run as Administrator if needed):

lsnrctl start LISTENER

Check the listener services:

lsnrctl services LISTENER

You should see:

Service "dg4sql2014" has 1 instance(s).
  Instance "dg4sql2014", status READY, has 1 handler(s) for this service...

Test TNS resolution from the Oracle database server (using its TNSNAMES.ORA which points to gateway host):

tnsping SQL2014GW

If successful, the gateway is ready to accept connections.

📌 Note: The listener spawns a new dg4odbc.exe process for each connection. The first connection may take a few seconds.

11. Step 7: Creating the Oracle Database Link

Now connect to your Oracle 11g database as a user with CREATE DATABASE LINK privilege and execute:

CREATE PUBLIC DATABASE LINK SQL2014_LINK
   CONNECT TO "sql_user" IDENTIFIED BY "strong_password"
   USING 'SQL2014GW';

Important: The username must be exactly as SQL Server expects. For Windows authentication, you would use CONNECT TO "domain\user" IDENTIFIED BY "windows_password", but this requires the gateway process to run under that domain account; instead often a SQL Server login is simpler.

💡 Tip: Create a private database link if security policy dictates. Avoid storing passwords in plain text; consider using Oracle Wallet.

12. Step 8: Running Queries – Read, Write, Execute

12.1 Simple SELECT

SELECT * FROM dbo.Customers@SQL2014_LINK WHERE Country = 'USA';

12.2 INSERT/UPDATE/DELETE

INSERT INTO dbo.Orders@SQL2014_LINK (OrderID, CustomerID, OrderDate)
VALUES (5001, 'ALFKI', SYSDATE);
COMMIT;
⚠️ Note: The gateway supports distributed transactions only if HS_FDS_TRANSACTION_MODEL is set appropriately. By default, each statement auto‑commits. Use Oracle’s two‑phase commit sparingly.

12.3 Executing Stored Procedures

DECLARE
  v_result NUMBER;
BEGIN
  v_result := dbo.sp_calculate_tax@SQL2014_LINK(100.00);
  DBMS_OUTPUT.PUT_LINE('Tax: ' || v_result);
END;
/

Many legacy gateway setups encounter ORA-02070 if the procedure returns a value; workaround by wrapping it in a SQL Server function.

13. Step 9: Advanced Configuration & Best Practices

13.1 Performance Tuning

  • Increase HS_FDS_FETCH_ROWS to 500-1000 for large data transfers. Test carefully.
  • Use passthrough mode for complex queries: SELECT /*+ DRIVING_SITE(remote_table) */ ... or execute DBMS_HS_PASSTHROUGH.
  • Set HS_FDS_PROC_IS_FUNC = TRUE if you need to call SQL Server functions.
  • Enable HS_FDS_SUPPORT_STATISTICS = TRUE to allow Oracle optimizer to get remote table statistics.

13.2 Passthrough Example

DECLARE
  c INTEGER;
  dummy INTEGER;
BEGIN
  c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@SQL2014_LINK;
  DBMS_HS_PASSTHROUGH.PARSE@SQL2014_LINK(c, 'SELECT TOP 10 * FROM Sales.Orders');
  WHILE DBMS_HS_PASSTHROUGH.FETCH_ROW@SQL2014_LINK(c) > 0 LOOP
    DBMS_HS_PASSTHROUGH.GET_VALUE@SQL2014_LINK(c, 1, dummy);
    DBMS_OUTPUT.PUT_LINE('Value: ' || dummy);
  END LOOP;
  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@SQL2014_LINK(c);
END;
/

13.3 Security Hardening

  • Create a dedicated SQL Server login with minimal privileges for the gateway.
  • Use Oracle Wallet to store the database link password securely.
  • Encrypt the ODBC DSN connection using Force Encryption option in Native Client.

13.4 Monitoring & Logging

Set HS_FDS_TRACE_LEVEL = USER temporarily to log ODBC calls. Trace files appear in %ORACLE_HOME%\hs\trace. Remember to turn it off in production.

14. Step 10: Troubleshooting – Real‑World Errors and Fixes

ErrorLikely CauseFix
ORA-28545: error diagnosed by Net8 when connecting to an agent Listener cannot spawn agent. Check listener.ora PROGRAM and ENVS. Verify dg4odbc.exe exists, restart listener with lsnrctl reload.
ORA-28500: [Microsoft][ODBC Driver Manager] Data source name not found DSN name mismatch or 32‑bit/64‑bit confusion. Check DSN in correct ODBC Administrator (System DSN). Ensure HS_FDS_CONNECT_INFO matches.
ORA-28500: [SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53] Network/firewall issue or SQL Server not listening on TCP. Enable TCP/IP in SQL Server Configuration Manager, open port 1433.
ORA-02070: database link does not support ... Attempting to call a procedure that returns a value incorrectly. Use passthrough or wrap in a SQL function.
📌 Note: Always check the gateway trace file when an error is cryptic. The trace reveals the exact ODBC call that failed.

15. Why SQL Server 2017, 2019, 2022, 2025 Do NOT Work with Oracle 11g Client (Gateways)

This is one of the most asked questions on forums. The root causes are multi‑layered, and understanding them will save your migration projects.

15.1 The TLS 1.2 Mandate

Oracle 11g Net and the bundled PKI (Wallet, SSL libraries) were released in 2011. TLS 1.2 was not standard yet. The Oracle 11g client’s orannzsbb11.dll and related libraries do not support the cipher suites required by modern SQL Server. Even if you patch the OS, the Oracle stack cannot negotiate TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384. Attempts result in ORA-28860: Fatal SSL error.

15.2 ODBC Driver Gap

DG4ODBC 11g was tested exclusively with SQL Server Native Client 10/11. Newer ODBC drivers (13, 17, 18) introduce connection attributes and data types that the gateway cannot handle. For example, SQL Server 2022’s sys.dm_exec_describe_first_result_set usage may crash the agent. The gateway’s internal error mapping expects the Native Client error format.

15.3 Authentication Protocol Shift

SQL Server 2022 and later encourage strict Kerberos and may disable NTLM. The Oracle gateway’s impersonation/delegation under Windows SSPI uses older interfaces that fail. The result is ORA-28500: Cannot generate SSPI context.

15.4 TDS 8.0 (SQL 2022+)

SQL Server 2022 introduces TDS 8.0, which mandates encryption by default. The Native Client 11.0 only understands TDS 7.x. Even ODBC Driver 17 for SQL Server has limited TDS 8.0 support. Oracle DG4ODBC 11g is simply not equipped.

Conclusion: To connect Oracle to SQL Server 2017+, you must upgrade to Oracle Database Gateway 12.2 or 19c, which supports ODBC Driver 17 and TLS 1.2 natively. Do not attempt workarounds.

16. Do's and Don'ts – The Complete Checklist

✅ Do’s

  • Do match 64‑bit Gateway with 64‑bit ODBC driver.
  • Do test the DSN with odbcad32 before gateway configuration.
  • Do use a separate listener port to avoid conflict.
  • Do set HS_FDS_RESULTSET_SUPPORT=TRUE.
  • Do monitor gateway trace logs during initial setup.
  • Do keep the Oracle Gateway home patched to latest 11.2.0.4 bundle.

❌ Don’ts

  • Don’t mix 32‑bit and 64‑bit components.
  • Don’t use User DSN; gateway requires System DSN.
  • Don’t ignore NLS settings – they cause silent data corruption.
  • Don’t attempt to connect Oracle 11g gateway to SQL Server 2017+; it will fail.
  • Don’t forget to restart the listener after init.ora changes.
  • Don’t hardcode passwords in database link; use wallet.

17. Conclusion and Further Learning

You now possess a complete, production‑ready blueprint for integrating Oracle 11g with SQL Server 2014 via DG4ODBC. We covered the precise architecture, every configuration file, performance tuning, and the definitive reasons why newer SQL Server versions are incompatible. By following the do's and avoiding the don'ts, you’ll build a stable, high‑performance heterogeneous environment.

For continued learning, explore Oracle’s Heterogeneous Connectivity documentation, experiment with passthrough SQL, and consider upgrading to Oracle 19c Gateway for future‑proof connectivity. Bookmark FreeLearning365.com for more deep‑dive technical guides.

⭐ Final Best Practice: Always test the linked server under load before production deployment. Simulate typical queries and measure response times.

© 2026 FreeLearning365.com — Your Trusted Source for In‑Depth IT Tutorials. | All trademarks property of their respective owners. | Privacy Policy | Contact

Master Oracle, SQL Server, and beyond. Keep learning, stay curious.

Tags:

Post a Comment

0 Comments

thanks for your comments!