Learn more about authorizations on Exact Online in the explanation below and how Invantive Cloud helps you to easily solve and prevent authorization issues from the website.
The explanation below is specific to the Exact Online platform and is not applicable to other platforms. The explanation is applicable for all Exact Online countries.
Hundreds or a few Exact Online companies to Power BI
Invantive Cloud is a simple and powerful online software environment to unlock a few or hundreds of Exact Online companies towards Power BI, Power Query for Excel, Azure Data Factory or - for example - towards Python, Ruby or Google Functions. Over 1,000 Exact Online tables are available from both the XML and REST APIs thanks to Invantive SQL, providing the most extensive and flexible range of possibilities for small and large users in one Exact Online database.
Pull principle
Exact Online is intensively working to bring the security of its platform to a higher level, but the design of the choices is not always optimal for use in business critical applications. This regularly leads to failures when importing Exact Online data in Microsoft Power BI (both Microsoft Power BI Desktop and PowerBI.com) with the Invantive Cloud connector based on OData.
Unlike many other solutions, the Invantive Cloud connector is based on the pull principle: the processing only takes place when data is requested. This leads to virtually real-time data on Exact Online, even for thousands of companies. Thanks to extensive SQL optimizations this prevents Exact Online from being overloaded. Only when data is more frequently accessed from Exact Online smart so-called “caches” are used to increase the processing speed.
The disadvantage of the pull principle is that every service disruption in the entire chain up to and including Exact Online is immediately visible to end users. Invantive SQL therefore offers many unique features to solve such failures under the hood and still provide as much real-time data as possible. In the future, Invantive Cloud may offer a facility to prepare the Cloud in advance in order to reduce the impact of any instability of the Exact Online ecosystem on the processes.
Faults lurking when loading Exact Online
When loading the database from Exact Online, so-called “refresh tokens” are used; these are application-specific passwords that are the result of going through the so-called “Code Grant Flow” according to the OAuth specifications. The lifetime of a refresh token is extremely short on Exact Online in contrast to the regular long-term validity (see RFC6749 section 6).
In addition, Exact Online requires a rotation of refresh tokens from a ring with only one element; the OAuth specification calls this “challenging”.
The refresh token is used to obtain a short-form password; the so-called “access token”. In this sense, the access token is similar to the so-called “ticket” of Kerberos, which can be used, for example, for corporate networks running Windows and UNIX, among others.
The access token has a short lifespan, usually between a few minutes and an hour. On Exact Online, the access token is valid for 10 minutes. This is different from Kerberos; on Kerberos a ticket usually involves a session. However, for efficient use on the web, the session-concept is inconvenient; hence the for Exact Online fixed lifetime of up to 10 minutes.
Currently (October 2021) a rollout is taking place per Exact Online country where a new short-valid password cannot be obtained more often than once every 9.5 minutes. At a higher refresh frequency it is easy to obtain an invalid refresh token (see Itgeneor362 Voor toegang tot de OAuth-gegevensbron is een geldig access token vereist. Het access token kon niet worden verkregen (Dutch)). This scenario can be recognized by the one-time occurrence of the error message:
itgeneor364
Access to the OAuth data source requires a valid access token.
The access token could not be acquired. Exact Online requires at least 9,5 minutes between each request for a new access token.
Please try again in … seconds.
Please log on to Invantive Cloud and renew the authorization on the data container with alias ‘eol’ of the database ‘…’.
All in all, potential failures lurk on an ongoing basis such as “Old refresh token used” and “Token is not allowed, because of invalid or empty chainId”.
Refresh Token Troubleshooting
Currently (October 2021) approximately 1 out of 1.000 authorizations leads to a failure. Removing the causes is often beyond the control of Invantive, but we try to limit the frequency and impact with ever smarter algorithms, without significantly affecting the performance.
In the Invantive Bridge Online Monitoring and in Power BI an error message can occur about the refresh tokens. The following error codes indicate that manual intervention is necessary because the chain of valid refresh tokens is broken:
itgeneor361
Exact Online requires at least 9,5 minutes between each request for a new acces token.
itgeneor362
Only one refresh token can be active on Exact Online for a combination of client ID and user. The lifetime of a refresh token on use is 10 minutes. The request for data was made using an expired refresh token or refresh token that was not activated by a subsequent API call.
Please renew authorisation in the user interface.
itgenoda494
An obsoleted refresh token can not be used on Exact Online.
Please generate a new refresh token and make sure to keep the chain intact.
Such an error is easily repaired manually via the Invantive Cloud website. The steps to fix an error in the refresh tokens on Exact Online are described at Easier renewal of authorizations on Invantive Cloud itgenscr652, itgeneor559 or itgeneor562), but the basis is to choose the number that represents the number of databases in need of repair:
Preventing failures on Exact Online refresh tokens
Restoring authorization on Exact Online as described requires a manual step. In a limited number of applications this can be an unacceptable continuity risk, for example because there is a failure in the chain that repeatedly causes authorization problems.
For such emergency scenarios Invantive Cloud can restore the environment with the so called “Auto Recovery” based on the credentials of a user. These user credentials will be used to go through the authorization completely if needed and create a completely new refresh token (see RFC 6749, section 4.1).
Invantive Cloud records the username, password and the so-called TOTP-secret behind Invantive Data Guard in an encrypted environment. The encryption is based on an organization-specific key, but even then storing sensitive data always brings risks and is a trade-off between security and robustness.
When using auto-recovery, we recommend the following measures to reduce security risks:
- Use (as with regular reporting) a separate Exact Online user with only the necessary permissions.
- Use a strong password for this user.
- Take note of some advice around Exact Online at Hardening Exact Online security for applications.
To further reduce the risks, entered data for auto-recovery is deleted after 30 days. It is necessary to re-enter them every 30 days if auto-recovery is to be maintained. In a future release, this interval will be made variable between meaningful lower and upper limits.
The auto-recovery can be set by selecting the button on the data container, similar to renew authorizations:
After selecting the button the login details of the designated Exact Online user can be entered:
The TOTP secret is used to automatically determine the verification code. Explanations on how to retrieve the TOTP-secret can be found at Circumvent Exact Online 2FA to avoid entering PIN-code every ten minutes.