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. 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.
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
Note: configuring the ODBC DSN with the following steps requires the Identity Provider to support, and be configured with, the Resource Owner Password authentication flow. Client credentials flow cannot be configured with this configuration but only using a DSN-less connection.
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 hostname of the Virtual DataPort server as well as the 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 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¶m2=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 have 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, you can navigate the Vitual 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 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 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
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
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.