This article describes how to authenticate one of the many CData provider that support OAuth. This authentication method is very popular as it is considered one of the most secure methods of authenticating to a web service.
In the below example I will use the CData REST provider to connect to the Spotify REST API to get a list of albums by The Beatles. (You can create a free Spotify account and try this out as well.)
For more information you can review the CData documentation on Authenticating to REST from a Desktop Application
Find required info from the API documentation
Most APIs have some documentation that explains how to communicate with their API. While not all guides use the exact same flow or wording, the overall process is usually similar.
In this case, I simply searched the web for "Spotify web API developer documentation" and found the Authorization Guide.
You will need to find and record the following required details from the API documentation:
- URI -This is the URL or address of the file or REST Endpoint that you want to connect to.
- Typically, the documentation will provide you with a list of various Enpoints which are the different URLs you can connect to extract various datasets or (tables) offered by the API.
- In this example I will be using the Spotify "Artists Albums" Endpoint.
- OAuth Access Token URL: The is the REST API endpoint which will provide you with the required Access Token. This is typically a URL starting with http and ending with /token or similar.
- OAuth Access Token URL: This is not always required and is sometimes the same as the Access Toke URL above.
- OAuth Authorization URL: This is the URL which the web service uses to authenticate users through the web browser. This is typically a URL starting with http and ending with /Authorize or similar.
- OAuth Client ID
- OAuth Client Secret
- Both the Client ID and Client Secret are typically generated by registering an application (also referred to as "Create an App" or similar) through the web service developer interface. You should find the instructions on how to do this in the API documentation. For example here is the one for Spotify: Register your Application
- When registering your application, the API may ask you for the Callback URL or Redirect URI to be "whitelisted". You should provide the exact same URL used as the Callback URL below: http://localhost:33333.
Configure the REST Provider
In TimeXtender, Create a new data source and select the provider for CData REST 2020 and define the following required properties:
- Authentication > Auth Schem: OAuth
- Connection > URI: This is the Endpoint URI which contains the dataset you want to extract. In this case, I will use the following URI to extract the list of albums by The Beatles: https://api.spotify.com/v1/artists/3WrFJ7ztbogyGnTHbHJFl2/albums
- OAuth>Callback URL: http://localhost:33333 - This is address that the API will use to relay information back to the client, also known as the Redirect URI. The Cdata REST provider always listens on the client machine (locahost) on port number 33333.
- When registering your application, the API may ask you for the Callback URL or Redirect URI to be "whitelisted". You should provide the exact same URL above http://localhost:33333.
- OAuth>Initiate OAuth: GETANDREFRESH - Using this property will obatain and refresh the OAuth access token each time you connect (or execute).
- OAuth>OAuth Access Token URL: -This can be found in the source API Documentation (see above)
- OAuth>OAuth Refresh Token URL: -You only need to define this parameter if it is required, as it is not always required. Sometimes it is the same as the Access Toke URL above. This can be found in the source API Documentation (see above).
- OAuth>OAuth Authorization URL: -This can be found in the source API Documentation (see above)
- OAuth>OAuth Client ID: -This is typically generated by registering an application through the web service developer interface (see above).
- OAuth>OAuth Client Secret: -This is typically generated by registering an application through the web service developer interface (see above).
- OAuth>OAuth Grant Type: CODE -This is defined by the OAuth authorization flow being used, for OAuth 2.0 this is typically: CODE.
- OAuth>OAuth Settings Location: -You can leave this as default. But you must specify an .TXT file on the local server. It does not need to exist beforehand as it will be created or updated automatically. This file is where the REST provider will store the OAuth Access Tokens. By default it stores it in the current users APPData folder. Be sure that the service account running the ODX Server service (or scheduler service if using business units) has permissions to modify this file and can see it. For multiple environments and scheduled executions it should be set with a path to a specific location all users can see and has permissions to use.
- OAuth>OAuth Version: 2.0
Once the provider is properly configured you must Authorize the client using the steps below.
- Click the Authorize OAuth button at the bottom of the dialogue. If the Client ID and Client Secret are accepted, the Authorization URL will open in a web browser.
- Sign-In using your credentials to authorize the application. (Some API's will only expose the endpoints that the user account has access to. If this is the case you may want create a service account to use elevated privileges to access the desired endpoints).
- Once the Sign-in is successful, the API will send an Authorization code back to the Callback URL and display a message in the browser "OAuth Authorization Successful!"
- At this time, the REST Provider will use the Authorization Code to request the Access Token from the Access Token URL.
- The REST Provider retrieves the Access Token and stores the encrypted tokens in the OAuth Settings Location .txt file.
- Click Test Connection to ensure you are successfully connected and authorized.