DSN configuration for ODBC connections to VDP

To configure an ODBC connection to VDP, Denodo provides an ODBC driver that is part of the Denodo Platform installation and can be found under:

<DENODO_HOME>/tools/client-drivers/odbc

For the Denodo version previous to 6.0, the Denodo ODBC driver is not available and the PostgreSQL ODBC driver must be used, it can be downloaded from the PostgreSQL website. (http://www.postgresql.org/ftp/odbc/versions/msi/). The most recent updates of Denodo 5.5 have also included the Denodo ODBC driver for this version.

The most common mistake when setting up an ODBC connection is to configure the client to use port 9999 for the connection instead of port 9996 that is the default port for ODBC connections.

Once the drivers are installed, to avoid any mistake, please follow these steps:

1. Make sure that you have logged in using an account with administrative rights.
2. Open the ODBC Data Sources applet of the Windows Administrative Tools.
3. Open the System DSN tab and click on Add.
4. Select the DenodoODBC Unicode driver and click on Finish.
5. In the configuration dialog fill in the following information

1. Database: database in Virtual DataPort. E.g. admin.
2. Server and Port: hostname and port of the server that runs Virtual DataPort. The default ODBC port is 9996.
3. User Name and Password: credentials of a Virtual DataPort user.
4. If SSL is enabled on the Virtual DataPort server, in the SSL Mode list, select require.

6. Click on Datasource to open the Advanced Options dialog:

5. Select Use Declare/Fetch.
6. Clear KSQO (Keyset Query Optimization).
7. Clear Bools as Char.

7. Click on Page 2:

1. Clear the checkbox Server side prepare.
2. Clear the check box Updatable Cursors.
3. In the area Level of rollback on errors, select Transaction. This step is for versions prior to Denodo 8.0.

8. To set the internationalization configuration of the connection to the Virtual DataPort Server, enter SET i18n TO <i18n_name> in the Connect Settings box (e.g. ‘SET I18N TO us_est’). If empty, the connection will have the I18N of the server configured. When executing a query, the "date" fields of the result are converted to the time zone assigned to the i18n of the connection.
9. You can change the timeout entering the following in the Connect Settings box:

SET QUERYTIMEOUT TO <value> (value in milliseconds);

10. Add the following to the “Connect settings” box to connect to Virtual DataPort using Kerberos authentication:

/*krbsrvname=HTTP*/

Important: This line has to be the last line on the “Connect settings” box.

11. Click on Page 3
12. Set the option “The use of LIBPQ library” to Yes.
13. Click on Test to test the connection to Virtual DataPort.
14. Click Ok.

The default port for ODBC connections in VDP is 9996, but it can be configured.

In order to configure the ODBC port using the VDP admin tool go to "Administration > Server configuration > Server connectivity > ODBC Port Number".

Enabling Verify-full SSL Mode

Taking in account that the required signed certificates have been generated and imported and the VDP server configuration has been set to use SSL, in order to configure the SSL Mode as “verify-full” instead of “require” follow the next steps:

1. Check that the "CN" attribute of the self-signed certificate matches the hostname or IP address of the host where the Denodo server is running.
2. Obtain the public key certificate encoded in Base64 from the self-signed certificate.
3. In the host where you are going to create the DSN, rename this file to "root.crt" and
     - On Linux, copy it to the directory ~/.postgresql/
     - On Windows, copy it to the directory %APPDATA%\postgresql\

The following steps explain in more detail this configuration:

1. Obtain the "alias" of the self-signed certificate in the keystore. To do this, execute the following:
   

        You will see something like this

In this case "self_signed_cert" is the alias of the certificate.

2. Check that the "cn" attribute of the self-signed certificate is the host name or the IP address of the host where the Denodo server is running.
   
   In "verify-full" mode, the driver matches the "cn" (Common Name) attribute of the certificate against the host name. If the "cn" attribute starts with an asterisk (*), it will be treated as a wildcard, and will match all characters except a dot (.). This means the certificate will not match subdomains. If the connection is made using an IP address instead of a host name, the IP address will be matched (without doing any DNS lookups).
   
   In the output of step 1., you will see something like:

In the line that starts with "Owner: ", check that the value of the attribute "CN" is the IP or the hostname of the machine where Denodo is running, or "*". In this example, the certificate will only work if the Denodo server runs in "192.168.1.147".

3. Export the public key certificate from the self-signed certificate. To do this, execute the following:

4. Copy the file "public_key.crt" to the host where you are going to create the DSN and rename the file "public_key.cer" to "root.crt". Then, copy it to:

1. On Linux, copy it to the directory ~/.postgresql/
2. On Windows, copy it to the directory %APPDATA%\postgresql

References

Virtual DataPort Developer Guide: Configuration of the ODBC driver in Windows

Virtual DataPort Administration Guide: Server Connectivity