DG4ODBC setup on Oracle 11g Linux to access Microsoft SQL Server database tables

Had a requirement recently to configure HS gateways connectivity between an Oracle 11g on Linux 64bit and MS SQL 2005 database and here's the simple configuration that worked for me. 

As always, it's better to start testing this type of configuration with basic settings first and then move onto other more complex stuff later.

·                    Install the third party ODBC drivers on the Linux Oracle 11g Server, in my case, I've    used datadirect ODBC drivers CONNECT64 FOR ODBC RELEASE 7.1.0 ON UNIX/LINUX

Installation is self-explanatory as you uncompress the tar file and run the script.

·                    Setup the odbc.ini file with the below basic settings

[ODBC Data Sources]

DB2 Wire Protocol=DataDirect 7.1 DB2 Wire Protocol

Informix Wire Protocol=DataDirect 7.1 Informix Wire Protocol

Oracle Wire Protocol=DataDirect 7.1 Oracle Wire Protocol

Sybase Wire Protocol=DataDirect 7.1 Sybase Wire Protocol

Teradata=DataDirect 7.1 Teradata

SQL Server Wire Protocol=DataDirect 7.1 SQL Server Wire Protocol

MySQL Wire Protocol=DataDirect 7.1 MySQL Wire Protocol

PostgreSQL Wire Protocol=DataDirect 7.1 PostgreSQL Wire Protocol

Greenplum Wire Protocol=DataDirect 7.1 Greenplum Wire Protocol

Salesforce=DataDirect 7.1 Salesforce

Sybase IQ Wire Protocol=DataDirect 7.1 Sybase IQ Wire Protocol

Progress OpenEdge Wire Protocol=DataDirect 7.1 Progress OpenEdge Wire Protocol

Apache Hive Wire Protocol=DataDirect 7.1 Apache Hive Wire Protocol

[ODBC]

IANAAppCodePage=4

InstallDir=/opt/Progress/DataDirect/Connect64_for_ODBC_71

Trace=1

TraceFile=/tmp/odbctrace.out

TraceDll=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddtrc27.so

[mssql]

Driver=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddsqls27.so

Description=DataDirect 7.1 SQL Server Wire Protocol

AnsiNPW=1

Database=sqldatabase

EnableQuotedIdentifiers=1

HostName=Linux Hostname

LogonID=mssqluser

Password=mssqlpassword

PortNumber=1433

·         Update the below settings into the .bash_profile or .profile

LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib:$ORACLE_HOME/lib

ODBCINI=/opt/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini

At this point, you can test the odbc connectivity as user 'oracle' as below:

/opt/Progress/DataDirect/Connect64_for_ODBC_71/samples/demo/demoodbc -uid -pwd  mssql

• Update the listener.ora with the below settings

*********************

SID_LIST_LISTENER = 

   (SID_LIST = 

      (SID_DESC = 

       (SID_NAME=DG4ODBC) 

      (ORACLE_HOME=ORACLE_HOME_PATH) 

      (ENV="LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib:ORACLE_HOME_PATH/lib"

      (PROGRAM=dg4odbc) 

      ) 

   ) 

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))

    )

  )

ADR_BASE_LISTENER = /u00/app/oracle

*********************

• Add the below entry to the tnsnames.ora as below

*********************

DG4ODBC = 

   (DESCRIPTION= 

      (ADDRESS=(PROTOCOL=tcp)(HOST=Linux Hostname)(PORT=1521)) 

      (CONNECT_DATA=(SID=DG4ODBC)) 

      (HS=OK) 

   )

*********************

• Setup the init.ora of the Gateway, there is a sample file under $ORACLE_HOME/hs/admin directory.

***********************

# 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 = /opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/libodbc.so

#

# ODBC specific environment variables

#

set ODBCINI=/opt/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini

#

# Environment variables required for the non-Oracle system

#

#set =

***********************

• Configure the Oracle database link

create database link connect to “sqluser"  identified by "pwd" using 'DG4ODBC';

• Restart the listener

Now you're ready to access MS SQL from your Oracle instance , say as below

select count(*) from sqltable@dblink;

or select count(*) from sqldatabase.sqltable@dblink etc.