Oracle MS Sql connection

Oracle MS Sql connection

Using Oracle's heterogenous services also known as HS, we can make a connection to non-Oracle databases and access the data remotely. I used MS SQL server as the target non-Oracle database on Windows and used HS to access from Oracle database residing on Linux. Here, we'll see step by step instructions on how to set up a connection from Oracle database to Microsoft SQL server.

In this example, I've used the following:
Source Oracle database on Linux:test-oralin
Target SQL server on Windows:test-mssqlwin
DSN name:MSORA

Our objective is to make a connection from test-oralin to test-mssqlwin and access the SQL server database from Oracle. Here are the steps:

1. On test-mssqlwin, install Oracle Home. Example: E:\OraHome
Note: Only Oracle binaries are needed here so you need not create a database.

2. On test-mssqlwin, install ODBC drivers so that Oracle can access SQL server

3. On test-mssqlwin, configure ODBC by setting up a system DSN(Data Source Name)
Typically you can find this at Start->Settings->Control Panel->Administrative Tools->Data Sources(ODBC) however depending on the Windows version, this may differ slightly.
On System DSN tab, click Add button and then choose the driver you want to use to connect to SQL server. Continue with the ODBC configuration by entering the details of the SQL server you wish to connect to along with necessary credentials. For this test, I've chosen "MSORA" as my DSN name.

4. On test-mssqlwin, setup the heterogenous services init file.
Look for inithsodbc.ora in $ORACLE_HOME/hs/admin/ which in our case would be E:\OraHome\hs\admin. Make a copy of it and rename it to initMSORA.ora. Remember we had chosen "MSORA" as our DSN name. Open the file and enter the DSN name that you just created recently i.e "MSORA". This is how E:\OraHome\hs\admin\initMSORA.ora should look like:
# This is a sample agent init file that contains the HS parameters that are needed for an ODBC Agent.
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MSORA
HS_FDS_TRACE_LEVEL = OFF

5. On test-mssqlwin, configure listener.ora. Take a sample copy from E:\OraHome\hs\admin\listener.ora.sample and copy it to E:\OraHome\network\admin\listener.ora
This is how your E:\OraHome\network\admin\listener.ora should look like:

LISTENER_MSORA =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=test-mssqlwin)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

SID_LIST_LISTENER_MSORA=
(SID_LIST=
(SID_DESC=
(SID_NAME=MSORA)
(ORACLE_HOME = E:\OraHome)
(PROGRAM=hsodbc)
)
)

Note: SID_NAME should be your DSN name. PROGRAM=hsodbc tells Oracle to use heterogenous services. Restart your listener to make sure the settings are in effect.

We have completed our configuration on the target MS SQL server i.e test-mssqlwin. Now let us move to our source Oracle database i.e test-oralin from where we wish to access test-mssqlwin

6. On test-oralin, configure your $ORACLE_HOME/network/admin/tnsnames.ora
MSORA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test-mssqlwin)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = MSORA)
)
(HS=OK)
)
Note:SID should be the DSN name created on test-mssqlwin. PORT should be same as what was configured for the listener on Oracle Home that was installed on test-mssqlwin. HS=OK means Oracle will use heterogenous services.

7. Verify the connection from test-oralin to test-mssqlwin by doing a tnsping MSORA from test-oralin.

8. We are all set now so let us access the SQL server database now from Oracle.
On test-oralin, connect to the Oracle database from where you want to access SQL server database located on test-mssqlwin
Make sure that global_names parameter is set to false
SQL> sho parameter global_names

NAME TYPE VALUE
------------------ ---------------- -------
global_names boolean FALSE

Create a database link:
CREATE DATABASE LINK <> CONNECT TO <> IDENTIFIED BY <> USING '<>';

Example:
SQL> CREATE DATABASE LINK MSORA CONNECT TO REPL IDENTIFIED BY REPL USING 'MSORA';

Database link created.

Note: REPL is a user on SQL server database i.e test-mssqlwin with REPL as it's password. 'MSORA' was given as a connection string in tnsnames.ora. Here, we have chosen MSORA as dblinkname, you could have any name.

We have completed our configuration. You may start accessing the data now.
Example:
SQL> desc emp@msora
Name Null? Type
------------- -------- --------------
EMPID NOT NULL NUMBER(1)
EMPNAME VARCHAR2(60)
CREATED DATE