Oracle Heterogeneous Services(hsODBC)

Heterogeneous Services provide the ability to communicate with non-Oracle databases and services.

I will discuss, how to communicate with SQL Server 2005 from Oracle.

1. First of all, you should have a trusted username in SQL Server 2005 to connect to the SQL database.

If you don’t have it, do the following:

1.1 Connect to the SQL Server 2005 database.
1.2 In the Object Explorer, expand the Security folder, then Logins and create new login.

New Login...(Sql Server 2005)

1.3 In General, type Login name, choose SQL Server authentication, type password for user, uncheck User must change password at next login, choose English in Default language.

Login_New(SQL Server 2005)

1.4 In the Server Roles, public and sysadmin roles should be checked.

Login Properties_Server Roles(SQL Server 2005)

1.5 In the User Mapping, check the database for which this user will be owner(Note that this database should be the database from where you want to query data).

Login_Properties_User_Mapping(SQL Server 2005)

1.6 In Status.

Login_Properties_Status

1.7 Right click on the server in the Object Explorer window, choose properties, in Security, choose SQL Server and Windows Authentication mode.

Server Properties_Sequrity(SQL Server 2005)

1.8 Reload the server.

2. Now, when you already have a trusted user in SQL Server 2005. Let’s install ODBC driver for sql server on the computer where Oracle is installed.

2.1 Start -> Control Panel -> Administrative Tools -> Data Sources (ODBC), go to System DSN tab, click Add button.

ODBC Data Source Administrator_System DSN

2.2 Choose SQL Server, click Finish.

Create New Data Source

2.3 In the Name field, type some name, we will use it later as a SID_NAME. In Server field, type the IP of the server, where SQL Server 2005 database is installed.

Create a New Data Source to SQL Server(Data Source)

2.4 Choose second radio button, and type Login ID and Password for the user, which is a trusted user in SQL Server 2005(We have created it before, section 1).

Create a New Data Source to SQL Server

2.5 Check Change the default database to, and choose your database(Note this should be the database from where you want to query data).

Microsoft_SQL_Server_DNS_Configuration

2.6 Leave default settings.

Microsoft_SQL_Server_DNS_Configuration_2

2.7  This window shows you the summary, click Test Data Source button.

ODBC Microsoft SQL Server Setup

2.8 If you have indicated the correct settings, it should be successful.

SQL Server ODBC Data Source Test

3. Now, it’s time to configure in Oracle the following files: listener.ora, tnsnames.ora and %ORACLE_HOME%\hs\admin\inithsodbc.ora.

3.1 In listener.ora file add the following entries:

listenerradiustosql =
    (ADDRESS_LIST=
        (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
         (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
)
SID_LIST_listenerradiustosql=
(SID_LIST=
   (SID_DESC=
     (SID_NAME=radiustosql)
     (ORACLE_HOME = C:\oracle\product\10.2.0\db_1)--Your oracle home
     (PROGRAM=hsodbc)
    )
)

Note: HOST must be localhost(127.0.0.1) not the computer IP, on where oracle is installed.
PORT is non-default port 1522, or choose the port which is free.
You should have two different listeners, one for Oracle itself and other one for radiustosql.

3.2 In tnsnames.ora file add the following entries:

radiustosql  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
    (CONNECT_DATA=(SID=radiustosql))
    (HS=OK)
  )

3.3  Rename %ORACLE_HOME%\hs\admin\inithsodbc.ora to %ORACLE_HOME%\hs\admin\initradiustosql.ora and change its content to the following:

# 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 = radiustosql
HS_FDS_TRACE_LEVEL = OFF

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

3.4 Reload both Listeners: Oracle listener and newly created listenerradiustosql.

–To start default listener

>lsnrctl stop
>lsnrctl start

–To start non-default listener

>lsnrctl stop listenerradiustosql
>lsnrctl start listenerradiustosql

4. Connect to Oracle database and create database link.

4.1
–To create database link

create database link radiustosql
connect to m identified by itsPassword using 'radiustosql'

–To check if it works, run the following:

SELECT *
FROM dual@radiustosql

–Output

DUMMY
--------
X
Advertisements

One Response to Oracle Heterogeneous Services(hsODBC)

  1. Everything is very open with a really clear description of the challenges.
    It was really informative. Your site is very useful.
    Many thanks for sharing!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: