Blog

Using OAuth to get data from external applications

  • 19 January 2022
  • 11 replies
  • 410 views
Using OAuth to get data from external applications
Userlevel 2
Badge

The Thinkwise Platform has powerful functionality to connect to external applications using OAuth. Customers often use external applications, for example a payment system. In that case they often want to import the data to the Thinkwise database, as the external system is their single source of truth. We can use the Thinkwise Platform to synchronize this data. With the support of system flows we can even automate this to automatically collect the latest data.

In this blog I will use Exact Online as the external application to synchronize the projects from Exact to a database.

Creating the data model

I made a very simple model for demo purposes. As a developer you can extract a lot more data from the API.

The data model contains two tables, the oauth_token_info table to save the OAuth access and refresh tokens. The project table contains the information we will extract from the Exact Online API.

Data model

App Registration

To call the API first we need to register an application in Exact Online. To do that you can go to the Exact Online App Centre and then go to “Manage my apps”, please note that an account is required to register an application. Here you can register an app by giving it a name and a redirect URI. In my case I used the name “tw_auth_demo” and the redirect URI “https://test.local/oauth-callback”. The Thinkwise platform assumes the redirect URI is “http://localhost/oauth-callback” but Exact Online forces https and doesn’t allow localhost.

After registering the app, you get a “Client ID” and a “Client secret”, we will copy this information to the Thinkwise Software Factory. Open the screen “Project overview” and select the right project and project version. You will see a tab for “OAuth servers”. Here we will add the information from the Exact Online App registration.

Registering the OAuth server in the Software Factory

Remember when I mentioned the issue with the “Redirect URI”? We will also resolve this right now. Go to the “Runtime configurations” tab and then the “Extended properties” tab. Here we will add the extended property “oauthredirecturi” with the value of your Redirect URI.

Logging in to the API

Now that we have most of the setup done, we can start creating some process flows. The first thing we want to do is obtaining the OAuth access and OAuth refresh tokens. Let’s start by creating the trigger for our process flow. To do that we create a task (I called mine “task_exact_online_oauth_login”), with task logic type set to “None” and we don’t add parameters. Add the task to the “oauth_token_info” table by using the “Table tasks” tab.

Next step is creating the process flow. I called mine “exact_online_oauth_login”. I added 3 process variables that I will use in this flow.

OAuth login process variables

The flow starts with the “Start table task” process action (with the task “task_exact_online_oauth_login”) we just created. This is the trigger for starting the flow. The next step is the “OAuth login connector”. This is responsible for showing the login screen and will give us the token information. Here, it is important to setup the output parameters to fill in the process variables that we have created earlier.

After creating the process flow it should look like the one in the picture below.

exact_online_oauth_login process flow

Now we will go to the “Functionality” screen to give an implementation for the OAuth login connector procedure. We will create a Control procedure called “prc_exact_online_oauth_login_insert_token_information” with the Code group “PROCESSES” and provide it with a template. This template will write the token information we got from the OAuth login connector to the database.

INSERT INTO oauth_token_info(oauth_server_id, date_created, date_last_updated, access_token, refresh_token, access_token_expires_in)
VALUES
(
'exact_online',
SYSDATETIME(),
SYSDATETIME(),
@var_access_token,
@var_refresh_token,
@var_expires_in
)

Remember to assign it to the “prc_exact_online_oauth_login_oauth_login_connector_exact_online” program object.

Now we have enough to start testing this part. After deploying all the code to the database (and creating a “Menu” in the Software Factory), we can start the application. When we start the task we have just created from the “oauth_token_info” table, the login screen of Exact Online appears. After logging in we should see a record in the oauth_token_info with the access and refresh token information. These tokens should be treated like a password. Leaking them will give potential unauthorized users the ability to get and change data on your behalf! This access token can be used for 10 minutes (600 seconds). So in the next chapter we will explain how to use this token in the HTTP Connector and explain how to extend the lifetime of the access token.

Calling the external API

It is time to start with the interesting part, the process flow for getting the data from the Exact Online API. To do this we create a process flow which contains some steps to load the access token from the database. It optionally refreshes the access token by using the OAuth refresh token connector and call the API using the HTTP Connector.

I called the process flow “exact_online_get_projects” and added 5 process variables to use in this process flow.

Exact Online get projects process variables

The first step in this process flow is a Decision, which will load the OAuth tokens from the database. This Decision will have two arrows, one for the OAuth refresh token connector and the other one will go to the HTTP Connector.

The OAuth refresh token connector has one input parameter. We will assign the variable “var_refresh_token” to this parameter. In the “Output” tab we assign the variables for the Access token, Expires in and Refresh token. In Exact Online, refresh tokens can only be used once. So we need to save the new token information. Saving the token information to the database we will do in the process procedure after finishing the process flow.

The HTTP Connector is the next step, this step will call the Exact Online API. The HTTP Connector requires multiple input parameters. We will give it a constant value for the URL: “https://start.exactonline.nl/api/v1/3153115/project/Projects?$select=ID,Code,Description”, the HTTP method should be “GET” and the headers we will give a constant value of

[ { "Key": "Accept", "Value": "application/json" }, { "Key": "Authorization", "Value": "Bearer {var_access_token}" } ]

Notice how the variable “var_access_token” is placed between curly braces; this is something Indicium will automatically replace for us with the variable.

We also want to save the content of the response to a variable. So in the “Output” tab we will assign the Content to “var_http_content_response”. This variable will hold the JSON returned by the API.

In the process procedure which we will create later, we will write the data from the Exact Online API to the database. We will also parse the JSON returned from the API.

After following the steps, your process flow should look like the one below.

exact_online_get_projects process flow

If you want Indicium to automatically call this process flow, you can add a “Process schedule”.

Just like last time, we will go to the “Functionality” screen and create a Control procedure called “prc_exact_online_get_projects” with the Code group “PROCESSES”.

Create a template with the following contents and assign it to the “prc_exact_online_get_projects_load_oauth_tokens” program object. This procedure will load the tokens from the database.

DECLARE @date_last_updated date_time;
DECLARE @access_token_expires_in oauth_expires_in;

SELECT
@var_access_token = t1.access_token,
@var_refresh_token = t1.refresh_token,
@date_last_updated = t1.date_last_updated,
@access_token_expires_in = t1.access_token_expires_in
FROM oauth_token_info t1
WHERE oauth_server_id = 'exact_online'

-- There was no refresh token. So we stop the process flow
IF @var_refresh_token is NULL
BEGIN
SET @load_oauth_tokens_oauth_refresh_token_connector_exact_online = 0
SET @load_oauth_tokens_download_projects = 0
RETURN;
END

-- We remove 30 seconds from the expire time, so we update the token before it is expired.
DECLARE @calculated_expire_date date_time = DATEADD(second, @access_token_expires_in -30, @date_last_updated)

IF SYSDATETIME() < @calculated_expire_date
BEGIN
-- When not expired, we disable the refresh token connector step and enable the download projects step
SET @load_oauth_tokens_oauth_refresh_token_connector_exact_online = 0
SET @load_oauth_tokens_download_projects = 1
END
ELSE
BEGIN
-- The token is (almost) expired, first go to the oauth_refresh_token_connector
SET @load_oauth_tokens_oauth_refresh_token_connector_exact_online = 1
SET @load_oauth_tokens_download_projects = 0
END

Create another template and assign it to the “prc_exact_online_get_projects_oauth_refresh_token_connector_exact_online” program object. This template updates the new token information to the database.

UPDATE oauth_token_info
SET
date_last_updated = SYSDATETIME(),
access_token = @var_access_token,
access_token_expires_in = @var_expires_in,
refresh_token = @var_refresh_token
WHERE oauth_server_id = 'exact_online'

The last template is the most interesting one, it will parse the JSON and write it to the database. So we will create another template and assign it to the “prc_exact_online_get_projects_download_projects” program object.

MERGE INTO project as p
USING (
SELECT item.ID as id,
item.Code as code,
item.Description as description
FROM OPENJSON(@var_http_content_response, '$.d') as results
CROSS APPLY OPENJSON(results.value)
WITH (ID VARCHAR(255), Code project_code, Description project_description) as item
) as item
ON (p.id = item.ID)
WHEN MATCHED THEN
UPDATE SET p.code = item.Code,
p.description = item.Description
WHEN NOT MATCHED THEN
INSERT (id, code, description)
VALUES (item.ID, item.code, item.description);

The process flow is now implemented, and we can start deploying all the code to the database from the Creation screen.

Testing the process flow

To test if everything works, I use Insomnia to start the process flow, if you added a “Process schedule” the data should already start showing up in the “Project” screen.

My Indicium instance is running locally, so I executed a POST request to “https://localhost:5001/iam/oauth_demo/exact_online_get_projects” using my credentials (which you can add in the “Basic” tab. More information about starting system flows is written in this blog post: https://community.thinkwisesoftware.com/news-updates-21/test-system-flow-during-development-by-indicium-api-2463.

 

Insomnia

After clicking on Send, you will see a 204 No Content. That is the expected result. When you refresh the project table in the Windows GUI (or directly with a query on the database), you should see your projects.

Windows GUI with Exact Online projects

I hope this post explains how to use the OAuth Login and OAuth Refresh token connector and how you can use them with the HTTP Connector. This technique can be used by a lot of external applications to synchronize data from the external application to the database, but also from the database to the external application.


11 replies

Userlevel 3
Badge +14

Dear Dick,

This looks like a clear explanation. :thumbsup_tone1:

Before I test it myself, I would like to know whether it will also be possible to let end users connect independently with their own Office 365 environment in a secure way.

For example, to read a mailbox or to synchronize personal tasks and contacts.

Harm

Userlevel 2
Badge

Hi Harm,

Yes this is possible but requires to save a refresh and access_token for every user (as far as I know). So they must each login once.
Small note, these tokens must be treated as passwords and are very sensitive! Request only the scopes you need - and make sure no users have access to read the table with the token information.

I just did a quick test and I noticed a few things which might be of interest.

  • The refresh token for Microsoft is a lot larger (so make it at least 2048) instead of what I have in this blog.
  • Microsoft doesn't return the refresh_token by default, I used the scopes: `Contacts.Read offline_access` in the OAuth server for my test.

After that I could read all contacts from my Microsoft personal account using the same technique described in the blog post.

Userlevel 3
Badge +10

Hi Dick,

Great article! Would it be possible to create a reusable process flow for the oauth part? 

Userlevel 4
Badge +1

@Dick van den Brink Thanks for writing these details instructions! I'm trying to get it working with the Microsoft Graph API, but encounter some hickups in the login Process Flow:

  • When running the Process Flow in Universal GUI no Consent pop-up shows up, instead the following error is thrown in the Indicium log: “[err] Process action 'oauth_login_connector_microsoft_graph_api' in processflow 'ms_graph_api_oauth_login_flow' returned the following message: Expected the OAuth2 server to redirect but received status code OK instead. (e6d43b77)”
  • When running the Process Flow in the Windows GUI I do get the Consent page and can authenticate as expected, however it ends with this Admin consent page. This page should not occur, since the permissions have been granted by the Admin before. And even after the Admin again consents to the received request, the next time the Admin consent page pops up again.

 

  • When running the same Oauth call in Insomnia (based on these instructions from @Vincent Doppenberg) it all works fine and I don't get the Consent page (my admin already approved these rights for me earlier on).
  • EDIT: as mentioned by @Dick van den Brink in the comments: for the Graph API Refresh token the VARCHAR(1024) is not long enough, the Refresh token is actually 1050 characters:
    Refresh token length

     

Is there anything I must do differently, or are we encountering bugs here (in that case I'll raise it in TCP)?

Userlevel 2
Badge

Hi Arie,

I tested this with my personal Microsoft account and with my Thinkwise account on the Microsoft Graph API, but I think we don’t have this extra `admin consent` enabled. I got one consent screen where I could just click “allow” and it was working correctly.

I think it is best to create a TCP ticket for this issue, I found some code that could potential be responsible for this - but it feels a bit weird that AzureAD would react this way to that setting.. 
Probably you have in your AzureAD: Azure Active Directory → Enterprise applications → Consent and permissions, the setting “Do not allow user consent” enabled? 

As for the Universal GUI, I don’t think the OAuth Login Connector works in Universal (yet). According to the feature matrix in TCP:

 

Userlevel 4
Badge +1

Probably you have in your AzureAD: Azure Active Directory → Enterprise applications → Consent and permissions, the setting “Do not allow user consent” enabled? 

@Dick van den Brink You were right, we have now updated that in Azure AD, although still getting same Admin consent screen. Will debug a bit more with our Azure engineer before raising a TCP.

As for the Universal GUI, I don’t think the OAuth Login Connector works in Universal (yet). According to the feature matrix in TCP:

Thanks for checking! Raised TCP 2773 to ask for Universal GUI support...

Userlevel 2
Badge

I prefer to also make it work with the setting enabled, I can not test this right now because I don’t have enough rights for this. I think we are forcing the `consent` page in the Windows GUI with the authorize_url (with &prompt=consent) - if you add this in Insomnia (and delete cookies) it probably also won’t work anymore.

 

Userlevel 4
Badge +1

I prefer to also make it work with the setting enabled, I can not test this right now because I don’t have enough rights for this. I think we are forcing the `consent` page in the Windows GUI with the authorize_url (with &prompt=consent) - if you add this in Insomnia (and delete cookies) it probably also won’t work anymore.

@Dick van den Brink Alright, I have raised TCP 2774. The Prompt is an optional setting in the Graph API ánd there are multiple options available. It should be possible to configure this setting ourselves in the SF. For more info, see here the various Prompt options:

https://docs.microsoft.com/en-us//azure/active-directory/develop/v2-oauth2-auth-code-flow#request-an-authorization-code

 

On a sidenote, this article is a great help in reviewing Azure AD setup on possible causes of the Admin consent prompt: https://blogs.aaddevsup.xyz/2020/04/troubleshooting-consent-in-azure-ad/

Userlevel 1

@Dick van den Brink Hi Dick,

Thanks for the elaborate instruction, I’m also trying to get this working with the Microsoft Graph API, however I’m running into some problems with using the OAuth login connector. When I start the process flow it gives me the prompt to login, however it won’t set the output variables. 

Upon further inspection using the process flow monitor I noticed that the action of the OAuth login connector returns an error code -2. Which according to the documentation means: 
"-2 Unsuccessful, aborted. The user has aborted the login screen" 
However there I’m not aborting anything.
 

When I try to fetch the token with Insommnia, it all works fine and it returns the tokens. To make things even weirder: when I check the debug is I see “Show loginscreen completed” followed by the token that’s also shown in Insomnia.

Did you, by any chance, run into similar problems or better yet know how I can fix this?  

Userlevel 4
Badge +1

@Dick van den Brink @Vincent Doppenberg any clues on the question from @HarryA? We’re still struggling to get the OAuth connector flow to work unfortunately!

Userlevel 2
Badge

I missed this one last week, sorry about that.

Probably best to create a ticket in TCP with the contents of the `Debug` window. That way we can see what is going on. 

Reply