ODBC connectivity using UNIX ODBC and FreeTDS

This article will show you how you can use establish connectivity in Oracle and MS SQL database. I did my test on Fedora Core 6 with Oracle 10g R2. The solution is for testing purpose only. Please use it at your risk. The DataDirect offer commercial solution for this type of connectivity.

The connectivity between Oracle and MS SQL database is established using unixODBC and FreeTDS.

What you need?

Platform:
Fedora Core 6
Oracle Database 10g R2
MS SQL server 2000 database

Packages:
unixODBC
FreeTDS

Download URL:
http://www.unixodbc.org/
http://freetds.org/

If you want to install unixODBC with GUI option then you will also need Qt package. You can download it at the following link.

http://trolltech.com/developer/downloads/qt/x11

In my setup process, I didn't use GUI to setup DSN. If you don't know what I am talking about the bear with me for next few minutes.

The software version that I used are unixODBC-2.2.12.tar.gz and freetds-stable.tgz.

Setup 1: Install unixODBC package

Install unixODBC first. Download the software in your home directory or anywhere you like and uncompress and untar the software.
You can do this by using following command

# tar -xvzf unixODBC-2.2.12.tar.gz

Once it is done, go to the unixODBCxxxx dir and run the configure command.

# ./configure –prefix=/usr/local –enable-gui=no

Note: If you dont use enable-gui option and dont have Qt package, you will get the error. So make sure you use it if no Qt is installed.

After configure is successfully completed, run the make and then make install commands.

# make

# make install

Step 2: Install freeTDS package

Download the freeTDS package and untar and uncompress it.

# tar -xvzf freetds-stable.tgz

Run the configure.

# ./configure –with-tdsver=8.0 –with-unixODBC=/usr/local

Run the make

# make

Install it

# make install

Step 3: Configuration - freeTDS

Start configuration with freeTDS. Look for the freetds.conf file and add the entry for the MS SQL server.

Sql server standard port is the 1433. The tds version for MS SQL server 2000 is 8. You can try 8.0 with new version as well.

Step 4: Configuration – unixODBC

The unixODBC need two main configuration files called odbcinst.ini and odbc.ini. These files should be in the /etc/ dir.

I. The first file odbcinst.ini contain the definition of ODBC driver.

Note: Before making above entries, make sure libtdsodbc.so exists in the /usr/local/lib dir.

In the above configuration, we have define TDS as driver.

II. The second file is odbc.ini. This file has information about your MS SQL database.

You can notice above that the freeTDS driver is the name defined in odbcinst.ini while servername is the one defined in freetds.conf.

Step 5: Test MS SQL connectivity from Linux box

Check if you can connect to MS SQL database using unixODBC tool called isql.
 

# isql -v mstest satest satest
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from "sysObjects";

If you see the results, you are connected.

Step 6: Configuring Oracle heterogeneous services to work with MS SQL

I. Create init<sid>.ora file

Create a init<sid>.ora file in your $ORACLE_HOME/hs/admin dir. There should be a file called inithsodbc.ora in the directory. Copy this file into a file called initmstest.ora as our sid here is mstest and edit it.

# 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 = MSTEST
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = /tmp/freetds.trc
HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so

#
# ODBC specific environment variables
#
set ODBCINI=/usr/local/etc/odbc.ini

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

Please note that the parameter HS_FDS_CONNECT_INFO should be set to your DSN name.

II. Add listener entry

Open your listener.ora file add new listener entry.

SID_LIST_MSTEST =
(SID_LIST =
(SID_DESC =
(SID_NAME = mstest)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(PROGRAM = hsodbc)
)
)

MSTEST =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
)
)

After this entry is added, start the listener mstest.

# lsnrctl start mstest

III. Create a TNS entry.

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

Test the TNS connecivity using tnsping.

# tnsping mstest

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 29-JUN-2007 14:57:57

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/u01/app/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)) (CONNECT_DATA=(SID=MSTEST)) (HS=OK))
OK (30 msec)

IV. Create the database link in Oracle to connect to MS SQL

Connect to a user in Oracle database and create the database link

SQL> create database link mstest connect to user identified by password using 'MSTEST';

After link is created, you should be able to select data from MS SQL table.

SQL> select * from "sysObects"@mstest;

That is all. If this works, you are done.

Please note that MS SQL table name are case sensitive so make sure you use double quotes around the table/column name.

Troubleshooting:

Error: ORA-28511: lost RPC connection to heterogeneous remote agent using SID=%s

SQL> select * from sysobjects@mstest;
select * from sysobjects@mstest
*
ERROR at line 1:
ORA-02068: following severe error from MSTEST
ORA-28511: lost RPC connection to heterogeneous remote agent using SID=%s
ORA-28509: unable to establish a connection to non-Oracle system

Cause: This error means your library path is incorrect.

Solution: Check the library path in your ODBC.ini file. You make also need to add ENV=LD_LIBRARY_PATH=<odbc_library_file> in your listener.ora file.