Goal

This document explains how to connect to the Denodo Platform through ODBC using OAuth Authentication.

Accessing Denodo through ODBC Driver

The Denodo ODBC driver supports the OAuth 2.0 protocol for user authentication and authorization. The Denodo ODBC driver supports the following OAuth authentication methods when connecting to Denodo using an ODBC DSN.

• Resource Owner Password Credentials - The driver obtains OAuth tokens using the user's credentials, allowing applications to authenticate without handling the token flow directly.
• Authorization Code Grant  - This OAuth 2.0 flow allows the driver to obtain an access token through user interaction with an authorization server, enabling secure access to Denodo resources without exposing user credentials.
• Refresh Token Flow - This flow enables the driver to obtain a new access token using a valid refresh token, allowing continued access to protected resources without requiring the user to re-authenticate.
• Client Credentials Grant - This flow allows an application to obtain an access token using only its client ID and secret, without involving a user. It is typically used for server-to-server or background service communication.
• Authentication providing access token - The ODBC client application obtains an OAuth access token and passes it to the driver.

Before using the OAuth protocol to connect to Virtual DataPort, make sure that OAuth Authentication is enabled in Virtual DataPort. Otherwise, the authentication will fail. For your reference: Enabling OAuth Authentication.

Installing the ODBC Driver

Follow the section Install the ODBC Driver on Windows or Register the Denodo ODBC Driver in Unix ODBC.

Connecting to Denodo using ODBC DSN with OAuth Authentication

Note: Configuring the ODBC DSN with the following steps requires the Identity Provider to support, and be configured with, one of the following authentication flows:

• Authorization Code Flow
• Resource Owner Password Credentials
• Client Credentials Grant

Resource Owner Password Credentials

  After installing the ODBC driver, the DSN must be configured with OAuth Authentication to connect to the Virtual DataPort Server. To do this, open the Windows ODBC Data Source Administrator as an administrator and select the “Add” option to create a new data source

Choose either DenodoODBC ANSI or DenodoODBC Unicode driver and click on  Finish. Note that we recommend using the DenodoODBC Unicode driver.

Choose a name for this data source and set the Database name as the virtual database you want to connect to, for instance, “admin”. Specify the hostname of the Virtual DataPort server as well as the port number for establishing the ODBC connection, by default 9996.

Enter the credentials of the Identity Provider in the “Username” and “Password” fields to obtain the Access Token

After this step, we need to set the required OAuth parameters in the OAuth 2.0 settings dialog section of the Driver. This section is found under the Options “Datasource > Page 3”.

Select the check box “Enable OAuth 2.0 authentication” and choose “Resource Owner Password Credentials” from the dropdown.

Specify the parameters for OAuth authentication using Resource Owner Password flow such as:

• Token Endpoint: This endpoint is the URL exposed by the OAuth server and used to request the access_token. This URL will look something like this depending on the identity provider you are using:
  https://login.microsoftonline.com/common/oauth2/token 
  or
  https://dev-XXXXXXXX.okta.com/oauth2/default/v1/token
• Client ID:  This is the Application’s Client ID. Usually, you can obtain this when registering the client application in the Identity Provider.
• Client Secret: Application’s Client secret.
• Scope: Space-delimited list of requested scope permissions. For instance: api://<client_id>/role or Openid
• Extra parameters: Additional parameters that will be added to the body of the HTTP requests and 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.

Once the parameters are specified, click on “Ok” and select “Test Connection” to check if the connection is successful.

After this, you can use this DSN for connecting from any ODBC client applications to the Virtual DataPort Server. For instance, we will now connect from Microsoft Excel using this ODBC DSN for connecting to the Denodo Platform with OAuth Authentication.

Click “ok”

Here, you can navigate the Virtual databases and select the view you want to import.

Click “Load” or “Transform Data” to complete the import.

Authorization Code Grant

Create a DSN datasource as created for ROPC flow, Choose a name for this data source and set the Database name as the virtual database you want to connect to, for instance, “admin”. Specify the hostname of the Virtual DataPort server as well as the port number for establishing the ODBC connection, by default 9996.

For the Authorization Code Grant flow, the “Username” and “Password” fields are not required.

Navigate to the “Datasource” >“Page 3”, select the check box “Enable OAuth 2.0 authentication” and choose the “Authorization code”

Specify the parameters for OAuth authentication using Authorization Code Grant flow such as:

• Token Endpoint: This endpoint is the URL exposed by the OAuth server and used to request the access token. This URL will look something like this depending on the identity provider you are using:
  
  https://login.microsoftonline.com/common/oauth2/v2.0/token 
  or
  https://dev-XXXXXXXX.okta.com/common/oauth2/default/v2/token

• Authorization - The Authorization URL is the endpoint used by clients to start the OAuth 2.0 Authorization Code flow.

For instance:

https://login.microsoftonline.com/common/oauth2/v2.0/authorize

Or

https://dev-XXXXXXXX.okta.com/common/oauth2/default/v1/authorize

• Client ID:  This is the Application’s Client ID. Usually, you can obtain this when registering the client application in the Identity Provider.
• Client Secret: Application’s Client Secret.
• Scope: Space-delimited list of requested scope permissions. For instance: openid
• Extra parameters: Additional parameters that will be added to the body of the HTTP requests and 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.

Click “Apply” and “ok”.

Now give ‘Test Connection”, The process will launch your default browser and present a page resembling the example below.

Note: During a test connection, a random port between 8000 and 9000 is generated to complete authentication. To ensure the connection works, this port must be registered as a Redirect URI in your application. Since the port number changes with each attempt, configuring the Redirect URI as http://localhost:0/ allows all possible ports in this range to be accepted. For more information please refer to the Loopback Interface Redirection.

This redirection occurs entirely within the local application environment, meaning it does not navigate to external websites. Therefore, using http://localhost:0/ is considered safe and secure. For detailed information, please refer to the Redirect URI (reply URL) guidelines and restrictions specific to Microsoft Entra ID. If you are using a different identity provider, please verify their respective requirements for redirecting URIs.

It is also possible to specify the static port. The parameters for configuring the ports are OAuthServiceMinPort and OAuthServiceMaxPort, this can be configured in the ‘Registry Editor’ for Windows.

Now, you could use this DSN for connecting from any ODBC client applications to the Virtual DataPort Server. For instance, we will now connect from PowerBI using this ODBC DSN for connecting to the Denodo Platform with OAuth Authentication.

In the “Denodo Connector” pop-up window, provide the ODBC DSN name in the “DSN or Connection String” section.

Click “ok”

Here, you can navigate the Virtual databases and select the view you want to import. Click “Load” or “Transform Data” to complete the import.

Refresh Token Flow

In this flow, an access token and a refresh token are supplied as parameters. When the access token expires, the refresh token is used to automatically obtain a new access token, ensuring uninterrupted authentication.

Create a DSN data source as created for ROPC flow, Choose a name for this data source and set the Database name as the virtual database you want to connect to, for instance, “admin”. Specify the hostname of the Virtual DataPort server as well as the port number for establishing the ODBC connection, by default 9996.

For this flow,  the “Username” and “Password” fields are not required.

In the “Datasource” >“Page 3”, select the check box “Enable OAuth 2.0 authentication” and choose the “Refresh Token” flow.

Specify the parameters for OAuth authentication using Resource Owner Password flow such as:

• Token Endpoint: This endpoint is the URL exposed by the OAuth server and used to request the access_token. This URL will look something like this depending on the identity provider you are using:
  https://login.microsoftonline.com/common/oauth2/token 
  or
  https://dev-XXXXXXXX.okta.com/oauth2/default/v1/token
• Client ID:  This is the Application’s Client ID. Usually, you can obtain this when registering the client application in the Identity Provider.
• Client Secret: Application’s Client secret.
• Scope: Space-delimited list of requested scope permissions. For instance: openid
• Extra parameters: Additional parameters that will be added to the body of the HTTP requests and the driver will send to obtain OAuth tokens. The syntax of the value of this parameter is param1=value1&param2=value2&...
• Access Token: Generate an access token using the ‘OAuth Credentials Wizard” for more information on this please refer to How to get the OAuth access token to connect to Denodo Knowledge Base Article
• Refresh Token: Generate a refresh token through the “OAuth Credentials Wizard” by adding  “offline_access” as a scope along with the actual scope.
• 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.

Once the parameters are specified, click on “Ok” and select “Test Connection” to check if the connection is successful.

Now, you could use this DSN for connecting from any ODBC client applications to the Virtual DataPort Server. For instance, we will now connect from PowerBI using this ODBC DSN for connecting to the Denodo Platform with OAuth Authentication.

Click “ok”.

Here, you can navigate the Virtual databases and select the view you want to import.

Click “Load” or “Transform Data” to complete the import.

Client Credentials Grant

The Client Credentials Grant is an OAuth 2.0 authorization flow designed for server-to-server communication, where no user context is involved. In this flow, the client application authenticates directly with the authorization server using its client ID and client secret.

Once authenticated, the authorization server issues an access token that the client can use to access protected resources. This flow is ideal for background services, daemons, or any application that needs to securely access APIs or resources on its behalf.

Create a DSN data source, Choose a name for this data source, and set the Database name as the virtual database you want to connect to, for instance, “admin”. Specify the hostname of the Virtual DataPort server as well as the port number for establishing the ODBC connection, by default 9996.

For this flow, the “Username” and “Password” sections are not required.

In the “Datasource” >“Page 3”, select the check box “Enable OAuth 2.0 authentication” and choose the “Client Credentials” flow.

Specify the parameters for OAuth authentication using Client Credentials Grant flow such as:

• Token Endpoint: This endpoint is the URL exposed by the OAuth server and used to request the access_token. This URL will look something like this depending on the identity provider you are using:
  https://login.microsoftonline.com/common/oauth2/token 
  or
  https://dev-XXXXXXXX.okta.com/oauth2/default/v1/token
• Client ID:  This is the Application’s Client ID. Usually, you can obtain this when registering the client application in the Identity Provider.
• Client Secret: Application’s Client secret.
• Scope: Space-delimited list of requested scope permissions. For instance: For the scope parameter add /.default at the end

      For instance: &scope=api://*********************/.default

• Extra parameters: Additional parameters that will be added to the body of the HTTP requests and 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.

                     

Once the parameters are specified, click on “Ok” and select “Test Connection” to check if the connection is successful.

Now, you could use this DSN for connecting from any ODBC client applications to the Virtual DataPort Server. For instance, we will now connect from Excel using this ODBC DSN for connecting to the Denodo Platform with OAuth Authentication.

Click “ok”.

Here, you can navigate the Virtual databases and select the view you want to import.

Click “Load” or “Transform Data” to complete the import.

Connecting to Denodo using ODBC DSN-less connection with OAuth Authentication

Some of the ODBC tools support connecting to an ODBC server without using a data source name (DSN). These connections are referred to as DSN-less connections.

In these DSN-less connections, we can equip both the OAuth Authentication flows such as Authorization Code Grant, Resource Owner Password Credentials, Client Credentials Grant and Refresh Token flow to connect to Denodo Platform.

Now, we are going to see how to establish a DSN-less connection to the Denodo Platform using OAuth Authentication from Microsoft Power BI as an example, an ODBC client application. Note that we are using the Power BI application for this demonstration on the DSN-less connection, But, Power BI can connect to the Denodo Platform using the ODBC DSN approach as well.

In the Power BI desktop, choose the “Get Data” option and select “ODBC” as the connection type.

In the “From ODBC” dialog box, choose the DSN as “(None)”  as we are not going to use any ODBC DSN for this connection.

Then, expand the “Advanced options”. On doing so, you can see the Connection String section which will be displayed at the bottom. This is where we need to set the Connection String with all the necessary parameters for establishing the connection. Please note that the default DSN-less connection String incorporating all the DSN setting will look like :

DSN-less connection from a 64-bit client

DRIVER={DenodoODBC Unicode(x64)};UID=<user account>;PWD=<password of the user>;SERVER=<host name>;DATABASE=<database name>;PORT=9996;SSLmode=prefer;service=;krbsrvname=HTTP;UserAgent=<user agent>;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=set+i18n+to+us%5fpst%3b;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=0;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;PreferLibpq=1;GssAuthUseGSS=0;XaOpt=3;UseKerberos=0

DSN-less connection from a 32-bit client

DRIVER={DenodoODBC Unicode};UID=<user account>;PWD=<password of the user>;SERVER=<host name>;DATABASE=<database name>;PORT=9996;PWD=;SSLmode=prefer;service=;krbsrvname=HTTP;UserAgent=<user agent>;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=set+i18n+to+us%5fpst%3b;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=0;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;PreferLibpq=1;GssAuthUseGSS=0;XaOpt=3;UseKerberos=0

Method - 1 Authentication providing access token

To use the first OAuth Authentication using Access Token, we need to perform the following steps:

• Enter the DSN-less connection string with two OAuth parameters such as UseOAuth2 and Access Token.

• UseOAuth2: Set this as 1 to enable OAuth 2.0 authentication.
• AccessToken: the value is the access token obtained by the client application.

Note: To get the access token, you can use the OAuth credentials wizards from the Virtual Dataport Administration Tool or any API clients such as Postman. For more information, you can refer to the How to get the OAuth access token to connect to Denodo Knowledge Base Article.

• Remove the parameters UID and PWD from the current connection String.

 

After adding the two OAuth parameters along with its values and removing UID and PWD  parameters, the connection string will be:

DRIVER={DenodoODBC Unicode(x64)};SERVER=localhost;DATABASE=admin;PORT=9996;SSLmode=prefer;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=set+i18n+to+us%5fpst%3b;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=0;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;PreferLibpq=1;GssAuthUseGSS=0;XaOpt=3;UseKerberos=0;UseOAuth2=1;AccessToken=<token>

Now, you can click Ok. Once done, you will get the dialog box to enter the username and password for connecting to the Denodo Platform in the Database panel. Finally, click Connect.

As an alternative, you can choose “Default or Custom” as the Authentication method and then click Connect.

On a successful connection, you can introspect the elements of the Virtual DataPort server.

 

Method 2 - Resource Owner Password Credentials

The next OAuth Authentication flow is the Resource Owner Password Credentials. In this method, we need to provide all the OAuth Parameters in the Connection String which will be used to obtain the Access Token from the Identity Provider.

To use this method, you could follow the below steps:

• Provide a value for the parameters UID and PWD. For these, provide the credentials in the Identity Provider to obtain an access token.

• Add the OAuth parameters to the connection string such as UseOAuth2, TokenEndpoint, ClientId, ClientSecret, Scope, UseIdToken, OAuthSSLVerify. You can refer to the explanation of each parameter in the Connecting to Denodo using ODBC DSN with OAuth Authentication section.

In the DSN-less Connection String, use the below parameters:

DRIVER={DenodoODBC Unicode(x64)};SERVER=localhost;DATABASE=admin;PORT=9996;SSLmode=prefer;ReadOnly=0;Protocol=7.4-1;FakeOidIndex=0;ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;ConnSettings=set+i18n+to+us%5fpst%3b;Fetch=100;Socket=4096;UnknownSizes=0;MaxVarcharSize=255;MaxLongVarcharSize=8190;Debug=0;CommLog=0;Optimizer=0;Ksqo=0;UseDeclareFetch=1;TextAsLongVarchar=1;UnknownsAsLongVarchar=0;BoolsAsChar=0;Parse=0;CancelAsFreeStmt=0;ExtraSysTablePrefixes=dd_;LFConversion=1;UpdatableCursors=0;DisallowPremature=0;TrueIsMinus1=0;BI=0;ByteaAsLongVarBinary=0;UseServerSidePrepare=0;LowerCaseIdentifier=0;PreferLibpq=1;GssAuthUseGSS=0;XaOpt=3;UseKerberos=0;UseOAuth2=1;TokenEndpoint=<your_token_endpoint>;ClientId=<client_id>;ClientSecret=<client_secret>;Scope=<scope>;UseIdToken=0;OAuthSSLVerify=1;

Once done, click OK.

In the next window, click on the “Database” panel. Insert “User name” and “Password” and click “Connect”.

Alternatively, click on the “Default or Custom” panel and, under the “Credential Connection String properties”, please include the UID and PWD parameters which are going to be the credentials for your Identity Provider.

On doing so, the ODBC driver will obtain the access token necessary for establishing the connection.

After connection establishment, you can explore the elements inside the Denodo Platform.

Troubleshooting

The Windows ODBC manager generally provides only generic errors when testing authentication and connection to a database. The driver can be configured to log all the requests received by the DSN: from the configuration of the DSN, click Datasource > Advanced Options dialog, select CommLog (C:\denodoODBC_xxx.log) and MyLog (C:\mylog_xxx.log).

In Linux and Unix systems, when the “Debug” and “Commlog” flags of the UnixODBC configuration file are set, the driver logs detailed ODBC information inside the/tmp directory. Logs folder can be customized by editing the odbcinst.ini file (Logdir = /path/to/logs).

In a production environment, it is strongly recommended to disable the logging of the ODBC requests as it can result in large space occupation.

References

ODBC Driver download page

Virtual DataPort Developer Guide - Access through ODBC

Virtual DataPort Developer Guide - Creating a DSN-less connection

Virtual DataPort Developer Guide - Configuration of the ODBC Driver on Windows

Virtual DataPort Developer Guide - Configuration of the ODBC Driver in Linux and Other UNIX