Connecting to agile with odbc

Hi,
We need to get to agile db with odbc. I found these instructions online. Do you think they are ok for oracle 12c db as well. We’re on windows server, agile 9.3.385.

 I’ve never done this before.  Any gotchas?

Thanks,
Laura

How to Access SQL Server from Oracle using Database Gateway for ODBC

How to configure Oracle Database Gateway for ODBC to access SQL Server data from Oracle Database over db links.

In Oracle, you can access MS SQL Server data using database link with Oracle Database Gateway (formerly Oracle Heterogeneous Services). With either of these two options

  1. Oracle Database for SQL Server
  2. Database Database for ODBC

The focus of this post in the second option of setting up Database Gateway for ODBC to access SQL Server. Before we start, I am assuming you  have some basic knowledge of Oracle networking and database on Linux and already have the following installed. (and also this is intended for practice purpose only)

  • Oracle Database 11.2 on RHEL or OEL
  • SQL Server 2008 or later.
  • Optional. You can Install Oracle Database Gateway for ODBC in a separate oracle home. In this case I will be using Oracle Database home.

We will need a ODBC driver in order to configure Oracle Gateway for ODBC and fortunately there one from Microsoft. The following steps will guide you through installing and configuring Oracle Gateway for ODBC for SQL Server.

1 – Install ODBC Driver Manager.
2 – Download and Install ODBC Driver.
3 – Configure odbc.ini.
4 – Create init<instance name>.ora for heterogeneous services.
5. Register with the Listener.
6. configure tnsnames.ora.

7. Create database link in Oracle.

Step 1 – Install ODBC Driver Manager, the steps are documented on this link.

Step 2 – Download and Install ODBC Driver.
Follow this article to download and install the Microsoft ODBC Driver 11 for SQL Server. once the drive is installed you can verify it with sqlcmd command. sqlcmd gets installed along with Microsoft ODBC Driver.

sqlcmd -SServerName -Puserid -Upassword

serverName is the name of the windows server where sql server is installed.
userid and password are SQL Server credentials you want to connect to.

Step 3 – Configure odbc.ini
Add following to /etc/odbc.ini file

[sql_connection]
Driver=ODBC Driver 11 for SQL Server
Server=sqlserver_name,1433
Database=master

[sql_connection] is just an alias for this ODBC entry. You can enter a name which    represents the SQL Server you intent to connect to and has to be within the square brakets.

-Driver is the name of the driver we are using, in this case is the “ODBC Driver 11 for SQL Server”
-Server is the name of the windows server where SQL Server installed and the number is the port number for SQL Server connections .

-Database is the default database to open when login in to SQL Server.

Step 4 – Create init<instance name>.ora for heterogeneous services
Create a initialization file, think it like a oracle instance which we will later register with the listener. This initialization parameter file should be created under ORACLE_HOME/hs/admin folder. Give a name which is easy to associate with the SQL Server you are connecting to.

$ORACLE_HOME is wither your oralce home or oracle database gateway home in case you installed database gateway into another home.vi $ORACLE_HOME/hs/admin/initDG4SQL.ora

#————————————————————
# HS init parameters needed for the Database Gateway for ODBC
#————————————————————
HS_FDS_CONNECT_INFO = lfdevdb1
HS_FDS_TRACE_LEVEL = off
HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
#————————————
# ODBC specific environment variables
#————————————
set ODBCINI=/etc/odbc.ini

Step 5. Register with the Listener: register the the newly created initialization (initDG4SQL.ora) file with oracle listener. Go to ORACLE_HOME/network/admin and edit the listener.ora file and add the following.

(SID_DESC =
(ORACLE_HOME = /oracle/app/oracle/product/11.2/gwhome1)
(SID_NAME=DG4SQL)

(ENV=”LD_LIBRARY_PATH=/usr/lib64:/oracle/app/oracle/product/11.2/gwhome1/lib”)
(PROGRAM=dg4odbc)
)

Step 6. configure tnsnames.ora: Configure the tnsnames.ora. Note that this should be on the database home where the db link to sql server will be created. Go to ORACLE_HOME/network/admin and edit the tnsnames.ora file and add the following.

DG4SQL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = OraDbHost)(PORT = 1521))
(CONNECT_DATA =
(SID = DG4SQL)
)
(HS=OK)
)

Step 7. Create db link: And finally we are ready to create a db link and acess sql server. Login to sqlplus and create the database link to connect to sql server.

Replace sql server user name and password with targets’s SQL Server user id and password.

CREATE DATABASE LINK SQLLINK
CONNECT TO <sql server user name>
IDENTIFIED BY <sql sevrer password>
USING ‘DG4SQL’

SELECT * FROM “sysobjects”@SQLLINK

Note that, if the SQL Server table is is either mixed or small case then you will have to put the table name within double quotes else you don’t need the quotes the SQL Server table name is in upper case.

**********

Thanks,
Laur

Add Comment
4 Answer(s)

DISREGARD THIS. POSTED A DIFFERENT QUESTION.

Agile Talent Answered on June 7, 2019.
Add Comment

Hi Laura,

Are you trying to connect to SQL Server from Oracle? Or are you trying to connect another application, such as MS Access, to Oracle? Setting up an ODBC connection on a Windows server is pretty simple so I’m just clarifying what you’re trying to accomplish.

Agile Angel Answered on June 7, 2019.
Add Comment

Hi Keith,
I just posted a different question. 
We are trying to connect to agile with boom via odbc connection.
We have an error. Please see attached.

Thank you!
Laura

Agile Talent Answered on June 7, 2019.
Add Comment

I responded to the other thread with a link that discusses this issue. It could be a simple sqlnet.ora update on the database side. Using the 12c client might be another solution.

Agile Angel Answered on June 7, 2019.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.