Passthrough Authentication for Google BigQuery?

[Kraggle]

Hi,

first of all I would like to mention that I'm new to Power BI, as User, Devloper and Admin and in the beginning phase of a project. My work experience is based on MicroStrategy, please excuse if I'm using wrong terms. 

We have a distributed data warehouse based on BigQuery located in different GCP Projects. We use GCP features to manage the access on those projects and it's data with Authroized Views / IAM Roles etc.

Now we would like to use Power BI for our end users to visualize the data on their own. But we also want to create some reports provided by us, the BI department.

Our wish is, that we as BI developer create reports and dashboards. And when a user executes the report, the user should authenticate to Google, that the report only shows the data he is supposed to see with his Google Account. 

Our current situation unfortunatley is as follows:

We can import data to Power BI, but then the User access the imported data without additional authentication to goole.

If User B wants to execute the Report with his accoutn, User B needs to take ownership of the Dataset and update the credentials.

If we use DirectQuery, we need a gateway with a service account. The permissions of the users google account won't affect the report obviously. Additionally we have to map our google permissions to something based on Power BI with Roles and RLS (and do key rotation for the Service Account every now and then).

Basically we are looking for an option which is available for Azure SQL:

 https://docs.microsoft.com/bs-latn-ba/power-bi/connect-data/service-azure-sql-database-with-direct-c... 

Are we missing an option? Does anybody has experience with that kind of requirement togehter with BigQuery? 

Anybody experience with workarounds (e.g.using BigQuery ODBC driver) or https://docs.microsoft.com/en-us/power-query/startingtodevelopcustomconnectors ? 

I saw similar posts like this one: https://community.powerbi.com/t5/Service/PowerBI-Pass-through-Authentication-for-Data-source/m-p/903... but it's not exactly solving our requirement.

Thanks in advance!

[hi_world]

Hello, 

with the new version of March, have you managed to unblock the situation?
If yes how did you do it?

Best regards

[ggbae]

Hi,

Were you able to find a solution to this? I have similar issue where I am using view to enforce row-level access based on the current user in bigquery and for it to work, I need a feature where end users will use their credentials in power bi service.

Also, where are you implementing the RLS? In bigquery or power bi report?

[Kraggle]

Hi,

unfortunatley not, we use the workaround with the Service Account through the Gateway for now. (see my response from ‎05-11-2022)

Please note that in the meantime Microsoft released a bug during the process of adding Service Account Credentials to a BigQuery Datasource: 

https://community.powerbi.com/t5/Issues/Accountkey-limited-to-200-characters-in-the-new-interface-of...

We are still in the beginning of our Power BI setup, we just got Admin rights to our own capacity but requested external help as I only have experience as MicroStrategy Admin.

Right now we can split our users in just two groups and separate the access with two Workspaces.

We also would like to use our Authorized Views as intended but can't, as we have to work with the SA-workaround. I currently see no chance using BigQuerys RLS capabilities 😕 

With an increasing amount of reports we will have to implement RLS in Power BI.

If you or anybody else can help, please ping me too 😉

[v-yanjiang-msft]

Hi @Kraggle ,

There're some tips in this document for your reference:

---

When authenticating through a Google service account in Power BI Desktop, there's a specific credential format that's required by the connector.

• Service Account Email: must be in email format
• Service Account JSON key file contents: once this JSON key is downloaded, all new lines must be removed from the file so that the contents are in one line. Once the JSON file is in that format, the contents can be pasted into this field.

When authenticating through a Google service account in Power BI service, users need to use "Basic" authentication. The Username field maps to the Service Account Email field above, and the Password field maps to the Service Account JSON key file contents field above. The format requirements for each credential remain the same in both Power BI Desktop and Power BI service.

---

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kraggle]

Hi @v-yanjiang-msft ,

thank you for your reply. We are using the Service Account with a key file for now, but this workaround doens't scale in our case.

This is fine for our first report which consumes data from one Google Project, but as the data is distributed accross serval Projects operated by different teams, we now have to create service accounts for each project and this results in many manual (afaik, as I don't see options for Workload Identity Federation) Key Rotations in the gateway in future.

We also have to implement additional secruity measures in power BI as our Policies in Google are bypassed. 

I will update this thread when we found a suitable solution for our architecture