This guide shows you how to let users that are in Microsoft Entra groups access BigQuery data in Power BI by using Workforce Identity Federation.
Microsoft Entra is the identity provider (IdP). Groups claims from Microsoft Entra 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
- Make sure that you have a Google Cloud organization set up.
-
After installing the Google Cloud CLI, configure the gcloud CLI to use your federated identity and then initialize it by running the following command:
gcloud init
- You must have access to Microsoft Entra and Microsoft Graph.
- You must have Power BI set up.
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 the
IAM Workforce Pool Admin (roles/iam.workforcePoolAdmin
) IAM role on the organization.
For more information about granting roles, see Manage access to projects, folders, and organizations.
You might also be able to get the required permissions through custom roles or other predefined roles.
Alternatively, the IAM Owner (roles/owner
) basic role also
includes permissions to configure identity federation.
You should not grant basic roles in a production environment, but you can grant them in a
development or test environment.
Roles for federated identities
Power BI sends the userProject
parameter during token exchange. Because of
this, you must ask your administrator to grant the role Service Usage Consumer
(roles/serviceusage.serviceUsageConsumer
) to the federated identities on the
billing project.
To grant the role to a group of federated identities, run the following command:
gcloud projects add-iam-policy-binding PROJECT_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@example.com
. To see a list of common principal identifiers, see Principal identifiers.
Create a workforce identity pool
This section describes how to create the workforce identity pool. You create the workforce identity pool provider later in this guide.
Console
To create the workforce identity pool, do the following:
In the Google Cloud console, go to the Workforce Identity Pools page:
Click Create pool and do the following:
In the Name field, enter the display name of the pool. The pool ID is automatically derived from the name as you type, and it is displayed under the Name field. You can update the pool ID by clicking Edit next to the pool ID.
Optional: In Description, enter a description of the pool.
Session duration is set by default. To enter a custom session duration, click Edit. Session duration determines how long the Google Cloud access tokens, console (federated) sign-in sessions, and gcloud CLI sign-in sessions from this workforce pool are valid. The duration must be greater than 15 minutes (900s) and less than 12 hours (43200s). If session duration is not set, it defaults to a duration of one hour (3600s).
To create the pool in the enabled state, make sure that Enabled Pool is on.
To create the workforce identity pool, click Next.
gcloud
To create the workforce identity pool, run the following command:
gcloud iam workforce-pools create WORKFORCE_POOL_ID \
--organization=ORGANIZATION_ID \
--display-name="DISPLAY_NAME" \
--description="DESCRIPTION" \
--session-duration=SESSION_DURATION \
--location=global
Replace the following:
WORKFORCE_POOL_ID
: an ID that you choose to represent your Google Cloud workforce pool. For information on formatting the ID, see the Query parameters section in the API documentation.ORGANIZATION_ID
: the numeric organization ID of your Google Cloud organization.DISPLAY_NAME
: Optional. A display name for your workforce identity pool.DESCRIPTION
: Optional. A workforce identity pool description.SESSION_DURATION
: Optional. The session duration, which determines how long the Google Cloud access tokens, console (federated) sign-in sessions, and gcloud CLI sign-in sessions from this workforce pool are valid. The duration must be greater than 15 minutes (900s) and less than 12 hours (43200s). If session duration is not set, it defaults to a duration of one hour (3600s).
Register a new Microsoft Entra app
This section shows you how to create a Microsoft Entra app using the Microsoft Azure portal.
In the Microsoft Entra application that you registered, create a new client secret. Note the client secret.
Grant API permissions to your Microsoft Entra application so that it can access users and groups information from Active Directory. To grant permissions for Microsoft Graph API, do the following:
- In your application, select API Permissions.
- In Configured permissions, click Add a permission.
- in the Request API permissions dialog, select Microsoft Graph.
- Select Application permissions.
- In the Select Permissions dialog, do the following:
- In the search field, enter
User.ReadBasic.All
. - Click User.ReadBasic.All.
- Click Add permissions.
- In the search field, enter
- in the Request API permissions dialog, select Microsoft Graph.
- Select Application permissions.
- In the Select Permissions dialog, do the following:
- In the search field, enter
GroupMember.Read.All
. - Click GroupMember.Read.All.
- Click Add permissions.
- In the search field, enter
- In Configured permissions, click Grant admin consent for (domain name).
- When you are asked to confirm, click Yes.
To access the values that you need to configure the workforce pool provider later in this guide, do the following:
- Go to the Overview page of the Microsoft Entra application.
- Click Endpoints.
Note the following values:
- Client ID: the ID of the Microsoft Entra app that you registered earlier in this guide.
- Client Secret: the client secret that you generated earlier in this guide.
- Tenant ID: the tenant ID of the Microsoft Entra app that you registered earlier in this guide.
- Issuer URI: the URI of the OpenID Connect metadata document,
omitting
/.well-known/openid-configuration
. For example, if the OpenID 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:
gcloud iam workforce-pools providers create-oidc 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=azure-ad-groups-mail \
--extra-attributes-filter=FILTER
Replace the following:
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 to connect your IdP to.DISPLAY_NAME
: an optional user-friendly display name for 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 noted earlier.ATTRIBUTE_MAPPING
: a mapping of the group and, optionally, other attributes from the Microsoft Entra claim to Google Cloud attributes—for example:google.groups=assertion.groups, google.subject=assertion.sub
. The group is granted access on BigQuery data later in this guide.APP_ISSUER_URI
: the issuer URI of the Microsoft Entra application that you noted earlier.APP_CLIENT_ID
: the issuer client ID that you noted earlier.APP_CLIENT_SECRET
: the issuer client secret that you noted earlier.FILTER
: the filter used to request specific assertions passed from the IdP. By specifying--extra-attributes-type=azure-ad-groups-mail
,--extra-attributes-filter
filters for a user's group claims that are passed from the IdP. By default, all of the groups associated with the user are fetched. The groups that are used must be mail and security enabled. To learn more, see Use the $search query parameter. A maximum of 100 groups can be fetched.The following example filters for groups that are associated with user email addresses that start with
gcp
: The following example filters groups that are associated with users that have email addresses starting with--extra-attributes-filter='"mail:gcp"'
gcp
and a displayName that containsexample
:--extra-attributes-filter='"mail:gcp" AND "displayName:example"'
Create IAM policies
In this section, you create an IAM allow policy that grants the
role BigQuery Data Viewer (roles/bigquery.dataViewer
) to the mapped group on
the project where your BigQuery data is stored. The
policy lets all identities that are in the group view data from
BigQuery tables and views that are stored in the project.
To create the policy, run the following command:
gcloud projects add-iam-policy-binding BIGQUERY_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 your BigQuery data and metadata are storedWORKFORCE_POOL_ID
: the workforce identity pool IDGROUP_ID
: the group—for example,admin-group@example.com
Access BigQuery data from Power BI Desktop
To access BigQuery data from Power BI Desktop, do the following:
- Open Power BI.
- Click Get Data.
- Click Database.
- In the list of databases, select Google BigQuery (Microsoft Entra ID) (Beta).
- Click Connect.
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/PROVIDER_ID
Replace the following:
WORKFORCE_POOL_ID
: the workforce identity pool ID.PROVIDER_ID
: the workforce identity pool provider ID.
Click Ok.
Click Next.
Click Select the data.
If you are asked to sign in, use a Microsoft Entra identity that is a member of the 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:
Go to Power BI Web.
Click Power query to add new data source.
Click Get data.
In the list, find and select the Google BigQuery (Microsoft Entra ID) (Beta).
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/PROVIDER_ID
Replace the following:
WORKFORCE_POOL_ID
: the workforce identity pool IDPROVIDER_ID
: the workforce identity pool provider ID
Click Connection Credentials > Authentication kind.
Select Organizational account.
Click Sign in.
Click Next.
Click Select 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, see Delete Workforce Identity Federation users and their data
- To learn about Google Cloud products' support for Workforce Identity Federation, see Identity federation: supported products and limitations