« Some Different Electric Bikes | Main | Connecting Oracle 10g on Solaris Sparc 64 to SQL Server on Windows using FREE ODBC Driver - Part Two »

July 22, 2009

Connecting Oracle 10g on Solaris Sparc 64 to SQL Server on Windows using FREE ODBC Driver

Well, a new twist has been added to our production Oracle database. It became necessary to connect our Oracle 10gR2 database up to a Microsoft SQL Server database. The goal was to be able to create a database link in our Oracle database and directly fetch data from the SQL Server database. As we worked through this process we learned that connecting to a "remote" server (that is, each database is on a different server) is a complicating factor. Other complications included: Oracle database is on Solaris 9 - 64 bit. SQL Server database is on a 32 bit Windows OS. The final complicating factor - we needed to use free/open source software - no budget for buying a commercial solution.

Here are the pieces you need to make this work. (Finding this out required a lot of time!)


What we found was that a lot of people have tried to make these connections, and that the documentation was either incomplete or very confusing. Much of the confusion came from understanding when to use 64 bit libraries and when to use 32 bit libraries. Jose at Oracle helped a lot (SR). He modified the listener.ora file to tell the Oracle database to use 32 bit libraries. Once this was in place, then everything else used 32 bit libraries even though it was installed on a 64 bit OS. The 32 bit libraries work just fine on the 64 bit Solaris 9 OS we were using.

I can't say too much about the Windows ODBC setup, except to say it was done pretty quickly on the Windows server, and there was a good amount of supporting documentation that was accurate enough to get that job done. This was set up by Mike Huber and Brian Berkley (our Windows sysadmin). All of our challenges came on the unix/Solaris side.

After installing the odbc driver manager obtained from unixodbc.org, the next step was to configure this to connect with the windows ODBC driver. This should all be using 32 bit libraries (I know this now...). We found that adding freeTDS into the unixodbc driver was necessary to actually make our systems talk to each other. It may be that the freetds portion is not always necessary, but it was fairly easy to install and configure, and we could not get any connectivity without it. Dave Diemer, our unix sysadmin, installed the odbcdriver manager, and freetds for us - this must be done as root. These things want to install in the /usr/bin path, but Dave forced them into a different path in our Oracle area. Dave used a command like this:

It was quite helpful keeping all of this within our Oracle area.

The unixodbc driver uses two config files - odbc.ini and odbcinst.ini
Here's what our files look like:

odbc.ini -

[clmd]
Driver = TDS
Description = clm development
Trace = Yes
TraceFile = /[oracle path]/odbc/tmp/clmd.log
#Servername = winchester.wou.edu
Server = ip.address.of.your.server
Database = [name of database you want to connect to]
Port = 1433

Things to note about this file:
[clmd] - clmd is a common name we used throughout the setup. At first we had CLM_Development. Don't use such a long name, it can cause problems. Just pick something simple. We had to eventually go back through all of our setup and change to the simpler name.
Server = Use the ip address, at least initially. Even though the unixodbc.org instructions say to use "Servername" don't do it! It must say "Server = "
Database = This is the name of the database you want to connect to.
Port = 1433 is the standard port for this sort of thing.

odbcinst.ini -

[TDS]
Description = FreeTDS driver
Driver = /[oracle path]/odbc/lib/libtdsodbc.so
Setup = /[oracle path]/odbc/lib/libtdsodbc.so
Trace = Yes
TraceFile = /[oracle path]/odbc/tmp/freetds.log
FileUsage = 1

Our driver and setup entries refer to the freetds libraries. If you are not using freetds, then these entries point to the odbc libraries for your situation. The libraries come in pairs, for example the MySQL libraries are named libmyodbc3.so and libmyodbc3S.so (The Setup library has the extra "S", the other library is the Driver.)

freetds.conf -


# $Id: freetds.conf,v 1.12 2007/12/25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
; tds version = 4.2

# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff

# Command and connection timeouts
; timeout = 10
; connect timeout = 10

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512

# A typical Sybase server
[egServer50]
host = symachine.domain.com
port = 5000
tds version = 5.0

# A typical Microsoft server
[clmd]
host = your.server.ip.address
port = 1433
tds version = 8.0

The last section is really the only thing we had to configure in this file.

At this point, you should try connecting to the remote database using the odbc drivers you just configured and installed. There is a nifty utility for doing this called "isql". Don't let the name throw you, it is just a utility that comes with the unixodbc install. It is located in the /[oracle path]/odbc/bin folder. You call it like this:

isql -v tds(our situation, use your odbc.ini name) useridtodatabase password;

A successful connection returns a box, praises you for your success and opens a sql session with the remote database. You can then do an appropriate select and it will display the data in a cryptic, command line sort of way. Here's an example:

isql -v clmd sqlserveruserid password
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> select * from school_codes;
+---------------+------------+-------------+-------+---------------------------------------------------+-------------------------------+-------------------+-------------------------------+-------------------------------+-------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+-------------------------+
| SCHOOL_LINK_ID| SCHOOL_CODE| OPEID | CAMPUS| NAME | BANNER_IMAGE | MULTIPLE_ADDRESSES| MAIN_CONTACT | TITLE | ARCHIVE_FLAG| BUTTON_IMAGE | BILL_TEXT | CLOSE_AMT | LONG_AMT | INCLUDE_IN_CLEARINGHOUSE|
+---------------+------------+-------------+-------+---------------------------------------------------+-------------------------------+-------------------+-------------------------------+-------------------------------+-------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+-------------------------+
| 1 | WOU | 00320900 | WO | Western Oregon University | wou.jpg | | Dona Vasas | | N | | | | | N |
+---------------+------------+-------------+-------+---------------------------------------------------+-------------------------------+-------------------+-------------------------------+-------------------------------+-------------+-------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------+-------------+-------------------------+
SQLRowCount returns 1
1 rows fetched
SQL>

At this point you know you have the unix odbc driver and everything on your Windows box configured properly. This is a long process, so I'm going to quit right here. Next time, we'll look at the remaining part - getting the Oracle database to make this connection directly.

One final comment. To make this work, it took quite a number of people from the UCS Department here at WOU. Mike Huber, who is a Banner programmer that needs this functionality. Mike helped in many ways including finding unixodbc.org, freetds, and providing details in the various configuration files. Brian Berkley helped set up the Windows box and database to accept a remote connection from our Oracle database. Dave Diemer did the actual installs and provided the unix guru advice to put the pieces together. Oracle support - Jose Franco. Took our setup, analyzed it, pointed out where we had gone wrong, and actually made it work. Opening an SR (tar) for this was definitely the way to go! There are a lot of pieces that need to fit together just right in order for this to work.

Posted by rossm at July 22, 2009 1:09 PM

Comments

Post a comment

Thanks for signing in, . Now you can comment. (sign out)

(If you haven't left a comment here before, you may need to be approved by the site owner before your comment will appear. Until then, it won't appear on the entry. Thanks for waiting.)


Remember me?