This guide shows you how to let users that are in Microsoft Entra groups accessBigQuery data in Power BI by usingWorkforce Identity Federation.

Microsoft Entra is the identity provider (IdP). Groups claims from MicrosoftEntra are mapped to Google Cloud. Groups are granted Identity and Access Management (IAM)permission to access the BigQuery data.

This guide provides instructions for Power BI Desktop or Web.

Before you begin

1. Make sure that you have a Google Cloud organization set up.

2. Install the Google Cloud CLI. After installation,initialize the Google Cloud CLI by running the following command:

   gcloudinit

   If you're using an external identity provider (IdP), you must first sign in to the gcloud CLI with your federated identity.

   Note: If you installed the gcloud CLI previously, make sure you have the latest version by runninggcloud components update.
3. You must have access to Microsoft Entra and Microsoft Graph.
4. You must have Power BI set up.

Costs

Workforce Identity Federation is availableas a no-cost feature. However, Workforce Identity Federation detailed audit logging uses Cloud Logging. To learn about Logging pricing,seeGoogle Cloud Observability pricing.

Required roles

This section describes roles that are required for administrators and resources.

Roles for administrators

To get the permissions that you need to configure Workforce Identity Federation, ask your administrator to grant you theIAM Workforce Pool Admin (roles/iam.workforcePoolAdmin) IAM role on the organization. For more information about granting roles, seeManage access to projects, folders, and organizations.

You might also be able to get the required permissions throughcustom roles or otherpredefined roles.

Alternatively, the IAM Owner (roles/owner) basic role alsoincludes permissions to configure identity federation.You should not grant basic roles in a production environment, but you can grant them in adevelopment or test environment.

Roles for federated identities

Power BI sends theuserProject parameter during token exchange. Because ofthis, you must ask your administrator to grant the role Service Usage Consumer(roles/serviceusage.serviceUsageConsumer) to the federated identities on thebilling project.

To grant the role to a group of federated identities, run the following command:

gcloudprojectsadd-iam-policy-bindingPROJECT_ID\--role="roles/serviceusage.serviceUsageConsumer"\--member="principalSet://iam.googleapis.com/locations/global/workforcePools/WORKFORCE_POOL_ID/group/GROUP_ID"

Replace the following:

• PROJECT_ID: the billing project ID.
• WORKFORCE_POOL_ID: the workforce identity pool ID.
• GROUP_ID: the group ID—for example,admin-group@altostrat.com. To see a list of common principal identifiers, seePrincipal identifiers.

Create a workforce identity pool

This section describes how to create the workforce identity pool. You create theworkforce identity pool provider later in this guide.

gcloudiamworkforce-poolscreateWORKFORCE_POOL_ID\--organization=ORGANIZATION_ID\--display-name="DISPLAY_NAME"\--description="DESCRIPTION"\--session-duration=SESSION_DURATION\--location=global

Register a new Microsoft Entra app

This section shows you how to create a Microsoft Entra app using the MicrosoftAzure portal.

1. Register a new Microsoft Entra application.

2. In the Microsoft Entra application that you registered,create a new client secret. Note the client secret.

3. Grant API permissions to your Microsoft Entra application so that it canaccess users and groups information from Active Directory. To grantpermissions for Microsoft Graph API, do the following:

   1. In your application, selectAPI Permissions.
   2. InConfigured permissions, clickAdd a permission.
   3. in theRequest API permissions dialog, selectMicrosoft Graph.
   4. SelectApplication permissions.
   5. In theSelect Permissions dialog, do the following:
      1. In the search field, enterUser.ReadBasic.All.
      2. ClickUser.ReadBasic.All.
      3. ClickAdd permissions.
   6. in theRequest API permissions dialog, selectMicrosoft Graph.
   7. SelectApplication permissions.
   8. In theSelect Permissions dialog, do the following:
      1. In the search field, enterGroupMember.Read.All.
      2. ClickGroupMember.Read.All.
      3. ClickAdd permissions.
   9. InConfigured permissions, clickGrant admin consent for (domain name).
   10. When you are asked to confirm, clickYes.

4. To access the values that you need to configure the workforce pool providerlater in this guide, do the following:

   1. Go to theOverview page of the Microsoft Entra application.
   2. ClickEndpoints.

   3. Note the following values:

      • Client ID: the ID of the Microsoft Entra app that you registeredearlier in this guide.
      • Client Secret: the client secret that you generated earlier inthis guide.
      • Tenant ID: the tenant ID of the Microsoft Entra app that youregistered earlier in this guide.
      • Issuer URI: the URI of the OpenID Connect metadata document,omitting/.well-known/openid-configuration. For example, if theOpenID Connect metadata document URL ishttps://login.microsoftonline.com/d41ad248-019e-49e5-b3de-4bdfe1fapple/v2.0/.well-known/openid-configuration,then the Issuer URI ishttps://login.microsoftonline.com/d41ad248-019e-49e5-b3de-4bdfe1fapple/v2.0/.

Create a workforce identity pool provider

To create the provider, run the following command:

gcloudiamworkforce-poolsproviderscreate-oidcWORKFORCE_PROVIDER_ID\--workforce-pool=WORKFORCE_POOL_ID\--location=global\--display-name=DISPLAY_NAME\--issuer-uri=ISSUER_URI\--client-id=https://analysis.windows.net/powerbi/connector/GoogleBigQuery\--attribute-mapping=ATTRIBUTE_MAPPING\--web-sso-response-type=id-token\--web-sso-assertion-claims-behavior=only-id-token-claims\--extra-attributes-issuer-uri=APP_ISSUER_URI\--extra-attributes-client-id=APP_CLIENT_ID\--extra-attributes-client-secret-value=APP_CLIENT_SECRET\--extra-attributes-type=EXTRA_GROUPS_TYPE\--extra-attributes-filter=EXTRA_FILTER\--detailed-audit-logging

Replace the following:

• WORKFORCE_PROVIDER_ID: a unique provider ID. The prefixgcp-is reserved and can't be used in a provider ID.

• WORKFORCE_POOL_ID: the workforce identity pool ID toconnect your IdP to.

• DISPLAY_NAME: an optional user-friendly display namefor the provider.

• ISSUER_URI: the value of issuer uri, formatted as,https://sts.windows.net/TENANT_ID. ReplaceTENANT_ID with the tenant ID that you notedearlier.

• ATTRIBUTE_MAPPING: a mapping of the group and,optionally, other attributes from the Microsoft Entra claim toGoogle Cloud attributes—for example:google.groups=assertion.groups, google.subject=assertion.sub. The groupis granted access on BigQuery data later in this guide.

  For more information, seeAttribute mapping.

• APP_ISSUER_URI: the issuer URI of the Microsoft Entraapplication that you noted earlier.

• APP_CLIENT_ID: the issuer client ID that you notedearlier.

• APP_CLIENT_SECRET: the issuer client secret that younoted earlier.

• EXTRA_GROUPS_TYPE: the type of group identifier, whichcan be one of the following:

  • azure-ad-groups-mail: Group email addresses are retrieved from theIdP—for example:admin-group@altostrat.com.

  • azure-ad-groups-id: UUIDs that represent the groups are retrieved from theIdP—for example:abcdefgh-0123-0123-abcdef.

• EXTRA_FILTER: the filter used to request specificassertions to be passed from the IdP. By specifying--extra-attributes-type=azure-ad-groups-mail,--extra-attributes-filterfilters for a user's group claims that are passed from theIdP. By default, all of the groups associated with the user are fetched. Thegroups that are used must be mail and security enabled. To learn more, seeUse the $search query parameter.

  Important: A maximum of 999 groups can be fetched.

  The following example filters for groupsthat are associated with user email addresses that start withgcp:

  --extra-attributes-filter='"mail:gcp"'

  The following example filters groups that are associated with users that have email addresses starting withgcp and adisplayName that containsexample:

  --extra-attributes-filter='"mail:gcp" AND "displayName:example"'

• Workforce Identity Federationdetailed audit logging logs information received from your IdP to Logging. Detailed audit logging can help you troubleshoot your workforce identity pool provider configuration. To learn how to troubleshoot attribute mapping errors with detailed audit logging, seeGeneral attribute mapping errors. To learn about Logging pricing, seeGoogle Cloud Observability pricing.

  To disable detailed audit logging for a workforce identity pool provider, omit the--detailed-audit-logging flag when you rungcloud iam workforce-pools providers create. To disable detailed audit logging, you can alsoupdate the provider.

Create IAM policies

In this section, you create an IAM allow policy that grants therole BigQuery Data Viewer (roles/bigquery.dataViewer) to the mapped group onthe project where your BigQuery data is stored. Thepolicy lets all identities that are in the group view data fromBigQuery tables and views that are stored in the project.

To create the policy, run the following command:

gcloudprojectsadd-iam-policy-bindingBIGQUERY_PROJECT_ID\--role="roles/bigquery.dataViewer"\--member="principalSet://iam.googleapis.com/locations/global/workforcePools/WORKFORCE_POOL_ID/group/GROUP_ID"

Replace the following:

• BIGQUERY_PROJECT_ID: the project ID where yourBigQuery data and metadata are stored.
• WORKFORCE_POOL_ID: the workforce identity pool ID

• GROUP_ID: the group identifier, which depends onthe value of--extra-attributes-type that was used to create the workforceidentity pool provider, as follows:

  • azure-ad-groups-mail: the group identifier is an email address—forexample:admin-group@altostrat.com

  • azure-ad-groups-id: the group identifier is a UUID for the group—forexample:abcdefgh-0123-0123-abcdef

Access BigQuery data from Power BI Desktop

To access BigQuery data from Power BI Desktop, do the following:

1. Open Power BI.
2. ClickGet Data.
3. ClickDatabase.
4. In the list of databases, selectGoogle BigQuery (Microsoft Entra ID) (Beta).
5. ClickConnect.

6. Fill in the following required fields:

   • Billing project ID: the billing project ID.

   • Audience URI: the Google Cloud URI, formatted as follows:

     //iam.googleapis.com/locations/global/workforcePools/WORKFORCE_POOL_ID/providers/WORKFORCE_PROVIDER_ID

     Replace the following:

     • WORKFORCE_POOL_ID: the workforce identity poolID.

     • WORKFORCE_PROVIDER_ID: the workforce identity poolprovider ID.

7. ClickOk.

8. ClickNext.

9. ClickSelect the data.

If you are asked to sign in, use a Microsoft Entra identity that is a member ofthe group.

You can now use data from BigQuery in Power BI Desktop.

Access the BigQuery data from Power BI Web

To access BigQuery data from Power BI Web, do the following:

1. Go to Power BI Web.

2. ClickPower query to add new data source.

3. ClickGet data.

4. In the list, find and select theGoogle BigQuery (Microsoft Entra ID) (Beta).

5. Fill in the following required fields:

   • Billing Project ID: the Google Cloud billing project

   • Audience URI: the audience URI, formatted as follows:

     //iam.googleapis.com/locations/global/workforcePools/WORKFORCE_POOL_ID/providers/WORKFORCE_PROVIDER_ID

     Replace the following:

     • WORKFORCE_POOL_ID: the workforce identity poolID

     • WORKFORCE_PROVIDER_ID: the workforce identity poolprovider ID

6. ClickConnection Credentials>Authentication kind.

7. SelectOrganizational account.

8. ClickSign in.

9. ClickNext.

10. ClickSelect the data.

You can now use data from BigQuery in Power BI Web.

What's next

• To delete Workforce Identity Federation users and their data, seeDelete Workforce Identity Federation users and their data
• To learn about Google Cloud products' support forWorkforce Identity Federation, seeIdentity federation: supported products and limitations