Circumvent two-step verification and refresh tokens on Exact Online using Data Hub

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 of Data Hub storing the last activated refresh token in the Invantive Keychain.

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.xml in %USERPROFILE%\invantive.
  • Add the XML shown below.
  • Replace the user, password and TOTP secret key.
  • Restart your Invantive application.
  • Log on.

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:

Run query on Exact Online

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.

Version 20.1.502

The above steps have been performed. In the Query Tool it works, but in the Data Hub I get the following message itgenexl053:

2021-07-28 23:44:04.556 Error itgencun016: Fout itgenexl053: Een verbinding met de database '--------' kon niet worden opgebouwd als gebruiker ''.

The application 'InvantiveDataHubWindows' can only be used with your own client ID in combination with a production license key on top-level domain 'nl'.
Please configure api-client-id in your connection string to include your own client ID. Also include api-redirect-url with the desired redirect URL. Or switch to a trial, freemium, unpaid or Public Data license key.
2021-07-28 23:44:06.568 Error itgenexl053: ValidationException
ValidationException
   at Invantive.Basics.ValidationException..ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String messageText, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId)
   at Invantive.Data.Providers.ExactOnline.EolLicenseConstraints.GetClientId(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String apiUrl, Application application, Boolean correctApplication, String providerAlias)
   at Invantive.Data.Providers.ExactOnline.ExactOnlineRestProvider.GetOAuthAuthenticationEndpoint(GlobalState owner, ExecutionOptions executionOptions, String apiUrl, ConnectionStringAttributes connectionAttributes)
   at Invantive.Data.Providers.OData.ODataWithOAuthProvider.GetOAuthToken(GlobalState owner, ExecutionOptions executionOptions, SerializableDatabase database, SerializableDataContainer dataContainer, Credentials credentials, ConnectionStringAttributes connectionAttributes)
   at Invantive.Data.Providers.OData.ODataWithOAuthProvider.Invantive.Data.ISupportsOAuth.GetOAuthToken(GlobalState owner, ExecutionOptions executionOptions, SerializableDatabase database, DataContainer dataContainer, Credentials credentials, Boolean ignoreDecryptionErrors, Boolean& decryptionErrorsOccurred)
   at Invantive.Data.Providers.ExactOnline.ExactOnlineProvider.Invantive.Data.ISupportsOAuth.GetOAuthToken(GlobalState owner, ExecutionOptions executionOptions, SerializableDatabase database, DataContainer dataContainer, Credentials credentials, Boolean ignoreDecryptionErrors, Boolean& decryptionErrorsOccurred)
   at Invantive.Data.ConnectionManager.CF(GlobalState , ExecutionOptions , IConnectionDataProvider , SerializableDatabase , DataContainer , Credentials , Boolean , Boolean& )
   at Invantive.Data.ConnectionManager.OpenDataContainerProvider(GlobalState owner, ExecutionOptions executionOptions, SerializableDatabase database, DataContainer dataContainer, Credentials credentials, List`1 triedProviders, Boolean ignoreDecryptionErrors, Boolean& decryptionErrorsOccurred)
   at Invantive.Data.ConnectionManager.UF(GlobalState , ExecutionOptions , CredentialsContainer , Boolean , Boolean& )
   at Invantive.Data.ConnectionManager.Open(GlobalState owner, ExecutionOptions executionOptions, CredentialsContainer credentialsContainer, Boolean ignoreDecryptionErrors, Boolean& decryptionErrorsOccurred)
   at Invantive.Producer.UtilityBaseCore.OpenDatabase(GlobalState owner, ExecutionOptions executionOptions, String databaseFullName, CredentialsContainer credentialsContainer, Boolean ignoreDecryptionErrors, Boolean& decryptionErrorsOccurred)
   at Invantive.Producer.UtilityBaseCore.CheckConnection(GlobalState owner, ExecutionOptions executionOptions, Boolean forceReconnect, Boolean silent, String databaseName, CredentialsContainer credentialsContainer)
   at IDH.R.FC(String[] )
   at Invantive.Producer.QueryEngine.Program.Main(String[] arguments)
   at Invantive.Data.Providers.ExactOnline.EolLicenseConstraints.GetClientId(GlobalState owner, ExecutionOptions executionOptions, IProviderManager manager, String apiUrl, Application application, Boolean correctApplication, String providerAlias)
   at Invantive.Data.Providers.ExactOnline.ExactOnlineRestProvider.GetOAuthAuthenticationEndpoint(GlobalState owner, ExecutionOptions executionOptions, String apiUrl, ConnectionStringAttributes connectionAttributes)
   at Invantive.Data.Providers.OData.ODataWithOAuthProvider.GetOAuthToken(GlobalState owner, ExecutionOptions executionOptions, SerializableDatabase database, SerializableDataContainer dataContainer, Credentials credentials, ConnectionStringAttributes connectionAttributes)
   at Invantive.Data.Providers.OData.ODataWithOAuthProvider.Invantive.Data.ISupportsOAuth.GetOAuthToken(GlobalState owner, ExecutionOptions executionOptions, SerializableDatabase database, DataContainer dataContainer, Credentials credentials, Boolean ignoreDecryptionErrors, Boolean& decryptionErrorsOccurred)
   at Invantive.Data.Providers.ExactOnline.ExactOnlineProvider.Invantive.Data.ISupportsOAuth.GetOAuthToken(GlobalState owner, ExecutionOptions executionOptions, SerializableDatabase database, DataContainer dataContainer, Credentials credentials, Boolean ignoreDecryptionErrors, Boolean& decryptionErrorsOccurred)
   at Invantive.Data.ConnectionManager.CF(GlobalState , ExecutionOptions , IConnectionDataProvider , SerializableDatabase , DataContainer , Credentials , Boolean , Boolean& )
   at Invantive.Data.ConnectionManager.OpenDataContainerProvider(GlobalState owner, ExecutionOptions executionOptions, SerializableDatabase database, DataContainer dataContainer, Credentials credentials, List`1 triedProviders, Boolean ignoreDecryptionErrors, Boolean& decryptionErrorsOccurred)
   at Invantive.Data.ConnectionManager.UF(GlobalState , ExecutionOptions , CredentialsContainer , Boolean , Boolean& )
   at Invantive.Data.ConnectionManager.Open(GlobalState owner, ExecutionOptions executionOptions, CredentialsContainer credentialsContainer, Boolean ignoreDecryptionErrors, Boolean& decryptionErrorsOccurred)
   at Invantive.Producer.UtilityBaseCore.OpenDatabase(GlobalState owner, ExecutionOptions executionOptions, String databaseFullName, CredentialsContainer credentialsContainer, Boolean ignoreDecryptionErrors, Boolean& decryptionErrorsOccurred)
2021-07-28 23:44:06.613 Error itgencun016: Fout itgendhb005: Could not connect to database '--------'. Application will be closed.
2021-07-28 23:44:06.613 Error itgendhb005: ValidationException
   at Invantive.Basics.ValidationException..ctor(GlobalState owner, ExecutionOptions executionOptions, String messageCode, String messageText, String kindRequest, String localStackTrace, String nk, Exception innerException, Boolean inheritMessageCodeWhenPresent, Nullable`1 uid, Boolean isRecoverable, String poolIdentityId)
   at IDH.R.FC(String[] )
   at Invantive.Producer.QueryEngine.Program.Main(String[] arguments)
   at IDH.R.FC(String[] )
2021-07-28 23:44:06.633 Error itgenube182: itgendhb005: Could not connect to database '--------'. Application will be closed.

Background itgenexl053

The message code itgenexl053 has the following text:

The application ‘InvantiveDataHubWindows’ can only be used with your own client ID in combination with a production license key on top-level domain ‘nl’.
Please configure api-client-id in your connection string to include your own client ID. Also include api-redirect-url with the desired redirect URL. Or switch to a trial, freemium, unpaid or Public Data license key.

The background is that a number of (server-oriented) products require the use of a proprietary OAuth client ID under the following conditions:

  • No trial license.
  • No free license.
  • No Public Data license.
  • Outside Invantive organization.

This applies to the following products:

  • Invantive Data Hub for Windows
  • Invantive Data Hub for Linux and Mac
  • Invantive Data Access Point
  • Invantive Bridge for Developers
  • Invantive Runtime
  • unknown applications

The message is independent of the use of a refresh token or a TOTP secret key for the generation of the TOTP authentication code.

Solution

In the settings*.xml file containing the TOTP secret key, the connection string should also include a client ID, such as:

connectionString="totp-secret=C9E9WRUDJS1MZY42GJKVYFEZ04DSQXJI;api-url=https://start.exactonline.nl;api-client-id=123141abcdef23123123"

This has been corrected in the previously published example.

Is there a way to trace/find back the TOTP secret key, if you currently do not have it in your possession? Or do you need to reset the two-step verification within your Exact Online account and regenerate a new QR code to get the TOTP secret key?

And when you do need to reset the two step verification on the Exact Online account:
If I am correct that wouldn’t influence the current used refresh token in other settings*.xml files on the same Exact Online account, right?

No, by design the TOTP-specification RFC 6238 has been engineered such that it is very very hard to reverse engineer the secret key from one or a sequence of verification codes. The Invantive Authenticator has some functionality to determine whether a verification code can have been issued based upon a known TOTP secret key, but that is a brute force approach which will not establish the secret key.

On Exact Online you can reset the secret key easily. For more information on the process in Exact Online read Circumvent Exact Online 2FA to avoid entering PIN-code every ten minutes.

Changing the two-step verification secret key should no affect the validity of existing refresh tokens, nor should changing the password of the user do. It are separate sets of credentials.

1 Like