Connecting Oracle to SQL Server from Windows

Connect Oracle applications on Windows to SQL Server.

You can use Oracle's Database Gateway for ODBC (DG4ODBC) and the SQL Server ODBC Driver to connect Oracle to SQL Server.

DG4ODBC comes as part of Oracle 11g and later, at no additional cost, and is compatible with Oracle 10g and later.

DG4ODBC interacts with Heterogeneous Services (an Oracle database component) to allow Oracle client applications to access non-Oracle databases. The non-Oracle data is transparently integrated, and so Oracle client applications are not aware that the data is stored in a remote database from another vendor

The following instructions show you how to connect Oracle on Windows to SQL Server. For further information about DG4ODBC, refer to our DG4ODBC for Windows tutorials.

  1. Download the SQL Server ODBC driver for your Windows platform. (Registration required.)
  2. Install and license the SQL Server ODBC driver on the Windows machine where DG4ODBC is installed.

    For installation instructions, see the SQL Server ODBC driver documentation.

  3. In ODBC Data Source Administrator on your DG4ODBC machine, configure a System DSN that connects to your SQL Server instance.

    For instructions on configuring data sources, see the SQL Server ODBC driver documentation.

    64-bit Windows You need to check whether your version of DG4ODBC is 32-bit or 64-bit. To do this, start the Windows Task Manager and choose the Processes tab. In a Command Prompt window, type dg4odbc --help. In the Windows Task Manager, look for the DG4ODBC process. If the Image Name is "dg4odbc.exe *32" DG4ODBC is 32-bit. If the Image Name is "dg4odbc.exe" DG4ODBC is 64-bit. Press CTRL+C in the Command Prompt window, when you have used the Windows Task Manager to find out DG4ODBC's architecture.

    If you have the 64-bit version of DG4ODBC, you need to run 64-bit version of ODBC Administrator. To do this, open Administrative Tools in Control Panel, and then open Data Sources (ODBC). (On Windows Server 2003 and earlier, the Control Panel applet that launches ODBC Administrator is labelled Data Sources. On Windows 8 and later, the Control Panel applet is labelled ODBC Data Sources (64-bit).)

    If you have the 32-bit version of DG4ODBC, you need to run 32-bit version of ODBC Administrator. To do this, in the Windows Run dialog box, type:

    %windir%\syswow64\odbcad32.exe
  4. Create a DG4ODBC init file. To do this, change to the %ORACLE_HOME%\hs\admin directory. Create a copy of the file initdg4odbc.ora. Name the new file initmssql.ora.

    Note In these instructions, replace %ORACLE_HOME% with the location of your Oracle HOME directory. For example, C:\oraclexe\app\oracle\product\11.2.0\server.

  5. Ensure these parameters and values are present in your init file: 
    HS_FDS_CONNECT_INFO = my_sqlserver_odbc_dsn;
  6. Add an entry to %ORACLE_HOME%\network\admin\listener.ora that creates a SID_NAME for DG4ODBC. For example: 
    SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC=
     (SID_NAME=mssql)
     (ORACLE_HOME=%ORACLE_HOME%)
     (PROGRAM=dg4odbc)
   )
 )
  7. Add a DG4ODBC entry to %ORACLE_HOME%\network\admin\tnsnames.ora that specifies the SID_NAME created in the previous step. For example: 
    MSSQL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle_host)(PORT = 1521))
    (CONNECT_DATA =
      (SID = mssql)
    )
    (HS = OK)
  )

    Replace oracle_host with the host name of your Oracle machine.

  8. Start (or restart) the Oracle Listener: 
    cd %ORACLE_HOME%\bin
lsnrctl stop
lsnrctl start
  9. Connect to your Oracle database in SQL*Plus.
  10. In SQL*Plus, create a database link for the target SQL Server instance. For example: 
    CREATE PUBLIC DATABASE LINK mssqllink CONNECT TO
"my_sqlserver_user" IDENTIFIED by "my_sqlserver_password" USING 'mssql';

    Replace my_sqlserver_user and my_sqlserver_password with a valid username and password for the target SQL Server instance.

Notes

Problems Accessing Your Data

If you run into any issues trying to read / write data to SQL Server from Oracle please do the following: