ODBC (via Direct Access gateway)

The ODBC (via Direct Access gateway) connector allows you to access ODBC data stores that do not have an associated connector in Qlik Cloud. Connections can be configured by selecting ODBC (via Direct Access gateway) from the list of connectors in the Qlik Sense Add data connection or Data load editor dialogs.

Unsupported or partially supported drivers

The following ODBC drivers are either not supported or partially supported with the ODBC (via Direct Access gateway) connector:

• Oracle Instant Client - Not supported
• MongoDB Atlas SQL driver - Not supported

• MS Access ODBC driver - This driver is supported with process isolation only. Additionally, multiple users trying to preview data or select tables concurrently, might result in errors.

  For more information on process isolation, see Mitigating connector crashes during reload.

Verifying the driver is installed

An ODBC driver for your ODBC data source must be installed on the Qlik Data Gateway - Direct Access machine for Qlik Sense to be able to access your database. Best practice is to always install the latest driver. Please refer to the vendor's documentation for download and installation instructions. You can verify which version is installed in the ODBC Data Source Administrator.

To do this:

1. On the Direct Access gateway machine, open the ODBC Data Source Administrator by entering odbcad32.exe in the Type here to search box in the bottom left of your screen and pressing [Enter].
2. In the ODBC Data Source Administrator window (64-bit) dialog, select the Drivers tab and verify that the correct driver version is installed.

Creating a System DSN

You can access your data source by specifying the System DSN or by selecting the driver and configuring a connection string. You can use the ODBC Data Source Administrator to create a System DSN if one does not exist for your data source.

Before you begin

Obtain the following configuration information from your data source system administrator:

• The database server host name or IP address.
• The name of the database (and schema, if the database supports multiple schemas)
• A valid user name for accessing the system or data source
• A valid password for accessing the system or data source

Procedure

To create a System DSN, do the following:

1. On the Direct Access gateway machine, open the ODBC Data Source Administrator by entering odbcad32.exe in the Type here to search box in the bottom left of your screen and pressing [Enter].
2. In the ODBC Data Source Administrator window (64-bit) dialog, select the System DSN tab.

3. Click Add.

   The Create New Data Source window opens.

4. Select your driver and click Finish.
5. In the Create a new Data Source to <you-data-source> wizard, configure the DSN and driver options.
6. Click Test DSN (the button name differs according to your data source) to attempt to establish a connection to your data source.
7. If the connection fails, review and correct your parameters and then try again.
8. Finish the data source configuration by closing any open configuration windows.

Allowing only specific drivers and DSNs

When working with the ODBC (via Direct Access gateway) connector, you can set allow lists for specific drivers and DSNs.

Allowing only specific DSNs

You can add the DSNs you want to allow to the genericodbc_allowed_DSNs.txt file. This file is created in C:\ProgramData\Qlik\Gateway the first time a connection between Direct Access gateway machine and Qlik Cloud is established.

To do this:

1. On the Direct Access gateway machine, open C:\ProgramData\Qlik\Gateway\genericodbc_allowed_DSNs.txt as admin.
2. To allow all DSNs (the default), simply add * to the file.

3. To allow only specific DSNs, list the DSN names in the configuration file, as in the following example:

   GBQ_Test_DSN

   Denodo_Test_DSN

4. Save the file and restart the Qlik Data Gateway - Direct Access service.

   In the ODBC (via Direct Access gateway) connector dialog, when System DSN is selected, only the DSNs listed in genericodbc_allowed_DSNs.txt will appear in the ODBC source dropdown.

Allowing only specific drivers

You can add the drivers you want to allow to the genericodbc_allowed_drivers.txt file. This file is created in C:\ProgramData\Qlik\Gateway the first time a connection between Direct Access gateway machine and Qlik Cloud is established.

To do this:

1. On the Direct Access gateway machine, open C:\ProgramData\Qlik\Gateway\genericodbc_allowed_drivers.txt as admin.
2. To allow all drivers (the default), simply add * to the file.

3. To allow only specific drivers, add the driver names to the configuration file, as in the following example:

   DenodoODBC ANSI(x64)

4. Save the file and restart the Qlik Data Gateway - Direct Access service.

   In the ODBC (via Direct Access gateway) connector dialog, when ODBC driver is selected, only the drivers listed in genericodbc_allowed_drivers.txt will appear in the ODBC source dropdown.

Setting up custom SQL syntax

The default SQL syntax templates are stored in file C:\ProgramData\Qlik\Gateway\genericodbc_database_syntax.json. This file is created in C:\ProgramData\Qlik\Gateway the first time a connection using the ODBC (via Direct Access gateway) connector is established.

The SQL syntax templates are listed in the SQL syntax dropdown of the ODBC (via Direct Access gateway) connector dialog. You can edit an existing SQL syntax template or add new custom SQL syntax (that is supported by your ODBC driver), according to your needs.

To add custom SQL syntax:

1. On the Direct Access gateway machine, open C:\ProgramData\Qlik\Gateway\genericodbc_database_syntax.json as admin.

   Make a copy of the section:

   { "SyntaxId": "syntaxCustom", "DisplayName": "Custom SQL syntax", "DelimiterStart": null, "DelimiterEnd": null, "DataPreviewSelectTemplate": null, "DatabaseTerm": null, "OwnerTerm": null }
2. Replace the DisplayName value and the null values with your own custom values.
3. Save the file and restart the Qlik Data Gateway - Direct Access service.