OAuth Made Easy With ODBC Drivers

Out in the world, most of the data sources guarded with OAuth based authorization to secure access to data. OAuth specification supports different grant types to cater to variety of applications and use-cases. Most widely used and secure grant type is 'Authorization Grant' which requires human interaction during the process of obtaining an Access and/or Refresh token.

Typically, there are two tokens that an OAuth Server can return. An 'Access Token' is a short lived token that enables access to the data.  A 'Refresh Token' enables clients to request for a new 'Access Token' without any human interaction in order to continue to access data when an earlier access token gets expired.

Most of the Progress DataDirect drivers make it easy to obtain the initial Access and Refresh tokens via an intuitive User Interface. Once the Refresh token is obtained, the drivers are capable to discard the expired access token and request for a new access token from the OAuth Servers transparently to provide continuous access to data.

As an example, we use Progress DataDirect Sharepoint ODBC driver to demonstrate the capabilities around OAuth. There are two choices for the users to get through OAuth depending on the type of the client applications they wish to use.

1. Pre-fetched OAuth Tokens
2. Dynamic OAuth Tokens

We will learn about these, more in detail in the next section.

Applications that require access to data without any human intervention can pre-fetch the Access Token and Refresh Token once.  With the refresh token in place, the driver would automatically obtain a new access token when an existing access token expires. This way, applications would have continuous access to data.

Pre-requisites

Download and Install Progress DataDirect Sharepoint ODBC Driver.
OAuth Client application needs to be setup on the source side.
Refer to the full documentation here.

Follow the steps mentioned below, to pre-fetch the tokens

1. From Windows Start menu, launch ODBC Administrator Application
2. Choose Add , select DataDirect Sharepoint ODBC Driver, click Finish
3. This would launch a Configuration Manager for Sharepoint driver to configure a DSN
4. Fill-in the required details as shown in the below screen and click on 'Fetch OAuth Token' button
   
   
   
5. This would initiate an OAuth Handshake with the corresponding OAuth Server and present a Login screen like the one below
   
   
   
6. Enter your credentials and in the authorization screen provide your consent
7. In the background, Configuration Manager will request for the tokens and obtained tokens get filled into the Access Token and Refresh Token fields
8. Since, we have the Refresh Token configured as part of the DSN, the driver would get a new access token when the earlier one expires and continue to provide access to the data.
9. Save the DSN and you are good to use this DSN from an application of your choice, it could be PowerBI, Tableau or Excel or any other tool that requires ODBC Connection.

In some use-cases, if you have a need to have the client applications dynamically perform the OAuth handshake via the underlying ODBC driver, Progress DataDirect Sharepoint ODBC driver make that a possibility.

In this case, we will see how the driver initiates OAuth handshake when the Microsoft Excel application is being used.

1. From Windows Start menu, launch ODBC Administrator Application
2. Choose Add , select DataDirect Sharepoint ODBC Driver, click Finish
3. This would launch a Configuration Manager for Sharepoint driver to configure a DSN
4. Note that this time, we do not use 'Fetch OAuth Token' button, but turn on the 'Enable Login Prompt' option as shown in the below screen.
   
   
   
5. In addition, go to SQL Engine tab and choose SQL Engine mode as '2'. Save this DSN.
6. Launch your client application i.e., Microsoft Excel application in this example
7. Select 'Data' tab and choose 'Get Data' option
8. Choose 'Other Sources' and 'From ODBC' option
9. From the list of DSN drop down, choose the one we created in the Step#4
10. MS Excel tries to create a connection and Progress DataDirect Driver recognizes the fact that it has to initiate an OAuth Handshake.
11. A Login screen will be presented in a browser for the user to enter the credentials and provide his consent.
12. After a successful login, consent process, the following message is shown to the user.
    
    
13. Till this OAuth handshake process completes, excel will wait for the connection to succeed as shown below.
    
    
    
14. Once the driver obtains the access token in the background and connection succeeds, excel will show up the data as shown below

Depending on the level of security you care, this feature is particularly useful when the tokens are not saved as part of the DSN, but use the driver's ability to dynamically obtain required tokens and get access to data.