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.
- Download the SQL Server ODBC driver for your Windows platform. (Registration required.)
- 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.
- 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
- 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.
- Ensure these parameters and values are present in your init file:
HS_FDS_CONNECT_INFO = my_sqlserver_odbc_dsn;
- 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) ) )
- 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.
- Start (or restart) the Oracle Listener:
cd %ORACLE_HOME%\bin lsnrctl stop lsnrctl start
- Connect to your Oracle database in SQL*Plus.
- 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
- 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 / 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 / write data to SQL Server from Oracle please 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 SQLPlus on the Oracle machine. If the issue only happens for example under Toad and not in SQLPlus, please report the issue to the people that support Toad.
- Try to limit the issue to the problem column / table. Let's say, for example, you are running a
select * from table@link
and you have only 1 column that is causing the problem, try runningselect column from table@link
and see if that gives the same error. This helps us to diagnose the issue. - If you need to contact Easysoft Support with a DG4ODBC problem:
- Turn Dg4ODBC tracing on within your $ORACLE_HOME/hs/admin/initmssql.ora file:
HS_FDS_TRACE_LEVEL = Debug
- Stop and start your Oracle listener.
- 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.
- Send the Easysoft support team (support@easysoft.com):
- Output showing the login to SQLPlus, the query being executed and the error displayed.
- A copy of your Oracle initmssql log file. Please zip / compress this file if more than 1 MB.
- A copy of your initmssql.ora file.
- A copy of the files ending in _install.info from /usr/local/easysoft. Once we have all items, the Easysoft support team should be able to quickly work out if this is an Oracle configuration issue / bug in Oracle, Easysoft configuration / bug or if we simply need more information.
- Turn Dg4ODBC tracing on within your $ORACLE_HOME/hs/admin/initmssql.ora file: