This note presents a number of easy steps to circumvent the use of an OAuth2 refresh token using the Code Grant Flow on the console apps Invantive Data Hub and Invantive Data Replicator. Instead of the refresh token, the user name, password plus a generated TOTP verification code are used to establish and maintain a connection going through the Implicit Grant Flow every approximately ten minutes.
Various other means are used for TOTP on Exact Online, like “2FA” (two factor authentication) and “MFA” (multi-factor authentication). Terms such as “two factor authentication” however also apply to other cloud platforms.
Old refresh token used
The instructions provided here can also be used to temporarily circumvent errors such as:
Error itgenoda185: A connection to the database ‘…’ could not be established as user ‘…’.
The data container with alias ‘eol’ could not be opened on provider ‘ExactOnlineAll’.
Your session has expired.
Old refresh token used.
The remote server returned an error: (401) Unauthorized.
The client is not authorized to request an authorization code using this method and/or authorization grant type.
and like:
Token is not allowed, because of invalid or empty chainId
For this type of problem please upgrade to a version 20.1.530 or newer of Data Hub storing the last activated refresh token in the Invantive Keychain.
More background on “Old refresh token used” can be found on the Dutch post:
Exact Online foutmelding: Old refresh token used.
Interactive apps
For interactive apps capable of displaying a browser window such as Invantive Control for Excel please refer to Circumvent Exact Online 2FA to avoid entering PIN-code every ten minutes. This is also a good introduction on the risks and steps to acquire the TOTP secret from a QR code or using copy & paste.
Background provider (headless OAuth)
Since no browser capability required for the Implicit Grant Flow is available, a special OAuth background provider is used. This OAuth background provider is one of the possibilities to connect to the Exact Online APIs besides or in addition to the Implicit Grant Flow and the Code Grant Flow supported. Always consult your local information security officer on the necessary requirements to fulfill.
The OAuth background provides takes the secret key used for TOTP and system clock time to generate the then current verification code. This is identical to the algorithm used by an authenticator app. The user name, password and verification code are used to navigate through the log on procedure using the Implicit Grant Flow. The Implicit Grant Flow is used for scenarios inside a browser or device on which the client secret required for Code Grant Flow cannot used since the browser or device runs on a device not trusted by the owner of the client secret.
In the first step, user name and password are provided in a headless browser. In the second step (therefor the term “two step”) the verification code is provided and the authentication is completed.
Changes to settings*.xml
It is quite easy to configure a database circumventing the two-step verification by following these steps:
- Create a new text file
settings-test.xmlin%USERPROFILE%\invantive. - Add the XML shown below.
- Replace the user value on
defaultUserLogonCode. - Replace the password value on
defaultPassword. - Replace TOTP secret key value on
totp-secret. - Restart your Invantive application.
- Log on.
More information on the settings.xml file format can be found in Settings.xml XML-format for virtual SQL databases.
Setup of Exact Online circumventing two-step verification and refresh tokens:
<?xml version="1.0" encoding="utf-8"?>
<settings version="5">
<group name="Test" sortingOrder="0">
<connection name="Test" authentication="Application" sortingOrder="0">
<database
provider="ExactOnlineAll"
userLogonCodeMode="Hidden"
passwordMode="Hidden"
defaultUserLogonCode="john.doe@acme.com"
defaultPassword="DXfwghrthftthisisverysecret34hefwthistoo@Gwfw"
connectionString="totp-secret=C9E9WRUDJS1SECRETNONSENSEV4DSQXJI;api-url=https://start.exactonline.nl;api-client-id=123141abcdef23123123"
/>
</connection>
</group>
</settings>
Please note that characters must be properly escaped for use in XML when the Exact Online password or user name contains characters reserved for use in XML in all or specific scenarios. In case of doubt change the password to contain none of:
- greater than (
>) - less than (
<) - ampersand (
&) - percent (
%) - single quote (
') - double quote (
")
Changing an Existing Settings*.xml
As an alternative, an existing database definition can be changed from Code Grant Flow into Implicit Grant Flow by replacing the data container definition from:
<database
order="10"
alias="eol"
provider="ExactOnlineAll"
userLogonCodeMode="Hidden"
passwordMode="Hidden"
connectionString="api-url=https://start.exactonline.nl;api-client-id=8ea6017a-fb8d-4cc2-9935-0026161b777e;api-redirect-url=https://exactonlineclientredirect.invantive.com;api-client-secret=myclientsecret;api-refresh-token=VKsb...a...lot...of...text...w0"
AllowConnectionStringRewrite="false"
/>
into:
<database
order="10"
alias="eol"
provider="ExactOnlineAll"
userLogonCodeMode="Hidden"
passwordMode="Hidden"
defaultUserLogonCode="mylogoncode"
defaultPassword="mysecretpassword"
connectionString="api-url=https://start.exactonline.nl;api-client-id=8ea6017a-fb8d-4cc2-9935-0026161b777e;api-redirect-url=https://exactonlineclientredirect.invantive.com;totp-secret=X6EMYSECRETKEYFORTOTPGG27"
AllowConnectionStringRewrite="false"
/>
Headless Log on Process
The log on process resembles:
The regular SQL functionality is available on Exact Online, such as:
Typically, such a database will be used when a refresh token is not possible since it for instance expired and a new refresh token is not yet available due to absence of a user.
EDIT A client ID setting was added to the sample above.