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

[Anonymous]

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).

[Anonymous]

@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.


Regards,
Lydia

[Anonymous]

@Anonymous

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]

@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

[Anonymous]

@Anonymous

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

Thanks,

Tim

[Anonymous]

@Anonymous,

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

Regards,
Lydia