Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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 [email protected] 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).

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Anonymous
        Not applicable
        8 years ago

        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