How To Configure Google BigQuery (Microsoft Entra ID) Power BI Connector Using Terraform

The Google BigQuery (Microsoft Entra ID) connector allows you to query BigQuery data with Power BI using your Microsoft credential. The connector relies on Google Workforce Identity Federation to federate your Microsoft credential into a Google credential.

This blog shows how to setup the connector using Terraform. Hereby, it complements the console-based setup guide.

Find the full example on GitHub

Query BigQuery with Power BI

Your user signs in to Power BI and opens a report with a BigQuery-based model on it. Power BI automatically exchanges your users Microsoft credential for a Google Cloud credential. Finally, Power BI queries the model and your user sees the data.

Power BI knows how to do this, because the Semantic model is associated with the Google Workforce Identity Federation pool provider, the Googla quota project and the BigQuery dataset.

Create the supporting infrastructure

First, the Workforce Identity Pool is associated with your Microsoft Entra ID tenant.

resource "google_iam_workforce_pool" "example_power_bi" {
  parent            = var.organization_id
  location          = "global"
  workforce_pool_id = "example-pbi"
}

resource "google_iam_workforce_pool_provider" "example_power_bi" {
  workforce_pool_id = google_iam_workforce_pool.example_power_bi.workforce_pool_id
  location          = "global"
  provider_id       = "aad"

  attribute_mapping = {
    "google.subject"      = "assertion.sub"    # Use 'sub' to bind to id composed of application and directory
    "google.groups"       = "assertion.groups" # Store 'groups' for groups IAM expressions
  }

  oidc {
    issuer_uri = "https://sts.windows.net/${var.tenant_id}/"
    client_id  = "https://analysis.windows.net/powerbi/connector/GoogleBigQuery"
    web_sso_config {
      response_type             = "ID_TOKEN"
      assertion_claims_behavior = "ONLY_ID_TOKEN_CLAIMS"
    }
  }

  extra_attributes_oauth2_client {
    issuer_uri = "https://login.microsoftonline.com/${var.tenant_id}/v2.0/"
    client_id  = azuread_application.power_bi_connector_bigquery_aad.client_id
    client_secret {
      value {
        plain_text = azuread_application_password.power_bi_connector_bigquery_aad.value
      }
    }
    attributes_type = "AZURE_AD_GROUPS_MAIL"
  }
}

Second, an Entra ID Application is registrered to allow Google Cloud to read (mail-enabled security-) Group memberships.

resource "azuread_application" "power_bi_connector_bigquery_aad" {
  display_name     = "PowerBI BigQuery SSO connector"
  owners           = [data.azuread_client_config.current.object_id]

  lifecycle {
    ignore_changes = [
      password,                 # managed using azuread_application_password.power_bi_connector_bigquery_aad
      required_resource_access, # managed using azuread_application_api_access.power_bi_connector_bigquery_aad
    ]
  }
}

resource "azuread_service_principal" "power_bi_connector_bigquery_aad" {
  client_id                    = azuread_application.power_bi_connector_bigquery_aad.client_id
  app_role_assignment_required = false
  owners                       = [data.azuread_client_config.current.object_id]
}

resource "azuread_app_role_assignment" "power_bi_connector_bigquery_aad" {
  for_each = toset(["GroupMember.Read.All","User.ReadBasic.All"])

  principal_object_id = azuread_service_principal.power_bi_connector_bigquery_aad.object_id
  resource_object_id  = data.azuread_service_principal.msgraph.object_id
  app_role_id         = data.azuread_service_principal.msgraph.app_role_ids[each.key]
}

Finally, the federated credentials are granted permissions to run queries.

resource "google_project_iam_member" "example_power_bi_serviceusage_serviceusageconsumer" {
  project = var.project_id
  role    = "roles/serviceusage.serviceUsageConsumer"
  member  = "principalSet://iam.googleapis.com/${google_iam_workforce_pool.example_power_bi.name}/*"
}

resource "google_project_iam_member" "example_power_bi_bigquery_jobuser" {
  project = var.project_id
  role    = "roles/bigquery.jobUser"
  member  = "principalSet://iam.googleapis.com/${google_iam_workforce_pool.example_power_bi.name}/*"
}

Ensure the required APIs are enabled to run BigQuery queries.

resource "google_project_service" "power_bi_connector_bigquery_aad" {
  for_each = toset(["bigquery.googleapis.com","cloudresourcemanager.googleapis.com","bigquerystorage.googleapis.com"])

  project = var.project_id
  service = each.key
}

Configure the Semantic model

Conclusion

Image by Peter H from Pixabay