Connecting Oracle to SQL Server from Windows

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 tutorial.

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

   For installation instructions, refer to 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, refer to the SQL Server ODBC driver documentation.

   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, enter 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 Data Source 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 Data Source 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 Data Source Administrator. To do this, in the Windows Run dialog box, enter:

   %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 user name and password for the target SQL Server instance.

Notes

• If you have problems connecting to SQL Server from Oracle, enable DG4ODBC tracing and check the trace files written to the %ORACLE_HOME%\hs\trace directory. To enable DG4ODBC tracing, add the line HS_FDS_TRACE_LEVEL = DEBUG to initmssql.ora and then start or restart the Oracle listener. If the trace directory does not exist, create it.
• If you enable ODBC Driver Manager tracing, but do not get a trace file or get an empty trace file, change the trace file location to the Windows TEMP directory. For example, C:\Windows\Temp\SQL.log.

Problems accessing your data

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

• Make sure the issue is not with the application you are using, for example SQL Developer, Toad and so on. Test the issue using SQL*Plus on the Oracle machine. If the issue only happens for example under Toad and not in SQL*Plus, report the issue to the people that support Toad.
• Try to limit the issue to the problem column or table. Let's say, for example, you're running a SELECT * FROM table@link and you have only 1 column that is causing the problem, try running select column from table@link to check if that gives the same error. This helps us to diagnose the issue.
• If you need to contact Easysoft Support with a DG4ODBC problem:
  1. Turn Dg4ODBC tracing on within your $ORACLE_HOME/hs/admin/initmssql.ora file:

     HS_FDS_TRACE_LEVEL = Debug

  2. Stop and start your Oracle listener.
  3. Reproduce the issue in SQL*Plus. If you do not get an Oracle trace file in your $ORACLE_HOME/hs/log folder, your listener has not been restarted or Oracle has not been configured correctly.
  4. Send the following to the Easysoft Support team:
     • Output showing the login to SQL*Plus, the query being executed and the error displayed.
     • A copy of your Oracle initmssql log file. Zip or compress this file.
     • A copy of your initmssql.ora file.
     • A copy of the files ending in _install.info from /usr/local/easysoft.