Install Oracle ODBC on Solaris(Unix)_Connect Oracle_to_SQL_Server

I’ve searched a lot of software for to connect Oracle and SQL Server databases. There are a lot of software, but I couldn’t use any of them except DataDirect ODBC. Unfortunately, this soft is not free. I couldn’t find any free software that works on unix.

Let’s start installation.

1. Download DataDirect ODBC driver from here.

Choose your appropriate system(64-bit UNIX/Linux, 32-bit UNIX/Linux,…). After you choose it, there will appear Registration region. Fill it with your information and when you see the following section, do as it is shown on the picture:

DataDirect Registration Region

I chose Oracle, SQL Server and Solaris SPARC…Because I am trying to connect Oracle Database to SQL Server Database, note Oracle Database is installed on Solaris SPARC.

2.  Extract Downloaded file:

# gunzip PROGRESS_DATADIRECT_CONNECT_ODBC_6.1.1_SOL_32.tar.Z

# tar -xvf PROGRESS_DATADIRECT_CONNECT_ODBC_6.1.1_SOL_32.tar

3. Go to the extracted file and run the following:

# ./unixmi.ksh
Installation……………………………………………………………………………

Solaris
Is this the current operating system on your machine (Y/N) ? [Y] PRESS ENTER

READ THE LICENSE TERMS(PRESS ENTER to read whole content)

…

EULA - Connect Products (revised 2/21/11)

Enter YES to accept the above agreement : YES

…

In the Key field, enter either EVAL or the Key provided.

Fill it with your registration information. I am writing EVAL because I just want to try it.

Name          :Mariam
Company       :JSC Bank of Georgia
Serial Number :EVAL
Key           :EVAL

…

1.   Drivers for All Supported Databases
2.   Single Driver
Please enter an option [1]: 2

1.   DB2 Wire Protocol
2.   dBASE
3.   Greenplum Wire Protocol
4.   Informix Wire Protocol
5.   MySQL Wire Protocol
6.   Oracle Wire Protocol
7.   PostgreSQL Wire Protocol
8.   SQL Server Wire Protocol
9.   Sybase Wire Protocol
10.  Teradata
11.  Text
12.  Informix (client)
13.  Oracle (client)
14.  SQL Server Legacy Wire Protocol

Please enter driver option: 8

To change this information, enter C. Otherwise, press Enter to continue. : PRESS ENTER
Enter the full path to the temporary install directory.[/tmp]:PRESS ENTER
Enter the full path to the install directory.[/opt/Progress/DataDirect/Connect_for_ODBC_61]: PRESS ENTER
Would you like to install another product (Y/N) ? [Y] N

…

Installation ended successfully.

3. Configure odbc.ini file, located in /opt/Progress/DataDirect/Connect_for_ODBC_61.

There will be written some information when you open this file, but the following entries are enough:

[MS_SQLServer2005]
Driver=/opt/Progress/DataDirect/Connect_for_ODBC_61/lib/ivsqls25.so
Description=SQL Server ODBC driver
Database=DB_NAME
Address=10.0.0.88,1433
Quoteld=No

Note I have changed the following values:

MS_SQLServer2000 – is your desired name, name it as you wish.(Will be used later)
Description – Just a descriptive text, write what you wish.
Database – Name of the SQL Server Database.
Address – Ip address of the server on which SQL Server Database is installed. 1433 is a default port for SQL Server.  Ensure that this port is opened on  firewall(Or you will not  be able to connect SQL Server)

4. Tnsnames.ora

DB_NAME_ALIAS=  
   (DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.11)(PORT=1522))
     (CONNECT_DATA=(sid=DB_NAME))
     (HS=OK)
  )

Where,

DB_NAME_ALIAS – is your desired name.
DB_NAME – Is SQL Server Database name
1522 – Is any free port, If you have multiple listeners on the server(where Oracle is installed) choose any port that is available.
10.0.0.11 – Ip of the server, where Oracle is installed

5. listener.ora

LISTENER_DB_NAME_ALIAS = 
  (DESCRIPTION_LIST=
     (DESCRIPTION =
       (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = tcp)(HOST = 10.0.0.11)(PORT = 1522))
       )
     ) 
  ) 

SID_LIST_LISTENER_DB_NAME_ALIAS=
   (SID_LIST=
     (SID_DESC=
     (SID_NAME=DB_NAME_ALIAS)
     (ORACLE_HOME=/u0/app/oracle/10.2.0/db)
     (PROGRAM= hsodbc)
     (ENVS="LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect_for_ODBC_61/lib:/u0/app/oracle/10.2.0/db/bin")
    )
)

Underlined entry is very important, or you will get  ORA-28500 error.

6.  Go to  the following directory: $ORACLE_HOME/hs/admin

There will be the file called inithsodbc.ora, copy that file and rename it like this: initDB_NAME_ALIAS.ora

# 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 = MS_SQLServer2005
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = hsMSSqlMari.trc
HS_FDS_SHAREABLE_NAME=/opt/Progress/DataDirect/Connect_for_ODBC_61/lib/libodbc.so
set ODBCINI=/opt/Progress/DataDirect/Connect_for_ODBC_61/odbc.ini
#
# Environment variables required for the non-Oracle system
#

7.  Start the listener.

Before starting listener, ensure that ODBC lib directory is specified in the shared library environment variable:

LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect_for_ODBC_61/lib:/u0/app/oracle/10.2.0/db/bin

After that we can start listener:

$ lsnrctl start LISTENER_DB_NAME_ALIAS

8. Create database link:

SQL> create database link LINK_NAME     
     connect to “username”     
     identified by “password”     
     using ‘DB_NAME_ALIAS’;

Where,

username– is the user in SQL Server Database.
password – is its password

Do not forget to use quotas(“”).

9. Check if it works

SQL> select * from dual@LINK_NAME;

D
---
X

That’s it Smile

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