Configuration of the ODBC Driver on Windows

The Denodo Platform provides an ODBC driver for Windows, which is based on the ODBC PostgreSQL driver.

To connect to Denodo from an ODBC application, follow these steps in the machine where the client application runs:

  1. Install the ODBC driver

  2. Register a new Data Source on Windows (DSN)

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 To do this:

    1. Copy it from the installation (<DENODO_HOME>/tools/client-drivers/odbc/

    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.


    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.


      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.


      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/, enter

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

Denodo ODBC driver: configuration dialog

Denodo ODBC driver: configuration dialog

  1. 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).


      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)

Denodo ODBC driver: advanced configuration (Page 1)

  1. 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)

Denodo ODBC driver: advanced configuration (Page 2)

  • 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:

Parameters of the ODBC driver and their default value

Connection Property


Default Value


Maximum time (in milliseconds) the driver will wait for a query to finish. After this period, it will throw an Exception. If 0, the driver will wait indefinitely until the query finishes.

This parameter sets the default timeout for all the queries. In addition, you can change the timeout for a single query by adding the parameter 'QUERYTIMEOUT' = '<value>' to the CONTEXT clause of the query. See more about this in the section CONTEXT Clause of the Advanced VQL Guide.

900000 milliseconds (15 minutes)


Sets the internationalization (i18n) configuration of the connection with the Server. If not present, the driver assumes the i18n of the database that you are connecting to.

The “date” fields of the queries’ results are converted to the time zone assigned to the i18n of the connection.

The parameter i18n in the CONTEXT clause of the queries overrides the value of this parameter.

<I18N of the database that you are connecting to>


When a view redefine published type name and sizes such information is used to propagate that values to clients accessing the server through JDBC or ODBC. That redefinition does not affect the value itself.

Some ODBC clients have difficulties receiving a decimal value with different size and scale.

Enabling this setting will modify decimal values sent to ODBC to update its size and scale.


  1. 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/, the value of Krbsrvname has to be HTTP.

Denodo ODBC driver: advanced configuration (Page 3)

Denodo ODBC driver: advanced configuration (Page 3)

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

Parameters for OAuth authentication



Token Endpoint

URL exposed by the OAuth server and used to request the access_token. For instance

Client ID

Application’s Client ID. Usually, you obtain this when registering the client application in the Identity Provider.

Client Secret

Application’s Client secret.


Space-delimited list of requested scope permissions.

Extra parameters

Additional parameters that will be added to the body of the HTTP requests the driver will send to obtain OAuth tokens. The syntax of the value of this parameter is param1=value1&param2=value2&...

Use id_token

If selected, the driver will use the “id_token” for authentication. If cleared, it will use “access_token”. Select this check box in an environment with “OpenID Connect”.

Check certificates

If selected (recommended value), the driver will validate the SSL certificate of the Identity Provider. If cleared, it will not validate the certificate.

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.

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

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

  3. 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\scripts\ so multiple versions can be used at the same time. Follow the README at the aforementioned path for further instructions.

Add feedback