Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

BigQuery Connector Error: Restricted datasets causes connection failure (REST API: access denied)

When trying to connect to our BigQuery project, I am running into this problem: 

DataSource.Error: ERROR [HY000] [Microsoft][BigQuery] (100) Error interacting with REST API: Access Denied: Dataset projectname:first_dataset_name: The user name@domain.com does not have bigquery.tables.list permission for projectname:first_dataset_name."

 

Basically our BigQuery project has permission granted on the dataset level (by using BigQuery's "Share Dataset"). We get this with any user who has restricted access.

 

Person A has access to BQ dataset 1, 2, 5 which was done by using "Share Dataset" (we have BQ dataset 1, 2, 3, 4, 5)

 

When Person A connects using BigQuery Connector through PowerBI, they get the HY000 error (they do not have permission to list 3).

 

Ideally what should happen is to skip over any datasets that a person does not have permission to list instead of failing on the first permission error.

- Person A can access 1, 2, 5 (PowerBI BigQuery connector skips dataset 3 and 4 because Person A doesn't have the permission for those).

5 REPLIES 5
v-yuezhe-msft
Employee
Employee

@Anonymous,

If these datasets are in a single project, you would need to grant permission on the list which is mentioned in error message.

However, if you know the specific table name that you want to connect to, please add a blank query and paste the following code into Advanced Editor of the blank query, then check if you get any errors.

let
    Source = GoogleBigQuery.Database(null),
    #"project-data" = Source{[Name="yourprojectname"]}[Data],
    table_Schema = #"project-data"{[Name="yourtable",Kind="Schema"]}[Data]
in
table_Schema


There is an example for your reference.
1.PNG2.PNG

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-yuezhe-msft

If these datasets are in a single project, you would need to grant permission on the list which is mentioned in error message.

 

We do not grant project-wide permissions to certain users; we give it on a per-dataset. We are using Google BigQuery's "Share Dataset" to help us do access control of our data. https://cloud.google.com/bigquery/docs/datasets (under "Controlling access to dataset")

 

Is it not possible to use Google BigQuery's per-dataset permission model with Power BI? Can Power BI's BigQuery connector not just ignore the datasets which you don't have permission to access?

 

 

Also I've tried your other suggestion. It seems by Table, you mean BigQuery Dataset.

@Anonymous,

I am afraid that Power BI's BigQuery connector can't ignore the datasets which you don't have permission to access.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

@v-yuezhe-msft

 

I see, is it possible to request this feature somewhere?

 

Thanks,

Tim

@Anonymous,

You can submit a feature request in Power BI ideas forum:https://ideas.powerbi.com/forums/265200.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors