Integrating OAuth Authorization Code Grant with CData Connectors


Overview

OAuth is a common authentication mechanism and is used across a variety of services and APIs that the CData drivers communicate with. There are many things to consider when working with OAuth, such as how the token will be retrieved, refreshed, and stored. How these processes are handled will vary based on the application (e.g. desktop application, web application, or headless machine) as well as the use case and environment (e.g. multiple users, access to a file store, clustered environments, etc.). This document provides an in-depth look into how the CData drivers work with OAuth for a variety of use cases and the implementation methods available. The principles described here can be used across all drivers that utilize OAuth as an AuthScheme. The driver help documentation contains details specific to the service and should be referenced in addition to this document. Please note, OAuth can be difficult so if you have questions or want to discuss implementation techniques please contact our support team.

Retrieving OAuth Tokens

The first step in making an initial connection to a service that requires OAuth is to retrieve the OAuth token. How the OAuth token is retrieved will depend on the environment and application type (along with a few other factors). The CData drivers contain multiple mechanisms for retrieving an OAuth token and support using tokens retrieved from a source outside the driver.

Desktop Application (Application Capable of Opening a Web Browser)

For desktop applications, the CData drivers can initiate the OAuth flow by launching a browser to direct a user to login to the end service. This is controlled via the InitiateOAuth connection property when set to GETANDREFRESH. During this process, the driver creates a daemon to handle the HTTP response (Callback URL) of the login attempt and retrieve the OAuth token. In these cases, the CallbackURL is set to localhost on the port where the daemon is listening.

See the Creating a Custom OAuth Application section for more information about the user experience and branding of the OAuth flow.

Web Application

Web applications require that a user be directed to the service's authorization URL and then be redirected back to a page within the web application. The CData drivers are not capable of automating this flow, such as when used in a desktop application, as web applications require that a user be redirected back to a specific URL within the application (rather than to a daemon listening on localhost). Therefore, a custom OAuth application must first be created using the web application specific details. See the Creating a Custom OAuth Application section for more details about this process.

The drivers provide stored procedures to help initiate and manage the OAuth flow for web applications. The GetOAuthAuthorizationURL stored procedure is used to obtain the URL in which the user will be redirected to, allowing them to authenticate with the service. After a user successfully authenticates to a service, the GetOAuthAccessToken stored procedure can be used to obtain the OAuth access token.

An execution overview (from the app/customer point of view) is as follows:

  1. Application calls the following stored procedure: 
    EXECUTE GetOAuthAuthorizationURL
    AuthMode='WEB',
    CallbackURL='https://oauth.yourdomain.com',
    State='AppURL=customerhost.app.com:port'
  2. Application redirects the user to the URL returned by the above call to the GetOAuthAuthorizationURL stored procedure. This instructs the user to login to the service.
  3. The OAuth application will then redirect to https://oauth.yourdomain.com (as defined when the OAuth app was created). A small web app at oauth.yourdomain.com needs to parse the State parameter that is returned and then redirect the browser to that value (which should be your AppURL). You need to make sure the Verifier is also pushed back to the app. (Note, CData encodes these values before redirecting the browser back to the AppURL, so they are not plaintext parameters when coming into the app. But this is an optional decision CData does with its own implementation of this process.)
  4. Your web application will then need to read the Verifier from the HTTP response, which will be used to call the following stored procedure: 
    EXECUTE GetOAuthAccessToken
    Verifier='TokenFromHTTPResponse',
    AppMode='WEB'
    (This will return OAuthAccessToken, OAuthRefreshToken, and ExpiresIn)

Or you can simply pass the Verifier to the driver to get the token for you:

OAuthVerifier='CODE_FROM_REDIRECT';InitiateOAuth=GETANDREFRESH;

Headless Machines

For a headless server or a machine on which the driver cannot open a web browser, there are a couple of options to obtain an OAuth token. Some services (such as Google) enable support for Service Accounts which use silent authentication without requiring user authentication via a browser. See the Service Accounts section for more information about this process. If authentication with a User Account is desired, there are a few methods that can be used to obtain the OAuth access token.

One method is to install the driver on a separate machine and make a connection (to initiate the browser based OAuth flow). Once the OAuth flow has completed successfully, the OAuth token can be transferred to the headless machine (see more details in the Storing OAuth Tokens section for ways to transfer the token).

An alternative method, without installing the driver on another machine, is to obtain an OAuthVerifier value.

  • If using the CData embedded OAuth credentials (for services that support it), a link is provided in the driver's help documentation on the "Headless Machines" page. The link can be opened in a browser, to login and grant permissions to the driver, in which you will be redirected to the Callback URL which contains the verifier code.
  • If using a custom OAuth app, the verifier code can be retrieved by executing the following steps (driver specific details can be found in the help documentation):
    1. Set InitiateOAuth to OFF, along with any other required connection properties, on the headless machine.
    2. Call the GetOAuthAuthorizationURL stored procedure, with the CallbackURL input parameter to the exact Redirect URI specified in the app settings.
    3. Open the returned URL in a browser, log in, and grant permissions to the driver. Upon successful authentication, you will be redirected to the Callback URL which contains the verifier code.

Once a verifier code value is obtained, it can be set in the OAuthVerifier connection property on the headless machine. Set InitiateOAuth to REFRESH to enable the driver to use the verifier code to retrieve and refresh the OAuth token (see Refreshing OAuth Tokens for more information about refreshing tokens). Further details can also be found in the driver's help documentation.

OAuth Token Retrieved Outside of Driver

If an OAuth access token is retrieved prior to opening a connection, the access token can be specified via the OAuthAccessToken connection property. In this case, InitiateOAuth should be set to OFF or REFRESH to tell the driver that an OAuth token was already retrieved. The OFF setting indicates that the OAuth flow will be handled entirely by the containing application (not recommended as the driver will not be able to refresh the token thus may result in failed queries). The REFRESH setting indicates that the driver will only handle refreshing the OAuthAccessToken (more information about the refresh process can be found in the Refreshing OAuth Tokens section). If using the REFRESH setting, the OAuthRefreshToken should also be set to enable the driver to refresh the OAuth token. Optionally the OAuthExpiresIn and OAuthTokenTimestamp properties can be set. The driver uses these values to determine when the OAuth token needs to be refreshed. If not specified, the driver will refresh the token upon opening the connection to ensure the token is valid and obtain current Timestamp and ExpiresIn values.

Service Accounts

Some services utilize service accounts which use silent authentication, without user authentication in the browser. Service accounts can also be used to delegate enterprise-wide access scopes to the driver. To use a service account, a custom OAuth app must first be created. The driver help documentation contains details for how to create an app on the "Creating a Custom OAuth App" page. Following the steps on that page will enable you to create an app and obtain a JWT (JSON Web Token) certificate.

Service Accounts use a JWT certificate which will be exchanged for an OAuth access token (rather than user authentication via a browser). The driver enables support for service accounts via the OAuthJWT*** connection properties. The InitiateOAuth property can be set to GETANDREFRESH to enable the driver to retrieve an OAuth access token. Upon opening a connection, the driver will create and sign the JWT with the claim set required by the driver. The JWT will then be exchanged for the access token. The token will be stored (see Storing OAuth Tokens) and refreshed (by submitting the JWT for a new access token when the token expires).

Refreshing OAuth Tokens

CData drivers will automatically refresh OAuth tokens when InitiateOAuth is set to GETANDREFRESH or REFRESH. Both these settings behave the same when refreshing a token, the difference is that GETANDREFRESH will attempt to launch a browser to perform user authentication if a token is not available. The refreshing of an access token occurs behind the scenes and does not require user interaction.

When an access token is retrieved, a refresh token, expires in value, and a timestamp are also obtained. The timestamp identifies when the access token was retrieved. The expires in value identifies the life of the access token (which varies from service to service). The driver uses both the timestamp and the expires in value and compares it to the current system time to identify if the token has expired or not. If the token has not expired, the current access token is used. Otherwise, the driver will submit the refresh token to the OAuth app to retrieve a new access token, also updating the timestamp and expires in value. Note, the driver will refresh the access token if it has less than 10% of its life left to help avoid interruptions. Additionally, the driver will refresh the token in the middle of query execution as well. For instance, if a long query is running and the token becomes invalid while paging through the result set, the driver will receive the invalid token response, refresh the token, then re-submit the request using the new access token to continue with the execution of the query.

Storing OAuth Tokens

After an OAuth access token is retrieved, it can be stored so the driver can persist the access token across connections and processes. Multiple storage mechanisms are available within the driver:

  • Local File
  • Memory
  • S3 Blobs

Local File

By default, the driver will store OAuth tokens in a local file specified via OAuthSettingsLocation. Local file storage enables multiple connections to share credentials. The driver contains logic to handle race conditions and ensure that one connection does not invalidate the token of another connection. When a connection is closed, the local file remains on the file system and can be re-used when opening new connections (without the need for the user authenticated browser flow). The OAuthSettings file can also be transferred to another machine (such as a headless machine) to create connections there using the same credentials. When using an OAuthSettings file, only InitiateOAuth and OAuthSettingsLocation need to be set as all the OAuth*** connection property values will be read from the file.

The token value stored within the local file is encrypted, so the values cannot be easily read and are only decrypted by the driver.

In multiple user scenarios, a custom file path can be set for each user.

Memory

For use cases where a local file store is not a viable option, memory storage can be used. Memory locations are specified by using an OAuthSettingsLocation value starting with memory:// followed by a unique identifier for that set of credentials (e.g. memory://user1). The identifier can be anything you choose but should be unique to the user.

Unlike local file storage, the credentials must be manually stored when closing the connection to re-use them when opening a new connection. The OAuth values can be retrieved with a query to the sys_connection_props system table. The Value column for the OAuthAccessToken, OAuthRefreshToken, OAuthExpiresIn, and OAuthTimestamp properties (identified via the Name column) will contain the latest values that need to be stored. (If there are multiple connections using the same credentials, the properties should be read from the last connection to be closed.) The values returned by the system table are not encrypted, which should be taken into consideration when storing them. When opening a new connection, the same values retrieved from sys_connection_props can be set using the same corresponding OAuth*** connection properties. If OAuthExpiresIn and OAuthTimestamp are not specified, the driver will refresh the token upon opening the connection to ensure the token is valid and obtain current Timestamp and ExpiresIn values. Memory storage is shared across threads but not processes.

S3 Blobs

The CData drivers can store OAuth tokens in S3 blobs. This functionality is only available when running in an EC2 instance or AWS services that execute on EC2 (like Glue or Lambda), as the driver authenticates using the IAM Roles assigned to the EC2 machine the driver is running on. To enable this storage mechanism, OAuthSettingsLocation can be set to a value starting with s3:// followed by a unique identifier for that set of credentials (e.g. s3://user1). The driver has a built-in locking mechanism (to avoid race conditions) using empty S3 blobs.

Creating a Custom OAuth App

Embedded Credentials (Applicable to Desktop Applications)

CData has created OAuth applications and embedded the OAuth credentials into drivers where the services permit it. This enables users to connect to the service without first having to create a custom OAuth application, thus simplifying the connection process. During the OAuth flow, the end user will see CData branding on the web pages (such as on the permissions consent page and the success/error pages after a login attempt). The embedded OAuth app requests consent for scopes that pertain to all functionality available in the driver (e.g. read, write, etc.). To customize the end user experience and the requested scopes, a custom OAuth app will need to be created. Note, some services require a custom OAuth application be created and thus the driver does not contain embedded credentials. Information about embedded credentials (if applicable) can be found in the "Using OAuth Authentication" section of the driver help documentation.

Custom OAuth App

To preface the content below, the help documentation for each driver (that supports OAuth Authentication) contains information about creating a custom OAuth app (on the "Creating a Custom OAuth App" page). The help provides information and steps specific to each service, as well as a breakdown of what is needed for Desktop, Web Apps, and Headless Machines.

Each OAuth application has an associated ClientId and ClientSecret value that is generated. These values are what identify the OAuth application that is used. The OAuthClientId and OAuthClientSecret connection properties need to be set to enable the driver to authenticate using the custom OAuth app.

When creating an OAuth application, the rules of what you can define for Callback URLs will vary from source to source. Some allow you to define multiple Callback URLs, some only HTTPS, and others require an explicit port. If you need to generalize this process across all OAuth providers, it would be best to assume that you need an explicit URL with HTTPS and PORT defined.

What this means for a web application, is that you need to follow one of two patterns:

  1. All instances of the web application need their own OAuth application that points the Callback URL to them. (This is not a recommended approach, as it means separate OAuth applications for each deployment, for each customer).
  2. You define an intermediary Callback URL that then redirects to the proper web application. (This is what CData does for some of the embedded OAuth applications that do not allow for a callback of localhost).

The way this process works for #2 is that you would host a redirect URL on a publicly available domain website (e.g. CData uses oauth.cdata.com), and you would supply the actual Callback URL via the State parameter in the GetOAuthAuthorizationURL stored procedure call. OAuth is designed to echo the value passed in the State parameter back to you after the user has completed authentication. This means when the OAuth application redirects back, you can have your oauth.yourdomain.com URL read and redirect the user back to the appropriate application URL.

Custom Branding

When a custom OAuth application is created, customized consent pages can be created. This can include customized language and icons. This functionality is all contained within the OAuth app itself and is outside the driver functionality.

For Desktop Applications, the driver also exposes two connection properties to specify custom HTML pages after a successful and failed login attempt: OEMAuthSuccess and OEMAuthFailure. Both properties take a file path to an HTML file and are specified via the Other connection property.

By default, the driver displays a simple page identifying whether the authorization was successful or not. When using embedded credentials, CData branding will be displayed. If using a custom OAuth app (e.g. when OAuthClientId and OAuthClientSecret are specified), the CData branding is removed and a generic HTML page is used. If a more customized HTML page is desired, the OEMAuthSuccess and OEMAuthFailure options can be specified to accomplish this.

In this article

Related articles