Connecting Oracle to sql server

Connect Oracle to another DB

This document will cover the use of Generic Connectivity which permits to connect oracle to a generic DB.
The main requirement is the availability of an odbc driver.

It is quite easy to find an ODBC on a windows system, not so much on a unix.

Connecting to a sqlserver 2000

I need a practical example, then I’ll be able to generalize for other systems: then I’m going to start from a linux (SLES9), oracle10g and a sqlserver.
The odbc selected is unixODBC (already included in SLES9) even if it is not enough for a sql server connection.
We need another component to include into the ODBC the TDS protocol (the one used to communicate with MSSQL).
This time the product is freeTDS. The version *has* to be 0.63 or later due to a bug fix that will solve the following kind of error:

ERROR at line 1:
ORA-00942: table or view does not exist
[Generic Connectivity Using ODBC]DRV_DescribeTable: Record Region has no
fields. Loading failed
ORA-02063: preceding 2 lines from SQLSERVER

The main steps will be three:

installing and configuring the ODBC;
configuring the oracle Heterogeneous Service;
setting oracle DB.

Every step will include a short troubleshooting session.

Installing and configuring the ODBC:

The installation part of unixODBC is easy. You can perform it with the rpm command or yast.

rpm -Uvh unixODBC-2.2.8.rpm

Now you can install freeTDS. You can find the patched version here.

tar xvfz
cd
./configure
make
make install

By default the configuration files are placed in /usr/local/etc.

I prefer /etc/freeTDS so the compilation will change in this fashion:

The load the shared library with ldconfig.
If needed insert /usr/local/bin (or whatsoever) in your PATH.

Now we are ready to configure.
Start with freeTDS. The config file is freetds.conf.
The basic syntax is easy.
Simply add something like this:

[test2k]
        host = 192.168.91.39
        port = 1433
        tds version = 8.0

Sql server standard port is the 1433 while tds version change for every MSSQL version. The 8.0 is the right one for sqlserver 2000 while for sqlserver 7 you need the value 7.0.

The second configuration file is locales.conf but it is helpful only if you wish to change the national settings.

Now unixODBC.
The main configuration files are odbcinst.ini and odbc.ini.

The first one contains the definition of the drivers used by the ODBC. For us:

[FreeTDS]
Description             = v0.63 (develop)
Driver              = /usr/local/lib/libtdsodbc.so
Setup                   = /usr/local/lib/libtdsodbc.so
FileUsage               = 1
Trace                   = Yes
TraceFile               = /tmp/sql.log

[ODBC]
Trace                   = Yes
TraceFile               = /tmp/sql.log
ForceTrace              = Yes
Pooling                 = No

Driver and Setup can change if you installed freeTDS in a non-default directory.
Locate libtdsodbc.so should show you where the library is installed.

Now unixODBC knows the TDS driver with the name: freeTDS.

You are ready for the last configuration file: odbc.ini

[ODBC Data Sources]
sqltest = Sqlserver 2000 connection (Northwind)

[sqltest]
Driver      = FreeTDS
Description = Test Server
Trace       = Yes
Database    = Northwind
TraceFile   = /tmp/sql.log
Servername = test2k
Port        = 1433

Driver is the name defined in odbcinst.ini while servername is the one defined in freetds.conf (port shouldn’t be necessary).

Let’s try if everything is right.

FreeTDS has a command line tool called tsql which can be used for debugging.

# tsql -S test2k -U sa
locale is “en_US.UTF-8”
locale charset is “UTF-8”
Password:
Msg 5703, Level 0, State 1, Server AGONY2, Line 0
Changed language setting to us_english.
1> use Northwind
2> go
1> select * from Region
2> go
RegionID        RegionDescription
1       Eastern
2       Western
3       Northern
4       Southern
1>

With this tool you have to use the name defined in freetds.conf to connect, otherwise you can bypass the configuration file specifying the host and port:

# tsql -H 192.168.91.39 -p 1433 -U sa
locale is “en_US.UTF-8”
locale charset is “UTF-8”
Password:
1>

Remember that sql server is key-sensitive so “region” is not the same as “Region”.

For unixODBC you have another tool called isql:

Heterogeneous Service

It is now time to configure your “gateway”.

You need to modify three file: tnsnames.ora, listener.ora and init<SID>.ora
Go in ORACLE_HOME/hs/admin and create a file called init<sql server SID>.ora
The sql server sid is a name you can invent but it should later match the one you will use in tnsnames.ora and listener.ora.
The file should contains:

<>#
# HS init parameters
#
HS_FDS_CONNECT_INFO = sqltest
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = 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>

The parameter HS_FDS_CONNECT_INFO must match the DNS names of odbc.ini.
Trace level and trace file are there for debugging purpose only.

Creating a gateway

Id’ like to generalize a little bit.
So far we have considered to implement a generic connectivity using a HS configured on the oracle DB.
Not always this is possible or is a good idea. For example, a linux previous 10g is not able to use HS.
Another case is where more oracle DBs had to connect to the same sql server, mysql, DB2 or pool of different DBs.
What to do? We need a configuration for every remote DB on every oracle?

We can think to create a gateway based on HS.

You need: an oracle release which support HS, the right ODBCs for every connection.
On this machine you will configure the HS for as many connectivity you need using the steps showed above.
Every DB which wishes to connect to a remote non-oracle database will connect throught the gateway we set up via the usual sqlnet protocol.
You only need one or more entry in your tnsnames!
Nothing other.

It means you can use a windows system with oracle installed and the original MS ODBC for sql server as your gateway.

Note: the gateway doesn’t need a DB but only the oracle engine. You should be able to install it a no cost because you pay the license only for the DB not for the connectivity.

Troubleshooting:

If you got similar errors:

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

The main problem could be you didn’t add unixODBC directory to your LD_LIBRARY_PATH.
Another chance: you have something wrong in your listener.ora: again ENV=LD_LIBRARY_PATH or PROGRAM.

Look above for a sample configuration.