cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

BIgQuery Account Permissions

Good morning. I have been having some trouble updating my PowerBI service lately. I am receiving a message that states:

 

  • Processing error: ERROR [HY000] [Microsoft][BigQuery] (131) Unable to authenticate with Google BigQuery Storage API. Check your account permissions.

 

When I update from PowerBI desktop it works just fine, then I have to save and publish manually. I'd like to get back to the service updating automatically in the middle of the night so numbers are valid at the start of our day. Unfortunately, no matter how many times I reconnect to BigQuery via the Data Source Credentials it still gives me the error. 

 

I have reached out to MSFT support but they claim it's a google bigquery issue. I've reached out to Google but they claim if it works in desktop and not in service it's an MSFT issue. 

 

Help??

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Regular Visitor

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)

 

Good luck!

 

Thank you Jesus!

View solution in original post

8 REPLIES 8
Highlighted
Helper I
Helper I

I have this problems too!

and i have error: The key didn't match any rows in the table.

Highlighted
Helper I
Helper I

please, try it

GoogleBigQuery.Database([BillingProject="project name bq"])

Highlighted
Regular Visitor

Identical issue started for us today.

 

Unable to authenticate with Google BigQuery Storage API. Check your account permissions.. The exception was raised by the IDbCommand interface.
 
Highlighted
Advocate I
Advocate I

Having same issue. Two users out of dozens stopped being able to connect to BigQuery. One of those users is also having online reports fail to update. 

 

Tried upgrading to latest version of Power BI, and clearing permissions and reauthorising, neither of which worked.

 

Reverting to the July version of Power BI has fixed the problem in desktop, but still facing the issue that of online reports not being able to update. 

Highlighted
Frequent Visitor

I have similar issue with one particular published server dataset that will not on-demand or schedule refresh that started ~Sept 22.  I have July desktop with no problems to refresh locally on desktop.

 

One thing I've noticed is if I create a new report with the exact same connections to the problematic dataset (w/ July Desktop as before), the new published server dataset refreshes fine.  However if I save-as the problematic report and publish, the error persists with this cloned published server dataset. 

 

I have other published reports that connect to BigQuery with no issues at all that are supposedly using the same credentials.

Highlighted
Regular Visitor

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)

 

Good luck!

 

Thank you Jesus!

View solution in original post

Highlighted

Thanks draim!  Yes, assigning the predefined GCP IAM role "BigQuery Read Session User" worked for our case.  Much appreciated!

Highlighted

Hi AgTooOldForThis!

 

I'm glad it helped you!

 

David

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors