KB485896: How to Resolve the "Authentication failed: unauthorized_client. error_description: Unauthorized" Error

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.
Strategy 2021 Update 8 (February 2023) and later includes a new Google BigQuery ODBC driver version 2.6.0.1002. The new driver introduces system breaking changes to the ODBC DSN data source configurations with Refresh Tokens generated from vendor's Client ID and Client Secret. JDBC/ODBC DSN-less data sources will work without any changes. Therefore, no changes are required for those data sources.
 

Driver Behavior Changes

1. The new Google BigQuery ODBC driver expects all sensitive information to be encrypted in version 2.6.0.1002. If ODBC DSNs use refresh tokens generated from previous driver's Client ID and Client Secret, the DSNs break. See the following vendor release note:
   
• [GAUSS-1565] Upgraded Encrypt Sensitive Data Property
  
• The configuration dialog on Windows can now be configured to encrupt values to the registry such that they can be decrypted by the users. To do this, from the Encrypt Sensitive Data drop down list, select For All Users or For Current User Only. For more information, see the Installation and Configuration Guide.
2. The new Google BigQuery ODBC drive now includes support for multi-statement transactions via the EnableSession=1 setting. If you experience failures related to multi-statement transactional reports or dossiers, update every ODBC DSN using KB485892: How to Resolves the "Transaction Control Statements are Only Supported in Scripts or Sessions" error.

This article focuses on solving the first driver behavior change.
 

Errors

Linux

Error type: Odbc error. Odbc operation attempted: SQLDriverConnect. [28000:8711: on SQLHANDLE] [Strategy][BigQuery] (8711) Authentication failed: unauthorized_client. error_description: Unauthorized.

Windows

Error type: Odbc error. Odbc operation attempted: SQLDriverConnect. [HY000:-1: on SQLHANDLE] [Strategy][DSI] An error occurred while attempting to retrieve the error message for key 'DSDecryptError' with message parameters ['The parameter is incorrect.'] and component ID 100: Message not found in file "...\en-US\SimbaBigQueryODBCMessages.xml".

Solution

Linux

You must update every Google BigQuery ODBC DSN configuration block in the odbc.ini file (default location: /opt/mstr/Strategy) with the following:

• If the RefreshToken is generated using your own Client ID and Client Secret, no further changes are needed. 

Note: This is the Strategy's and driver vendor's recommended option. Using driver vendor's Client ID and Client Secret is a deprecated option and should not be used.

• If the RefreshToken is generated using the driver vendor's Client ID and Client Secret, or if you do not know how the RefreshToken was generated and there is no ClientID and ClientSecret parameters in your DSN configuration block, the following parameters must be added to every Google BigQuery ODBC DSN configuration block:
  
• ClientId=977385342095.apps.googleusercontent.com
• ClientSecret=wbER7576mc_1YOII0dGk7jEE

Windows

The driver vendor suggests that you recreate the DSN that was created prior to the Strategy 2021 Update 8 (February 2023). To recreate Google BigQuery ODBC DSN on Windows, see Create a Google BigQuery ODBC DSN for Workstation and Web on Windows.
 

Strategy's Recommendation

Strategy recommends the OAuth based connection method when connecting to BigQuery (not token based). This connection will not cause these errors. For more information, see Create an ODBC DSN-less Connection .