Linux DSN Configuration

This section shows how to connect to OData and execute SQL queries from several Linux distributions: Debian-based systems, like Ubuntu, and Red Hat Linux platforms, like Red Hat Enterprise Linux (RHEL), CentOS, and Fedora.

Using a Driver Manager

Before installing the CData ODBC Driver for OData 2016 on Linux, you will need to install a driver manager. ODBC-compliant applications connect to the driver through a driver manager. After you connect, the driver manager loads the driver and passes function calls from the application to the driver. The driver manager also implements some functions for discovering the registered drivers and DSNs. DSNs can be defined for a specific user or system wide.

Installing unixODBC

Precompiled binaries for the unixODBC driver manager are available for many operating systems.

On Debian-based systems like Ubuntu, you can install unixODBC by running the following command as root or with sudo:

apt-get install unixODBC unixODBC-dev

On Red Hat Enterprise Linux, CentOS, and Fedora, you can install unixODBC with YUM or DNF. For example, run the following command as root or with sudo:

yum install unixODBC unixODBC-devel

If binaries are not available for your operating system, you will need to compile unixODBC yourself. Please refer to the unixODBC website for more information about obtaining binaries or compiling unixODBC on your operating system.

Installing the Driver Dependencies

Minimum Linux Versions

Below are the minimum supported versions for Red Hat-based and Debian-based systems:


OS	Min. Version
Ubuntu	11
Debian	7
RHEL	6
CentOS	6
Fedora	13

Required Libraries and Packages

Below are the libraries required by the driver and their corresponding packages:


Debian/Ubuntu Package	RHEL/CentOS/Fedora Package	File
libc6	glibc	linux-vdso.1
libc6	glibc	libm.so.6
libc6	glibc	librt.so.1
libc6	glibc	libdl.so.2
libc6	glibc	libpthread.so.0
libc6	glibc	libc.so.6
libc6	glibc	ld-linux-x86-64.so.2
libstdc++6	libstdc++	libstdc++.so.6
zlib1g	zlib	libz.so.1
libgcc1	libgcc	libgcc_s.so.1

Installing the Packages

You can install the necessary dependencies with the following commands:

Debian/Ubuntu:

apt-get install libc6 libstdc++6 zlib1g libgcc1

RHEL/CentOS/Fedora:
```
yum install glibc libstdc++ zlib libgcc
```

Setting Up OAuth

OAuth requires the authenticating user to interact with OData, using the browser. The driver facilitates this in various ways as described in the following sections; to complete the authentication guide, first check if your environment needs additional configuration.

If the first OAuth interaction is to be done on the same machine the driver is installed on -- for example, a desktop application -- the driver will need access to the xdg-open program, which opens the default browser.

This OAuth flow is not possible on some systems, such as headless machines, servers, and machines disconnected from the Internet. You can use stored procedures to authenticate without this dependency.


Debian/Ubuntu Package	RHEL/CentOS/Fedora Package	File
xdg-utils	xdg-utils	xdg-open

Installing the Driver

You can use standard package management systems to easily and quickly install the driver.

On Debian-based systems, like Ubuntu, you can install the driver by running the following command as root or with sudo:

dpkg -i /path/to/.deb

On systems that support the RPM package format, you can install the driver by running the following command as root or with sudo:

rpm -Uvh /path/to/.rpm

Using the unixODBC Configuration Files

You can determine the location of the configuration files on your system by entering the following command into a terminal:

odbcinst -j

Below is an example of the output of this command:

DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/myuser/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

The driver installation registers the driver for system-wide use and creates a system DSN with empty values for commonly required properties. Modifying unixODBC's system-wide settings requires elevated permissions; to do so, you can use root or the sudo command.

Configuring a DSN

ODBC data sources are defined in sections in the odbc.ini file.

In addition to the connection properties required to connect to your data source, the Driver property specifies either a driver definition in the odbcinst.ini file or the path to the driver library.

[CData OData Source]
Driver = CData ODBC Driver for OData
Description = My Description
MyConnectionProperty1 = My Value
MyConnectionProperty2 = My Value2

You can list the data sources on your system with the following command:

odbcinst  -q -s

Connecting to OData

To connect, you need to set the Url to a valid OData service root URI in addition to the authentication values.

Also, you can specify a CacheLocation to store the metadata of your OData organization. This will keep the CData ODBC Driver for OData 2016 from having to send requests for metadata on each connection.

Fine-Tuning Data Access

Set the following properties to control how the driver models OData APIs as a database:

NavigationPropertiesAsViews: By default, the driver models navigation properties as views. This enables access to related entities, even though these entities may not be linked by a foreign key in your OData service.
SupportsExpand: If your API does not support the $expand parameter, set this property to avoid an error when NavigationPropertiesAsViews is set. If this is the case for your API, specify the base entity's primary key in the WHERE clause to access navigation properties.
DataFormat: Set this property to JSON or XML. Otherwise, the driver uses the default format defined by the service.
ODataVersion: Use this to override the version detected by the driver. This is useful if your application supports an older OData version.
UseIdUrl: By default the driver returns the direct URL to an entity as the primary key. By setting this to false, the entity key is returned.
UseSimpleNames: Set this to true to return only alphanumeric characters in column names. This can help you to avoid SQL escapes and errors in SQL-based tools.

Authenticating to OData

The driver supports the major authentication schemes, including HTTP and Windows.

Set AuthScheme to use the following authentication types.

The driver simplifies OAuth configuration. See Using OAuth for a how-to.

HTTP Authentication

The driver supports authentication with HTTP Basic, Digest, and custom headers. To use Basic or Digest, set the User and Password. You can specify other authentication values in CustomHeaders.
Windows (NTLM)

Set the Windows User and Password to connect and set AuthScheme to "NTLM".
Kerberos and Kerberos Delegation

To authenticate with Kerberos, set AuthScheme to NEGOTIATE. To use Kerberos Delegation, set AuthScheme to KERBEROSDELEGATION. If needed, provide the User, Password, and KerberosSPN. By default, the driver attempts to communicate with the SPN at the specified Url.

Securing OData Connections

By default, the driver attempts to negotiate SSL/TLS by checking the server's certificate against the system's trusted certificate store. To specify another certificate, see the SSLServerCert property for the available formats to do so.

Authenticating Web Applications

On headless servers or other machines on which the driver is not authorized to open a browser, the driver cannot obtain the access token automatically. However, it can still automatically refresh the token. To obtain the token, you can call the driver's stored procedures, GetOAuthAuthorizationURL and GetOAuthAccessToken. First, set the InitiateOAuth inside the Other property to one of the following:

OFF: Indicates that the OAuth flow will be handled entirely by the user. An OAuthAccessToken will be required to authenticate.
REFRESH: Indicates that the driver will only handle refreshing the OAuthAccessToken. The user will never be prompted by the driver to authenticate via the browser. The user must handle obtaining the OAuthAccessToken and OAuthRefreshToken initially. The OAuth values are persisted in the OAuthSettingsLocation Other property.

After calling GetOAuthAccessToken, set the following in the Other property as well:

OAuthAccessToken
OAuthAccessTokenSecret
OAuthRefreshToken

Refreshing OAuth Values

The driver can not only initiate the OAuth exchange, but also refresh the tokens when they expire. To enable the automatic token exchange, you can give the driver write access to /etc/odbc.ini or set the OAuthSettingsLocation property, an Other property, to persist the encrypted tokens to an alternate file path that the driver has write access to.

Configuring Driver Definitions

The sections of the odbcinst.ini file map a driver name to the driver library. The section begins with the driver name; the Driver property points to the path to the driver library. The driver library is the .so file located by default in /opt/cdata/odata/lib. A Description property can also be provided.

You can list the drivers installed on the system with the following command:

odbcinst -q -d

Testing the Connection

You can use isql, included with unixODBC, to execute SQL queries to OData from the command line. When testing the connection, use the -v flag to output any messages from the driver manager and the driver.

isql -v "CData OData Source"