Oracle Gateways setup for MS SQL using Microsoft drivers for Linux

This post outlines the procedure involved in setting up a database link between Oracle 11gR2 and MS SQL Server using Oracle's Database Gateways for ODBC (DG4ODBC) using the freeware odbc drivers available from Microsoft.

The software components involved are:

·         unixODBC Driver Manager 2.3.0 (http://www.unixodbc.org)

·         Microsoft SQL Server ODBC Driver 1.0 for Linux

·         Oracle 11gR2 DG4ODBC (shipped with Oracle Server)

·         MS SQL Server 2005 (or later versions 2008, 2012)

1.     Install the unixODBC driver manager and ODBC driver as per the instructions below as 'root' user.

http://www.microsoft.com/en-us/download/details.aspx?id=28160#instructions

2.     Setup /etc/odbc.ini as below (as root user)

[mssql]

Description=Microsoft SQL Server ODBC Driver V1.0 for Linux

Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1790.0

Threading=1

UsageCount=1

AutoTranslate=No

#Had to use the above AutoTranslate setting to avoid a single quote in MS SQL records being #allocated an extra byte at Oracle end.

Database=MSSQL_Database_Name

UserName=MSSQL_DB_User

Password=xxxxxx

Port=1433

Server=MSSQLServer

Trace=Yes

TraceFile=/u00/odbctrace.log

3.     Setup Oracle listener.ora ($ORACLE_HOME/network/admin/listener.ora) as below

*************************
# listener.ora Network Configuration File: xxxx/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME=DG4ODBC)
(ORACLE_HOME=Oracle_Home_Path)
(ENV="LD_LIBRARY_PATH=/usr/lib64:Oracle_Home_Path/lib")
(PROGRAM=dg4odbc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = Linux_Host)(PORT = 1521))
)
)

ADR_BASE_LISTENER = /u00/app/oracle
*************************

4.     Add the below entry to your tnsnames.ora (Oracle_Home_Path/network/admin/tnsnames.ora) as below

# tnsnames.ora Network Configuration File: Oracle_Home_Path/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

DG4ODBC =

(DESCRIPTION=

(ADDRESS=(PROTOCOL=tcp)(HOST=Linux_Host)(PORT=1521))

(CONNECT_DATA=(SID=DG4ODBC))

(HS=OK)

)

5.     Setup HS Gateways as below (Oracle_Home_Path/hs/admin/initDG4ODBC.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 = mssql
HS_FDS_TRACE_LEVEL = on
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
#
# ODBC specific environment variables
#
set ODBCINI=/etc/odbc.ini
#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

6.     Setup a database link as below

CREATE PUBLIC DATABASE LINK linkname
CONNECT TO "MSSQL_DB_User" IDENTIFIED BY "xxx" USING 'DG4ODBC';

7.     Add the below env variables to the .profile or .bash_profile of Oracle user

export LD_LIBRARY_PATH=/opt/microsoft/sqlncli/lib64:Oracle_Home_Path/lib
export ODBCINI=/etc/odbc.ini
export NLS_LANG=Language_Territory.Characterset