Configuring DG4ODBC

Oracle Home

D:\app\Oracle\product\11.2.0\dbhome_1

dg4odbc configuration file "initdg4odbc.ora"  is located under directory

D:\app\Oracle\product\11.2.0\dbhome_1\hs\admin

Contents of initdg4odbc.ora file

# 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 = sqlsysodbc     
HS_FDS_TRACE_LEVEL =


#
# Environment variables required for the non-Oracle system
#
#set =

#####################################################

where sqlsysodbc is name of system dsn we created for odbc connectivity.

Configure Oracle Listener

# This is a sample listener.ora that contains the NET8 parameters that are
# needed to connect to an HS Agent

LISTENER =
 (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))
 )

SID_LIST_LISTENER=
  (SID_LIST=
      (SID_DESC=
         (SID_NAME=dg4odbc)
         (ORACLE_HOME=D:\app\Oracle\product\11.2.0\dbhome_1)
         (PROGRAM=dg4odbc)
      )
  )

#CONNECT_TIMEOUT_LISTENER = 0

#############################################################

Above content in red is added so listener can listen for sid using parameter file
D:\app\Oracle\product\11.2.0\dbhome_1\hs\admin\initdg4odbc.ora by invoking utility dg4odbc

Configure tnsnames.ora

Add entry for tnsnames.ora


dg4odbc  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=myhost.mydomain.com)(PORT=1521))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
  ) 

###############################################################

If you want to access Remote SQL Server DB using domain username you need to right click listener and click Log on tab. Add your domain username and password.

Create db link

CREATE PUBLIC DATABASE LINK "TEST_SQL"
 CONNECT TO "MYDOMAIN\USERNAME"
 IDENTIFIED BY "PASSWORD"
 USING 'dg4odbc';

############################################################

Now you can connect sql server from Oracle using domain user

select * from dual@test_sql


DUMMY
----------------
X