Applies to :
- Denodo 8.0
Last modified on:
16 Sep 2021
Download: Document

You can translate the document:

Goal

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

Accessing Denodo through ODBC Driver

Starting from Denodo Platform 8.0 Update 20210209, the Denodo ODBC driver supports OAuth 2.0 protocol for authentication and authorization of users. The ODBC driver provides two methods to connect to Denodo using OAuth 2.0 :

Authentication providing access token - The ODBC client application obtains an OAuth access token and passes it to the driver
Resource Owner Password Credentials - If the client application does not have the capability of obtaining OAuth tokens, the driver can obtain one on behalf of the application.

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.

Note: Use the ODBC driver of the update 20210209 or a newer version to use this feature. This feature does not benefit client applications that use an older version of the Denodo ODBC driver.

Connecting to Denodo using ODBC DSN with OAuth Authentication

After installing the ODBC driver, we need to set up the DSN with OAuth Authentication to point to the Virtual DataPort Server. For that, you can open the Windows ODBC Data Source Administrator and click on 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 host name of the Virtual DataPort server as well as port number for establishing the ODBC connection, by default 9996.

In the Username and Password sections, you can give the credentials of the Identity Provider for obtaining 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”.

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 : https://login.microsoftonline.com/common/oauth2/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.
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.

Note: In case of the Okta Identity Provider, the client id and client secret has to be obtained using the Native Application as the Web Application does not support the Resource Owner Password flow.

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. As an example, we will now connect from Microsoft Excel using this ODBC DSN for connecting to the Denodo Platform with OAuth Authentication.

Click OK.

Here, choose Default or Custom Authentication and click Connect. On doing this, you will be able to connect to the Denodo Platform successfully.

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 Authentication providing access token and the Resource Owner Password Credentials 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 actual 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 Denodo Platform.

Here you can choose Default or Custom as the Authentication method and then click Connect.

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

Once done, click OK.

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