Google BigQuery connector: connections fails when not attempted on the same node where the data connection was created.

[Andrea_Bertazzo]

The problem occurs using the Google BigQuery connector (part of the ODBC Connector Package) with user authentication.
The connections on all nodes work when the data preview or reload is performed on the same node that the data connection was created on, but they fail when the same is attempted from another node.
The reloads run from the QMC may fail intermittently. Creating the data connection via the main site/load balancer has no effect.
The error is:

Failed to build token, please try again ---> Qlik.Connectors.SDK.Common.Encryption.WindowsAesGcmEncryptionException: Org.BouncyCastle.Crypto.InvalidCipherTextException: mac check in GCM failed

This problem occurs only on multi-node environments.

Environment

• Qlik Sense On Windows all supported versions

Resolution

The solution consists in two step. In the first step we will create a suitable encryption key for the connector and in the second step we will apply this key on all Qlik Sense nodes.

First step: Generate the key.

The code below provides a Windows-friendly procedure for users to generate CLI safe keys using PowerShell. This does not require installing third-party tools

Here you can find an example. we generate a Base 64 encoding of a random 24 byte key that provides a potential entropy of 192 bits.

# Generates a 32 character base 64 encoded string based on a random 24 byte encryption key
function Get-Base64EncodedEncryptionKey {
$bytes = new-object 'System.Byte[]' (24)
(new-object System.Security.Cryptography.RNGCryptoServiceProvider).GetBytes($bytes)
[System.Convert]::ToBase64String($bytes)
}

$key = Get-Base64EncodedEncryptionKey
Write-Output "Get-Base64EncodedEncryptionKey: ""${key}"", Length: $($key.Length)"

For example:

Other options can be chosen to generate the key. In order to work, the key must be 32 bytes array converted into UTF-8 string.

Second Step:  Apply the key

Do the following:

1. Open a command prompt and run:
   runas /user:{sense service user} cmd.
   The {sense service user} must be the name of the Windows account which is running your Qlik Sense Engine Service.
   
   
2. Run the following two command to switch to the directory containing the connectors and then set the key:
   cd C:\Program Files\Common Files\Qlik\Custom Data\QvOdbcConnectorPackage
   
   
3. Run
   QvOdbcConnectorPackage.exe /key {key}
   Where {key} is the key you generated in step A. For example, if you used the openssl command, your command might look like:
   QvOdbcConnectorPackage.exe /key 4D9DE03A22C412FA56F07160E1CC8DD2957BA84DECC56159FC4EF52247951E91
   
4. Remove this file: C:\Users\{sense service user}\AppData\Roaming\Qlik\Keys\qseow_file_cache_key
   
   
5. You should repeat from the steps above on each node in the multinode environment where the Qlik Engine and the connectors are installed. You must use the same key in each node.

Cause

The problem is sue to a feature into SDK recently added, which generates an encryption key if it doesn't exist and uses it for further Oauth related encryption.
This can lead to a situation where each node has its own encryption key. This allows to perform encryption within single node, but different nodes can't work together because of the different encryption keys.

Related Content

See this article.

Internal Investigation ID(s)

QB-16841