Using ODBC with Linux/Containers

Overview

Unlike on Windows where ODBC is managed through a user interface and Data Source Names (DSNs) are created using a Wizard, ODBC on Linux is managed through various configuration files and in a lot of cases can be more confusing than its Linux counterparts.

This page aims to provide documentation for working with ODBC on Linux, especially in the context of the Loome Agent containers which are based on the Debian 10 image.

If you are not using the Loome Agent Docker Image and instead hosting on a Debian/Ubuntu server, you will need to run sudo -i before following along with this documentation to ensure all commands are ran with super user privileges.

Setup Guides

We provide step-by-step setup guides for certain ODBC scenarios. These include:

Need help setting up an ODBC connector and not sure where to start? Contact us at support@loomesoftware.com

Checking for ODBC Installations

Loome Integrate requires unixODBC installed on an agent’s host in order to use the ODBC connector type as a source in Data Migration tasks. As of August 2020, the Loome Agent container image includes unixODBC installed out of the box so there’s no need to perform any installation of unixODBC to use the ODBC connector.

To test if unixODBC is installed, run odbcinst -j from the command line. You should receive an output like below:

unixODBC Installer Check

If you receive an error along the lines of odbcinst: command not found then you will need to install unixODBC by running:

apt-get install -y unixodbc

If you are able to run the ODBC diagnostics command successfully then you are ready to start configuring ODBC for the Loome Agent on that host.

Installing ODBC Drivers on Linux

No matter if the Loome Agent is running on Windows, Linux or a Docker Container; Loome does not provide any ODBC drivers with the agent and so you must find the drivers for your data source.

The installation process for drivers differs between data source as well. ODBC Drivers use the file extension .so so most of the time all you will need to do is download the driver and point the DSN at the driver file.

Not sure where to find the ODBC Driver for your data source? Contact us at support@loomesoftware.com and we’ll help you find the right driver files.

Managing Data Source Names (DSN) on Linux

To manage DSNs for unixODBC, we need to modify the “System Data Sources” file. This file’s location is shown in the odbcinst -j command output and by default is located at /etc/odbc.ini.

All ODBC DSN definitions follow the same pattern of looking like the following:

[NameOfDSN]
Driver = /path/to/driver.so
ConnectionProperty1 = ConnectionValue1
ConnectionProperty2 = ConnectionValue2
ConnectionProperty2 = ConnectionValue2
...

The configuration of the DSN will vary depending on the data source but we can say with certainty at a minimum they will require:

  • A unique name in square brackets at the top
  • A path to the driver file for the Driver property
  • Additional connection properties such as the data server host, port and database name.

Loome recommends not including credentials such as username and password in your ODBC DSN. This is not only because most drivers will not accept credentials from the DSN configuration but also because it is insecure and in plain text.

Creating an ODBC Connection in Loome Integrate

Once your DSN has been setup, open Loome Integrate and navigate to the connections page.

open connections page

Then select the “New Connection” button in the top right hand corner. Provide a name for the connection and then for the connector type select “ODBC”.

Navigating to the next page will prompt you for a username, password and connection string.

The username and password should be set to the username and password used to access your data source. If you are accessing a data source such as Excel spreadsheet or your driver supports having the credentials stored in the DSN then these fields are not required.

Loome Integrate does not support Windows/Trusted authentication methods over ODBC so you will need to use SQL authentication even if your data source supports trusted authentication methods.

For the connection string, this is in the format of DSN=DSN_NAME. The value of the DSN name is the value you used in the square brackets for your DSN definition.

With this in mind, if I had an Oracle ODBC DSN named “TestOracleTNS” and was connecting to the data source as the user hr, my connection details would be the following:

connection details oracle ODBC

Once you’ve provided your credentials, make sure to validate the connection with the agent you created the DSN for and that’s it! You’re ready to start sourcing data from ODBC connections using your Linux/Container Loome Agent.