Creating a Database link from Oracle to Microsoft SQL
Server
Created By: Torti Ama-Njoku
Date Created: 25 October 2010
Version Number: 2.0
1|Page
Table of Contents
1 Introduction............................................................................................................................................2
2 Objective................................................................................................................................................3
3 System Pre-requirements.......................................................................................................................3
4 Configuration.........................................................................................................................................3
4.1 STEP 1: Setting up Data Source on Windows...............................................................................3
4.2 STEP 2: Configuration for Server X..............................................................................................9
4.3 STEP 3: Configuration for Machine C..........................................................................................9
4.4 STEP 4: Restart the listener on Server X.......................................................................................9
4.5 STEP 5: Create a database link AND test the link.......................................................................10
2|Page
1 Introduction
Heterogeneous Services is an integrated component within the Oracle database server. It
allows transparent SQL access from an Oracle client to non-Oracle systems as if the non-
Oracle system was an Oracle database. This solution helps eradicate the problem of data
duplication and redundancy in data.
Important HSODBC is a normally a 32-bit application even when distributed with a 64-bit
version of Oracle.
2 Objective
This document is to help create a connection/database link from an oracle database to
Microsoft SQL Server Database.
3 System Pre-requirements
Assuming the MS SQL Server host machine is called X and the client machine that wishes to
access the database on X is called C (Oracle client this could be a linux or windows
machine).
(NOTE: X and C may be the same machine)
On X:
Make sure X can ping C. If not resolve network issues and certain you can ping across
HSODBC agent must be installed on machine X, this can be verified by going to
ORACLE_HOME\bin folder and check if hsodbc.exe exists. Note that for 11g
installations hsodbc.exe is no longer in use, and what is now in use is dg4odbc.exe. These
would usually come with oracle 10g/11g if installed; if the users will not access Oracle
directly from this server the code tree is perfectly acceptable. Oracle XE comes with the
oracle listener and hsodbc/dg4odbc client needed, this would be sufficient.
SQL Server ODBC Driver should be installed and running
Set up an ODBC connection using the Windows ODBC Data Source Administrator and
test the connection to make sure it is connected this will be explained step by step in
section 5 of this document.
4 Configuration
4.1 STEP 1: Setting up Data Source on Windows
Follow the following link:
Control Panel Administrative Tools Data Sources (ODBC)
You will see this screen below:
3|Page
Then select the System DSN tab:
4|Page
Click on the “Add” button:
Select SQL Server driver or SQL Server Native Client if the version of SQL Server being
used is SQL Server 2008.
Enter a Data Source Name (DSN) of your choice you would use to reference the database
5|Page
Enter a description for this DSN. This is optional.
Select the appropriate Server to connect to.
Click next.
Use windows nt authentication if access to the remote database is by using the default
windows authentication then click next or if there is a server authentication needed, select the
server authentication radiobutton, and enter the login details is the field provided and as well
the password and click next. If there is more than one database running on that MS SQL
Server, then make sure to change the default database to use in the screen below.
6|Page
You may change the default database you would like the DSN to point to, or you may leave
it unchecked and click next.
Click finish.
7|Page
Click “Test Data Source” button and the following output should appear to show successful
connectivity:
8|Page
Now you are ready for configurations.
4.2 STEP 2: Configuration for Server X
You would need to configure the Heterogeneous services on this machine.
Create an init<DSN>.ora file. Since X would most likely be a windows platform, you would
need to create the file in this folder: $ORACLE_HOME\hs\admin
The file would have the following code:
HS_FDS_CONNECT_INFO = <DSN> (Replace DSN with the name used in Step 1)
HS_FDS_TRACE_LEVEL = 0
Modify the listener.ora file found in this folder: $ORACLE_HOME\NETWORK\ADMIN
Append the following code to the the SID_LIST:
(SID_DESC =
(SID_NAME=<DSN>)
(ORACLE_HOME=<$ORACLE_HOME>)
(PROGRAM=<hsodbc/dg4odbc>)
(ENVS= (LD_LIBRARY_PATH=E:\WINDOWS\system32;<$ORACLE_HOME>\
BIN))
)
NOTE: Make sure to replace ORACLE_HOME with the path to your oracle db home
and depending on the DB version installed on machine X, use hsodbc for 10g and
dg4odbc for 11g.
4.3 STEP 3: Configuration for Machine C
On the client in the $ORACLE_HOME\network\admin if C is a linux machine, then the
folder would most likely be $ORACLE_HOME/network/admin:
Add this to your tnsnames.ora file:
<Connect Identifier> =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST=<X>)(PORT = <listener port no. on X>)
)
)
(CONNECT_DATA =
(SID=<DSN>)
)
(HS=OK) –This tells oracle to use heterogeneous services
)
9|Page
4.4 STEP 4: Restart the listener on Server X
Execute the following commands on windows cmd:
lsnrctl reload
OR
lsnrctl stop (followed by) lsnrctl start
Alternatively, use the Services GUI in:
Control Panel Administrative Tools Services
Then find the applicable oracle 11g TNS Listener services.
4.5 STEP 5: Create a database link AND test the link
Logged in to the database you would like to link with the SQL Server database on X run the
following SQL Command to create a database link:
CREATE [public] DATABASE LINK <link_name>
CONNECT TO "<MSSQL Database user_name>"
IDENTIFIED BY "<MSSQL Database password>"
using '<Connect Identifier created above>';
Then test the link by:
SELECT * FROM <table_name>@<link_name>;
Something to NOTE:
You can call SQL Server stored procedures, but will not receive output on the calling client.
i.e. If you have a stored procedure that executes a select statement, the procedure would
execute, but no output would be received. But procedures that do updates would execute as
well. For running an MS SQL Server stored procedure:
DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@<link_name>('EXEC
<proceedure_name> [parameter_1] [parameter_2] ...
[parameter_n]');
4.6 Some Specific Information for the LITS Server
ORACLE Version installed on LITS server 11g
ORACLE_HOME G:\app\litsdvscluster\product\11.2.0\dbhome_1
SQL SERVER USERNAME LAC2
10 | P a g e
SQL SERVER PASSWORD Password1234#
SQL SERVER NAME LITSSQLSERVER
TNS Listener name LITSLISTENER
11 | P a g e
