Install Oracle ODBC on Solaris(Unix)_Connect Oracle_to_SQL_Server
May 17, 2011 Leave a comment
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:
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 
About Mariami Kupatadze
DBA Knowledge Base 