Getting Started with OAuth in CData Connectivity Solutions

OAuth 2.0 is an authorization protocol that that allows an API client to have restricted access to user data on a web server. It is notably used by the APIs of prominent applications like Facebook, Google, GitHub and many more. OAuth is based on authentication scenarios, or "Grant Types" (flows) that let the resource owner or the user, without disclosing their credentials, share the resource server's protected content. To accomplish this, an OAuth 2.0 server generates access tokens that client programs can use to gain access to restricted resources on behalf of the resource owner. OAuth 2.0 details can be found at oauth.net and RFC 6749.

In this article, we describe the various processes for performing OAuth authorization using CData connectivity solutions, focusing on the "Code" or the "Authorization Code" OAuth Grant Type since it is the most common among all the OAuth 2.0 grant types that you'll encounter. The instructions below refer to the CData JDBC Driver for Salesforce, but the principals can be used regardless of the data source or client application being used. You need the ability to use custom SQL queries to trigger some of the OAuth flows.

Contents

• (Optional) Creating a Custom OAuth Application in Salesforce
• Performing OAuth with Desktop Applications
• Performing OAuth with Web Applications
• Performing OAuth with Headless Machines

(Optional) Creating a Custom OAuth App in Salesforce

The following describes the step-by-step process of how you can create a custom application in Salesforce:

1. Login to your Salesforce account

2. Go to "Setup" at the top right corner of the Salesforce account
3. Enter "Apps" in the Quick Find box, go to "App Manager" from the dropdown and then click on "New Connected App" the link to create an application

4. In the New Connected App section, enter "Connected App Name", "API Name", "Contact Email"

5. Enable the API (Enable OAuth Settings) checkbox and enter the "Callback URL" and select the required OAuth scope of permissions that your application should request from the user. Example: Scopes such as 'refresh_token', 'offline_access' permission is required to get a refresh token and perform requests at any time. To get data, Full Access (full) or API (Manage User Data via APIs) are to be used

6. Click on "Save" to create the customer OAuth application
7. Under API (Enable OAuth Settings) section of the New Connected app, click on "Manage Consumer Details" to obtain the "Client ID" (Consumer Key) and "Client Secret" (Consumer Secret) which we use later to connect to this application using the CData Salesforce JDBC driver

Performing OAuth with Desktop Applications

For Embedded OAuth Credentials

For many data sources, CData provides embedded OAuth credentials that simplify OAuth Desktop Authentication.

To connect with the Embedded OAuth credentials using the Salesforce JDBC Driver, the user needs to follow the given steps:

1. Download and install the CData JDBC Driver
2. Follow the installation path to the Salesforce JDBC Driver and open the "lib" folder.
   Path (example): C:\Program Files\CData\CData JDBC Driver for Salesforce 2022\lib
3. Open the executable JAR file "cdata.jdbc.salesforce.jar" to open the JDBC URL configuration wizard
4. Set the "AuthScheme" to OAuth and set "InitiateOAuth" to GETANDREFRESH
5. Click on "Test Connection"
6. The user gets rerouted to Salesforce login page where they need to provide all the required credentials (Username and Password)
7. Once the correct credentials are entered, "Salesforce Authorization Successful" screen pops up

8. The JDBC driver alerts that the connection was successful (refer below):

For Connecting with the Custom OAuth Application (Desktop)

1. Follow steps 1 - 4 mentioned in "For Embedded OAuth" from Desktop Authentication
2. Additionally, along with setting "Initiate OAuth" as GETANDREFRESH, enter the "Client ID" and "Client Secret" details saved from your custom OAuth application (see "Creating Custom OAuth Application in Salesforce" above)

3. When you connect, the driver opens the OAuth endpoint in your default browser. Log in and grant permissions to the application.

4. The driver then completes the OAuth process as follows:
   • Extracts the access token from the "Callback URL".
   • Obtains a new access token when the old one expires.
   • Saves OAuth values in "OAuthSettingsLocation" that persist across connections.

   Performing OAuth with Web Applications

   When connecting via a web application, you need to register a custom OAuth application with Salesforce. You can refer to "Creating Custom OAuth Application in Salesforce" section of this article for more information. You can then use the driver to get and manage the OAuth token values.

   NOTE: This process would normally be implemented in your web application using SQL stored procedures, but here, we execute the stored procedures with DBeaver.

   Follow the following steps to obtain the access and refresh tokens for connecting to the custom web application:

   (Optional) Create a Connection in Dbeaver

   1. Open DBeaver (Java-based tool) and go to the "Driver Manager" option in the Database dropdown

   2. Click on "New" and go to the "Libraries" tab and click on "Add File" to select the executable Jar file cdata.jdbc.salesforce. Select the path and click on "Find Class" and select the driver generated

   3. Select the tab "Settings" and add a new Driver name for testing (e.g.: CData_WebApp). Click on "OK". The database named "CData_WebApp" is now created in DBeaver

   4. Again, go to the "Database" dropdown and select "New Database Connection". Select the database created and click on "Next"

   5. Enter the connection URL in the JDBC URL field, click on "Test Connection" and then click on "Finish":

      jdbc:salesforce:OAuthClientID='XXXX';OAuthClientSecret='XXXX';InitiateOAuth=OFF;

      Please note the ClientID and ClientSecret are the details which are saved from the custom OAuth web application

   6. Once the connection string is defined, you can see the new database connection under "General" on the left panel as well as in the "Connections" tab of DBeaver tool. Right click on the connection, go to "SQL Editor" and click on "Open SQL Script". The same can be selected from the menu bar - "SQL Editor"

   Call Stored Procedures to Complete the OAuth Exchange

   1. Call the GetOAuthAuthorizationUrl stored procedure. Set the CallbackURL input to the callback URL you specified in your application settings. If necessary, set the Scope parameter to request custom permissions. The stored procedure returns the URL of the OAuth endpoint. For example, run the "GetAuthorizationURL" stored procedure:

      EXEC GetoAuthauthorizationURL
        @CallbackUrl='http://localhost:33333',
        @Grant_Type='CODE'; 

   2. Open the URL, log in, and authorize the application. You are redirected back to the callback URL in the browser. Simply copy the content of the code after the '=' operator in the obtained URL and use a URL Decoder (URL Decoder) to decode the actual value for this code. The decoded value now is your OAuthVerifier. Save this verifier somewhere on your machine.
   3. Call the GetOAuthAccessToken stored procedure. Set the AuthMode input to WEB. Set the Verifier input to the "code" parameter in the query string of the callback URL. If necessary, set the Scope parameter to request custom permissions. As indicated above, run the "GetOAuthAccessToken" stored procedure:

      EXEC GetOAuthAccessToken
        @appmode=WEB,
        @Verifier='XXXX',
        @GrantType='CODE';

   4. The access and refresh tokens are now obtained as shown in the figure above. You can now connect to data and refresh the OAuth access token either automatically or manually by the steps given below.

   Automatic Refresh of the OAuth Access Token:

   To have the driver automatically refresh the OAuth access token, set the following on the first data connection:

   • InitiateOAuth: Set this to REFRESH.
   • OAuthClientId: Set this to the Client Id in your application settings.
   • OAuthClientSecret: Set this to the Client Secret in your application settings.
   • OAuthAccessToken: Set this to the access token returned by GetOAuthAccessToken.
   • OAuthRefreshToken: Set this to the refresh token returned by GetOAuthAccessToken.
   • OAuthSettingsLocation: Set this to the path where the driver saves the OAuth token values, which persist across connections.

   On hitting "Test Connection", the driver alerts that the connection has been successful (indicated in the below figure)

   On subsequent data connections, the values for OAuthAccessToken and OAuthRefreshToken are taken from OAuthSettingsLocation.

   Manual Refresh of the OAuth Access Token:

   The only value needed to manually refresh the OAuth access token when connecting to data is the OAuth refresh token.

   Run the "RefreshOAuthAccessToken" stored procedure to retrieve new tokens after the "ExpiresIn" parameter value returned by GetOAuthAccessToken has elapsed:

   EXEC RefreshOAuthAccessToken 
     @OAuthRefreshToken='XXXX'

   Finally, store the OAuth refresh token so that you can use it to manually refresh the OAuth access token after it has expired.

   Performing OAuth with Headless Machines

   Headless machines are the ones with no UI. They do not support a browser and don't allow to open a browser prompt. Devices that don't need UI functionality can be set to headless mode. Here, the UI stack is disabled, and UI apps will not launch. This reduces the amount of system resources used.

   Some examples of headless machines are UNIX, CentOS, Linux, etc.

   To configure the driver, use OAuth with a user account on a headless machine. You need to authenticate on another device that has an internet browser.

   1. Choose one of two options:
      • Option 1: Obtain the "OAuthVerifier" value as described in "Obtain and Exchange a Verifier Code" below.
      • Option 2: Install the driver on a machine with a browser and transfer the OAuth authentication values after you authenticate through the usual browser-based flow.
   2. Then configure the driver to automatically refresh the access token on the headless machine.

   Option 1: Obtain and Exchange a Verifier Code

   1. Establish a successful connection with the driver (CData_Headless) on your local machine in some JDBC tool like DBVisualizer, DBeaver, etc. (using DBeaver here) with a sample connection string that looks something like:

      jdbc:salesforce:OAuthClientID='XXXX';OAuthClientSecret='XXXX';InitiateOAuth=OFF;

      As you can see, the connection string above needs to be defined with the OAuthClientID, OAuthClientSecret and InitiateOAuth as "OFF"

   2. Now, run the "GetOAuthAuthorizationUrl" stored procedure:

      EXEC GetOAuthAuthorizationUrl 
        @CallbackUrl='http://localhost:33333',
        @Grant_Type='Code';

      Here, please make sure that you have mentioned the correct CallbackURL that is set in your custom OAuth app as well. You can further choose to use http://localhost:33333 as your CallbackURL as well in the app's settings. Now, once you run the "GetOAuthAuthorizationUrl" stored procedure, you get a URL as a result. Copy the value of this URL field and run it in a new browser of your machine. You will see some error message like the one shown below and a code in the URL.

      Now, the new URL of this browser looks something like: (given in the below figure) http://localhost:33333/?code=XXXXXX%3D%3D...

      Simply copy the content of the above code after the '=' operator and use a URL Decoder to decode the actual value for this code. The decoded value now is your OAuthVerifier. Save this verifier somewhere on your machine.

   3. Now, run the "GetOAuthAccessToken" stored procedure:

      EXEC GetOAuthAccessToken 
        @Verifier = 'XXXX',
        GrantType='CODE'

      You can see the AccessToken under the OAuthAccessToken field. Copy this AccessToken and create a new connection URL in your Dbeaver JDBC URL instance something like:

      jdbc:salesforce:OAuthClientID='XXXX';OAuthClientSecret='XXXX';InitiateOAuth=REFRESH;OAuthAccessToken='XXXX';OAuthRefreshToken='XXXX';

      The above connection string contains the OAuthClientID, OAuthClient Secret, InitiateOAuth as "REFRESH", OAuthAccessToken and OAuthRefreshToken obtained from running the "GetOAuthAccessToken" stored procedure.

      This connection string which has been obtained can be copied and used directly in a Headless Machine to connect with the OAuth custom application.

      Also, to test the connection string, provide the exact details in the CData Salesforce driver JAR file. If it connects, the connection string is ready to go.

      Option 2: Transfer OAuth Settings

      Prior to connecting on a headless machine, you need to install and create a connection with the driver on a device that supports an internet browser. Set the connection properties as described in "Desktop Applications" above.

      After completing the instructions in "Desktop Applications", the resulting authentication values are encrypted and written to the path specified by OAuthSettingsLocation. The default filename is OAuthSettings.txt. After you have successfully tested the connection, copy the OAuth settings file to your headless machine.

      On the headless machine, set the following connection properties to connect to data:

      • InitiateOAuth: Set this to REFRESH.
      • OAuthClientId: (custom applications only) Set this to the client Id assigned when you registered your application.
      • OAuthClientSecret: (custom applications only) Set this to the client secret assigned when you registered your application.
      • OAuthSettingsLocation: Set this to the path to your OAuth settings file. Make sure this file gives read and write permissions to the driver to enable the automatic refreshing of the access token.