BIgQuery Account Permissions

Hi!

UPDATED 2020-10-03

All we needed to do was actually to assign the predefined GCP IAM role "BigQuery Read Session User" - https://cloud.google.com/bigquery/docs/access-control#bigquery - to every PowerBI user that needs access to BigQuery and refresh reports. You can do that in several ways:

1. Directly assign this role to every relevant user. (Most manual work)

2. Add the matching permissions to an existing custom IAM role already assigned to the user. (Less manual work)

3. Create a GCP service account and granting access to it matching the predefined GCP IAM role "BigQuery Read Session User". Then using the gcloud cli you can add "domain-wide" policies (or anything else suitable covering your relevant user scopes) for impersonation of the service account. I'm not going to describe details about impersonation, you need to check the GCP docs. I'll only mention this part of the docs that were harder to find:

- https://cloud.google.com/iam/docs/reference/rest/v1/Policy
(This method should require the least manual work in the long run)

UPDATE: I haven't successfully implemented this third option myself, I just thought it would work this way. We use the second option ourselves.

Good luck!

Thank you Jesus!

ADD-ON 2021-01-12 (Swedish time zone)
On 2020-09-28 (or the day after) I prayed to God in the morning for technical solutions that would point to Him. On 2020-09-29 I got an email from my boss about our PowerBI environment not being able to communicate with BigQuery. Could solve it the same day within a few hours after quickly praying to God for a solution. Could get that solution into production the same evening to our top PowerBI users. The rest of the users got it rolled out the next day. Shared a clumsy solution 2020-09-30 here in this forum. Optmized the solution and updated the post. Everything worked for our end users from start. Our connectivity issues started on 2020-09-19 so I guess Microsoft and Google had worked on solving the problem from then, as far as I know. We had a support case open for several days and we got no help to sort our issue from there, until after I posted this. No one else was presenting any way to get it working anywhere either. There was also a PowerBI consultant working with the issue for several days at our company, without any luck. After I posted this solution, the PowerBI support sent us an email about how to solve our case, where they referred to this answer... 🙂
Again, thank you Jesus!
(I'm aware that the solution was of a temporary nature - the problem was probably fixed by Google with a long-term solution not long after I posted here.)

ADD-ON 2021-04-06

If the PowerBI user Bob has BigQuery access to multiple GCP projects, then you need to assign the predefined GCP IAM role "BigQuery Read Session User" to Bob in EVERY such GCP project. Or PowerBI will fail to load larger result sets from BigQuery at all. Just experienced and solved for us recently. Hope it helps someone!