Connecting to BigQuery as End User with Client ID / Client Secret

[marca_at_cue]

We are just starting with Alteryx at our organization.  Our primary data source is Google Big Query and we are starting with 10 Alteryx users.  We'd like to use the End User option to connect to BigQuery, but are having trouble understanding how to put things together.  We are opting for this path because we believe it is more secure than issuing Service Accounts to end users.

We can create GCP credentials with gcloud auth application-default login in the Google Cloud SDK.  That outputs a json file with contents similar to this (NOTE: I changed characters to anonymize and protect our environment, but structure is valid).

{
"client_id": "8675309-BigBlobOfCharacters.apps.googleusercontent.com",
"client_secret": "d-FL95QRANDOM7hHD0Ty",
"quota_project_id": "our-gcp-project",
"refresh_token": "1//06o4wwr-RANDOM-L9IrLRlR-VabrLy92H9fEyO64fCtB8X1SjsMpma6qwjHkMMuqbFTRaVRdAKPBXCeOUWWt5E",
"type": "authorized_user"
}

The client_id and client_secret do NOT work for the BigQuery Input node End User authentication mode.  I also tried to use the JSON file for the Service-to-Service method, it doesn't work either.  

Any idea how to let an end user generate GCP BigQuery Client ID/Secret that can be used with the Alteryx BigQuery Input tool?

Many thanks for your help,

Marc

[apathetichell]

I do not like the standalone BQ tool. My suggestion is authenticate at the ODBC 64 level with the BQ driver. Then set up a generic ODBC driver to connect to BQ.

Using the BQ driver and config in ODBC 64 you can authenticate via SSO which I assume you normally use to authenticate?

[marca_at_cue]

First - many thanks for the Response.  I made more progress in the last 30 minutes than I've made in the last 72 hours, thanks to you!

This is a really interesting option.  I connected successfully using the Input Data > Data Sources > Google BigQuery > ODBC (where I had been trying the Quick Connect, unsuccessfully as End User).  Does the ODBC credential ever expire and need the credentials updated (within a 24 hour period)?  

Are you using this methodology where a group of people rely on it in an enterprise type deployment?  Is it rock solid for you, or there are some pain points you've experienced.

apthetichell: "you can authenticate via SSO which I assume you normally use to authenticate?".  This is the first application we've explored that isn't native to GCP.  So, these are uncharted waters and trying to find a reliable best practice that doesn't have security concerns.  Do you deploy these connections to Alteryx Server?  Do you use any of the features to used centralized connection or service account once the workflows are on server?

[marca_at_cue]

BTW - this is an attractive option and potential solution.  I'm still exploring and hoping to hear some more feedback before I determine that this is really the Solution to this request.  

[apathetichell]

Hey @marca_at_cue - Yes - your SAML token in ODBC 64 will expire - Not 100% sure how often it will - but it will. As a note - I don't use BQ as often as I use a bunch of other DB's and hadn't used it since mid-June. It had expired this morning when I just checked... Page 14 in this guide talks about authentication as a user with a key file:

https://storage.googleapis.com/simba-bq-release/odbc/Simba%20Google%20BigQuery%20ODBC%20Connector%20...

This would be much more stable and you could control the refresh cycle.

For Server - neither of these is ideal. Server really relies upon the Server authenticating with the DB (via service account/driver) and control of the connectivity to that source in the Server (in DCM or run as user or equivalent collection control). You are looking to have the user control authenticating to the DB in Server - I know this is something Alteryx is working on - but it's not quite how Server is currently set up.  I can see some (non-ideal) workarounds but they are fairly complicated and there are some assumptions that I would make (ie your server is a Compute Instance on GCP already)...

Does that help? when you talk about an enterprise basis are we talking about 25 designers or 300 designers?  If it's 25 I might keep them on SAML via the ODBC and teach them to reset. I find InfoSec is happier with that I refer to as "native security" - ie authentication that ties into your existing authentication.

Are you a current Server customer? Feel free to private message me on this.