Install the ODBC Driver on Windows¶

Install the ODBC driver in the machine where the client application runs. To do this, follow these steps:

1. Obtain the file denodo-vdp-odbcdriver-windows.zip. To do this:

   1. Copy it from the installation (<DENODO_HOME>/tools/client-drivers/odbc/denodo-vdp-odbcdriver-windows.zip).

   2. Or download it from the ODBC page of the Denodo Community.

      On this page, download the driver for Windows (the name ends up with -win). Make sure you select a version of the package that is not newer than the Denodo server you are going to connect. For example, if your Denodo server has the update 7.0 20181011, do not download the package denodo-vdp-odbcdriver-7.0-update-20190312-win because it is newer.

2. Extract the contents of this file and copy them to the host where the client application runs.

   It contains two files:

   • DenodoODBC_x64.msi (folder “msi”) installs the ODBC driver for 64-bit clients.

   • DenodoODBC_x86.msi (folder “msi”) installs the ODBC driver for 32-bit clients.

   You can install both on the same host so all the applications can use this ODBC driver regardless of its “bitness”.

   The 32-bit driver is for 32-bit clients like Microsoft Excel. Microsoft Excel can only use the 32-bits ODBC driver, even if it is running on a 64-bits O.S and is going to connect to a Virtual DataPort server running with the 64-bits JRE.

Set Up a DSN on Windows¶

After installing the Denodo ODBC driver on the host where the client application runs, you need to register a new data source, also known as DSN, that points to the Denodo server.

Follow these steps to do this:

1. Open the ODBC Data Sources applet of the Windows Administrative Tools (Control Panel).

   To open it, press the Windows key and enter Set up ODBC data source.

   Important

   There are two dialogs to register an ODBC DSN, one for 32-bit applications and one for 64-bit applications.

   For example, to connect to Denodo from Microsoft Excel, use Set up ODBC data sources (32-bit).

2. In the tab User DSN, click Add.

   When creating the DSN, do it with the same user account with you run the application that will connect to Denodo. If that is not possible, create a “System DSN”. Only local administrators of the computer can register a system DSN. If an administrator cannot create the DSN, create a user DSN.

   The difference between a “System DSN” and a “User DSN” is that the “User DSN” can only be used by the current user and the “System DSN” can be used by all the users of the system.

3. Select the DenodoODBC Unicode driver (not DenodoODBC ANSI) and click Finish.

4. In the configuration dialog, fill in the following information:

   1. Database: database in Virtual DataPort. E.g. admin. If the name of the database contains non-ASCII characters, they have to be URL-encoded. For example, if the name of the database is “テスト”, enter “%E3%83%86%E3%82%B9%E3%83%88”.

   2. Server and Port: host name and port of the server that runs Virtual DataPort. The default ODBC port is 9996.

   3. User Name and Password: credentials of the Virtual DataPort user.

      Note

      Some tools may ask you for a DSN name and then ask you for an user and password instead of using those defined in the DSN. you may not be able to connect if the password contains the following four characters: %, {, } and +. If so, you may need to escape these characters: % as %25, + as %2B, { as %7B and } as %7D.

      If Kerberos authentication is selected on the Denodo database you are connecting to, the driver will ignore these credentials. Instead, it will obtain a Kerberos ticket from the system cache to use Kerberos authentication. In this case, it is not possible to connect using normal user & password authentication. On the other hand, selecting Kerberos authentication at database level is no longer required to use Kerberos in ODBC connections, you can enable Kerberos authentication on the client configuration on Page 3 of the data source configuration in the DSN.

      Note

      To be able to use Kerberos authentication, the configuration of the DSN has to meet these conditions:

      1. The client has to belong to the Windows domain. The reason is that the ODBC driver requests the Kerberos ticket to the ticket cache.

      2. In the Server field, enter the fully qualified domain name of the Denodo server. That is, if in the Denodo server, in the Kerberos configuration, the field Server principal is HTTP/denodo-prod.subnet1.contoso.com@CONTOSO.COM, enter denodo-prod.subnet1.contoso.com.

   4. If SSL is enabled on the Virtual DataPort server, in the SSL Mode list, select require.

Denodo ODBC driver: configuration dialog¶

5. Click Datasource to open the Advanced options dialog.

   The default options (shown in the screenshot below) are correct, but consider the following:

   • If you want to log all the requests received by this DSN, select CommLog (C:\denodoODBC_xxx.log) and MyLog (C:\mylog_xxx.log).

     Important

     In a production environment, we strongly recommend clearing these check boxes because logging all the requests impacts the performance of the driver and the log file may grow to a very large size.

   • In “Unknown sizes”, select Maximum. See more about what this means in the section Maximum Length of Text Values.

   • As “Use Declare/Fetch” is selected, the DSN will use DECLARE CURSOR/FETCH to handle SELECT statements. The effect is that the DSN will retrieve the rows of the result set in blocks, instead of retrieving them all at once. Chunk Size establishes the number of rows of each block. The “Chunk size” of the DSN is equivalent to the “Fetch size” of the JDBC connections.

Denodo ODBC driver: advanced configuration (Page 1)¶

6. Click Page 2:

   The default options (shown in the screenshot below) are correct.

   In addition, in the area “Level of rollback on errors”, select Transaction.

Denodo ODBC driver: advanced configuration (Page 2)¶

• In the TCP KEEPALIVE setting box you can enable/disable the TCP keep alive feature. This is useful to prevent network agents like proxies or load balancers to close pooled connections at ODBC clients:

  1. disable: select this to disable this feature.

  2. idle time: after this number of seconds of inactivity, the driver sends a TCP keepalive message to Virtual DataPort. Default value: 60.

  3. interval: number of seconds after which the driver retransmits a TCP keepalive message that has not been acknowledged by Virtual DataPort. Default value: 5.

• In the Connect settings box you can change the properties of the ODBC connection established with Virtual DataPort, by entering the following:

  1. SET QUERYTIMEOUT TO <value> to change the query time out (value in milliseconds).

  2. SET i18n TO <i18n> to change the i18n of the connection.

  3. SET FORCE_DECIMAL_PROPERTIES TO true to force size and scale from decimal values to match the metadata information.

     For example, to set the default timeout of the queries to one hour, add the following:

     SET QUERYTIMEOUT TO 3600000;
     SET I18N TO us_pst;
     

     Note the ; between each statement.

     The following table describes these properties and lists its default values:

7. Click Page 3 and do the following:

   1. Set the option The use of LIBPQ library to Yes.

   2. In the User agent box, enter the name of the application that will use this DSN. We recommend setting this field in all the DSNs to Virtual DataPort because is very useful for logging.

   3. In the fields about Infrastructure it is possible to include information about the cloud infrastructure, like provider and region. To know more about cloud infrastructure’s configuration, see the section Setting Cloud Infrastructure Properties.

   4. Select Use Kerberos to enable Kerberos authentication.

   5. Usually, the default value of Krbsrvname is correct. If you enabled Kerberos authentication, this value has to match the “service class” of the Service Principal Name of the Server. For example, if in the field Server Principal of the Kerberos settings of the Server, you have HTTP/denodo1.contoso.com@CONTOSO.COM, the value of Krbsrvname has to be HTTP.

Denodo ODBC driver: advanced configuration (Page 3)¶

6. To establish the connection using OAuth authentication instead of user and password, select Enable OAuth 2.0 authentication and provide the following values:

Before using OAuth to connect to Virtual DataPort, you have to enable OAuth in Virtual DataPort. Otherwise, the connections with OAuth will fail.

When the client application opens a connection, the driver will request an OAuth token to the Identity Provider and will use that token to establish the connection.

8. Click Ok to close the “Advanced Options” dialog.

9. Click Test to test the connection to Virtual DataPort.

10. Click Ok.

The DSN is now configured and ready to be used.

After setting up the DSN, we recommend reading the section Integration with Third-Party Applications.

If there is an error during the creation of the DSN, try logging in using an account with administrative privileges.

Install Multiple Versions of the ODBC Driver on Windows¶

Currently, the DenodoODBC .msi installers do not allow having multiple versions of the DenodoODBC ODBC driver in the same Windows machine. But the DenodoODBC ODBC driver for Windows does include a set of scripts located at denodo-vdp-odbcdriver-windows.zip\scripts\ so multiple versions can be used at the same time. Follow the README at the aforementioned path for further instructions.