Google Service Account to connect to Big Query, is there a way to force Matillion to attempt a fresh oauth connection in each request?

Matillion ETL
1

I've set up a service account (with a key, the p12 file uploaded to Matillion box) and an app with client/secret as per https://documentation.matillion.com/v150/docs/2963740

 

I’m having issues with the authentication, getting errors such as:

Request had invalid authentication credentials.

Request had insufficient authentication scopes

 

The connection seems flaky, I was struggling yesterday evening with it trying to make it work and couldn’t connect.

Then today in the morning when I first logged in to matillion I tested and it automagically worked. I can even see some authentication traffic in the GCP console. I modified the IAM policies in GCP, I deleted the BigQuery Admin role (as admin is a bit too high for a service account) and I couldn’t access anymore from matillion. I immediately added again the role, and tried again but haven’t been able to connect anymore.

 

I am wondering if there is something like an oauth cache in matillion, or a way to force to authenticate. I would have imagined that in every request it does go and authenticate. But the behavior is making me think otherwise.

 

Any thoughts?

2

Solved! Indeed it was a problem with cached tokens.

After testing out some of the connection parameters in the BigQuery Data Model

 

https://matillion-docs.s3.eu-west-1.amazonaws.com/datamodels/Google+BigQuery.html

 

I found the right combination of parameters to add in the connection parameters:

Readonly True

AutoCache False

ConnectOnOpen False

DatasetId {dataset name}

OAuthSettingsLocation

OAuthJWTSubject {service account}

 

 

It seems that by setting the OAuthSettingsLocation to blank it forces it, according to documentation: "To automatically refresh the OAuth token values, set OAuthSettingsLocation and additionally set InitiateOAuth to REFRESH."