topic BigQuery Connector Error: Restricted datasets causes connection failure (REST API: access denied) in Power Query

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

Anonymous — Thu, 16 Aug 2018 09:44:08 GMT

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

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

Anonymous — Fri, 17 Aug 2018 08:39:12 GMT

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

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

Anonymous — Fri, 17 Aug 2018 09:28:41 GMT

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.

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

Anonymous — Mon, 20 Aug 2018 09:14:04 GMT

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

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

Anonymous — Wed, 22 Aug 2018 02:10:28 GMT

Anonymous

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

Thanks,

Tim

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

Anonymous — Thu, 23 Aug 2018 08:17:14 GMT

Anonymous,

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

Regards,
Lydia