Introduction
In this blogpost I will explain how you can setup Oracle Database Gateway for ODBC (=DG4ODBC) on a linux host to connect from an Oracle Database to a Microsoft SQL Server. To achieve this we will setup the unixODBC driver manager and use the freetds driver.
Overview of the solution.
The solution
Pre-requisites
Working databases
It is not the purpose of this blogpost to tell you how to install an Oracle or MS SQL Database. We will therefore only explain the setup of the DG4ODBC.
A (virtual) machine on which you can install DG4ODBC.
A (virtual) machine with a linux OS and a 64 bit architecture is required. I used an Oracle Linux 6.3 (64-bit) OS but any Red Hat (derivative) should suffice. The installation of the OS is rather basic. On the hard disk I only created 1 EXT4 partition and 1 swap partion. I chose 1 CPU with 2 cores and 2GiB of RAM.
Installed packages
I just did a server installation (no desktop environment). After the installation I installed the oracle-rdbms-server-11gR2-preinstall package (see https://blogs.oracle.com/linux/entry/oracle_rdbms_server_11gr2_pre for more information). This package is installed because I use this VM as a template to create test machines. It is not a requirement for the setup.
Actually if you want to install the package you can execute the yum statement below. Besides installing the packages the package also sets OS parameter to tune the OS for running a database.
yum install cloog-ppl compat-libcap1 compat-libstdc++-33 cpp gcc gcc-c++ glibc-devel glibc-headers
kernel-uek-headers ksh libXmu libXxf86dga libXxf86misc libaio-devel libdmx libstdc++-devel mpfr ppl
xorg-x11-utils xorg-x11-xauth glibc glibc-common
disable selinux
Just to be sure that selinux doesn't spoil the fun. I will disable it:
[root@gwexample ~]# cat /etc/sysconfig/selinux
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these two values:
# targeted - Targeted processes are protected,
# mls - Multi Level Security protection.
SELINUXTYPE=targeted
Step 1: Make it possible to connect to MS SQL server from Linux
Install the unixODBC Driver manager.
As unixODBC is provided in the public-yum repositories of Oracle we can make our life easy and install the software from the repo.
yum install unixODBC unixODBC-devel
Do not forget the devel package, it contains files like sql.h which are necessary to compile freetds with support for unixODBC!
Installation of freetds
Download freetds
Download the latest version of freetds. I downloaded the freetds-stable.tgz. We will copy this to /tmp/freetds-stable.tgz. Execute the following to extract it and move it to /home/oracle.
#As user oracle
cd /tmp
gunzip -c freetds-stable.tgz | tar -xvf -
mv freetds-0.91/ /home/oracle/
Configure and build freetds
Now we need to configure it:
#As user oracle
cd /home/oracle/freetds-0.91
./configure --prefix=/usr/local/freetds --with-tdsver=8.0 --with-unixodbc=/usr
If you get an error about missing sql.h. It is most likely because unixODBC is installed on a different location. Do a search for the file (either `locate sql.h` or if locate is not available `find / -name "sql.h"`). If the location is /aa/bbb/c/include/sql.h the configure argument should be --with-unixodbc=/aa/bbb/c . So for my setup it was /usr/include/sql.h
Next build the source
#as user oracle
make
And finally install it
#as root
make install
Configuring unixODBC driver manager
Setup the content of /etc/odbc.ini
[ODBC Data Sources]
#I do not think this line is really necessary but it does no harm :-)
MSSQLVM=FreeTDS
#The name between square brackets should correspond to the DB name of your SQL server.
#From now on it will be referred to as DSN !!
[MSSQLVM]
Driver = /usr/local/freetds/lib/libtdsodbc.so
#Do not use Driver = /usr/lib64/libodbc.so
#Location of the MS SQL Server (ip or hostname)
Server = 172.16.58.130
#Port where MS SQL Server is listening
Port = 1433
TDS_Version = 8.0
TraceFile = /tmp/odbc.trace
DEBUG = 1
Trace = Yes
dump file = /tmp/freetds.log
The following should be checked:
- You should check if the file /usr/local/freetds/lib/libtdsodbc.so exists, because without it it won't work!
- If you use a hostname to locate the server, make sure it is resolvable. Try to do `ping ` and see if you can reach the server. (If not try `ping ip` , if that doesn't work ICMP might be disabled). As I use a simple setup with VMs and no DNS server I use /etc/hosts file of linux to use the hostname.
Verify if freetds can be used via odbc
isql -v
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
For example isql -v MSSQLVM test Welcome1
Setup Oracle DB gateway for ODBC
Download software
The Oracle DB gateway for ODBC can be downloaded separately if you have got an Oracle Support Account. Instructions are available at https://updates.oracle.com/Orion/Services/download?type=readme&aru=14125322#CHDIHDIB . (you need to get Oracle Gateways)
Install software
Unzip the downloaded zip. And run the installer gateways/runInstaller
In splash screen click next.
We leave the default values for location and name.
No incompatibilities were found -> Next.
Select Oracle Database Gateway for ODBC 11.2.0.3.0 -> Next
Click the Install button to install the software.
Execute the 1 or 2 scripts mentioned in the dialog screen
AS ROOT.
select
Perform typical configuration and click Next.
An error might popup. Just click OK
The error has been noted. Click retry to try again.
If we do exactly the same will it work now?
Apparently yes :-).
Verify if the software works
#as user oracle
export ORACLE_HOME=/home/oracle/product/11.2.0/tg_1
./dg4odbc
Oracle Corporation --- WEDNESDAY FEB 06 2013 21:02:11.918
Heterogeneous Agent Release 11.2.0.3.0 - 64bit Production Built with
Oracle Database Gateway for ODBC
Everytime you start dg4odbc make sure ORACLE_HOME is set!
Configure the listener
This must be done on the host ware DG4ODBC is installed. Because this listener will be your entry point to the MS SQL SERVER.
Listener config file is situated at $ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC =
(SID_NAME = MSSQLVM)
(ORACLE_HOME = /home/oracle/product/11.2.0/tg_1)
(ENVS="LD_LIBRARY_PATH=/usr/lib64:/usr/local/freetds/lib:/home/oracle/product/11.2.0/tg_1/lib")
(PROGRAM = dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = gwexample)(PORT = 1511))
)
)
)
ADR_BASE_LISTENER = /home/oracle/product/11.2.0/tg_1
Important notes with listener config:
- ENVS is not a typo, it should be ENVS
- SID_NAME should correspond to DSN from /etc/odbc.ini
- Each occurence of '/home/oracle/product/11.2.0/tg_1' should be replaced by the oracle home of dg4odbc but you cannot use the variable $ORACLE_HOME
- HOST should be set to hostname or IP of the machine that hosts the dg4odbc. (if hostname is used it is best to have it in /etc/hosts of this machine)
- PORT should be set to any free port
- Firewall should be configured to allow connections to the PORT selected above -> do this now because you will forget about it!
- ADR_BASE_LISTENER might not be necessary but I set it to the oracle home of dg4odbc and that does work
Configure tnsnames.ora
This file should be available at each machine that has an Oracle Database that wants to connect to the MS SQL server using the DG4ODBC. It is used to resolve the listener!
tnsnames config file is situated at $ORACLE_HOME/network/admin/tnsnames.ora
MSSQLVM =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=gwexample)(PORT=1511))
(CONNECT_DATA=(SID=MSSQLVM))
(HS=OK)
)
Important notes with tnsnames.ora config
- The name at the start is chosen to be the same as the DSN for convenience
- The value for HOST does not have to be the ip/hostname of the DB server but must be the ip/hostname of the machine where dg4odbc is installed (this can be the same as the DB server)
- The value after the SID must be the DSN
- The line (HS=OK) must be present!
Configure dg4odbc
Configuration of this is done in $ORACLE_HOME/hs/admin/init.ora
# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC
#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MSSQLVM
HS_FDS_TRACE_LEVEL = 255
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
HS_FDS_SUPPORT_STATISTICS = FALSE
HS_LANGUAGE=american_america.we8iso8859P1
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set =
Important notes with dg4odbc config
- HS_FDS_CONNECT_INFO must be set equal to the DSN
- HS_FDS_TRACE_LEVEL must be set to 0 after setup is working! It should only be set to 255 to do troubleshooting at the beginning.
- HS_FDS_SHAREABLE_NAME should point to the ODBC library (i.e. the one of the ODBC driver manager)
- HS_LANGUAGE should be set to american_america.we8iso8859P1
Reload the listener
This should be done for each change of listener.ora (on dg4odbc machine) and tnsnames.ora (on DB machine)
#as user oracle
#Make sure if ORACLE_HOME is set correctly and if DB SID
lsnrctl reload
Test if dg4odbc can be reached from the DB server
#as user oracle
#if not set, set oracle environment using . oraenv
[oracle@dbserver ~]$ tnsping MSSQLVM
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 10:09:48
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=gwexample)(PORT=1511)) (CONNECT_DATA=(SID=MSSQLVM)) (HS=OK))
OK (0 msec)
Creat DB link
#in the Oracle DB execute:
create database link db_link connect to "test" identified by "Welcome1" using 'MSSQLVM';
SELECT * FROM dbo.table@db_link;
Important notes:
- db_link is the name of the database link. It can be used after an @-sign
- In the create database link statement BOTH the username and password should be surrounded by double quotes.
- After using comes the DSN, surrounded by single quotes.
Troubleshooting
Handy files
- /tmp/sql.log
- $ORACLE_HOME/hs/log directory contains traces
Error messages + solution
Strange output -> language issue (we only speak americano)
sqlplus output
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[
ORA-02063: preceding 2 lines from link_name
Sqldeveloper ouput
For Sqldeveloper is the output a bit clearer:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[ u n i x O D B C ) ( D r i v e r M a n a g e r ] D a t a s o u r c e n a m e ...
Strange output comes due to incorrect language settings. Please verify that HS_LANGUAGE=american_america.we8iso8859P1 is set in init.ora !!
Corrupt link
This time no error message is displayed at all.
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding 2 lines from link_name
(and ORA-28500) are caused by invalid
If the listener is restarted with a new config your link in the database might have become invalid. Drop it and recreate it:
DROP DATABASE LINK db_link;
create database link db_link connect to "test" identified by "Welcome1" using 'MSSQLVM';
Nonexistent table or view
ORA-00942: table or view does not exist
[FreeTDS][SQL Server]Invalid object name 'your.table' . {42S02,NativeErr = 208}[FreeTDS][SQL Server]Statement(s) could not be
ORA-02063: preceding 2 lines from link_name
00942. 00000 - "table or view does not exist"
This can happen if there are quotes surrounding the table name -> Remove surrounding quotes!
References
Some of the contents is based on http://gotodba.weebly.com/install-and-configure-db-link-to-sql-server-database-in-linux.html.
De freetds website http://freetds.schemamania.org/
https://forums.oracle.com/forums/thread.jspa?messageID=4351081 . Thank you kgronau for the answer on setting HS_LANGUAGE!
