Connect to BigQuery with OAuth (Beta)

If you want to authenticate to BigQuery from Sigma using connection-level OAuth, follow the steps in this document after you have configured a BigQuery connection, as well as created a Sigma OAuth application.

For more details about using OAuth with Sigma, see Configure OAuth.

Limitations

For a BigQuery connection configured to use OAuth, the following limitations apply:

• You cannot reuse organization-level OAuth configurations. You must configure OAuth separately for a BigQuery connection. See Use different OAuth configurations for authenticating users to your connections than you use for your Sigma organization.
• CSV uploads are disabled.

Requirements

• You must be assigned the Admin account type in Sigma.
• You must be assigned the IAM Workforce Pool Admin role in Google Cloud platform.

Prerequisites

• Configure a BigQuery connection as detailed in Connect to BigQuery.

• If you are creating a new connection, follow the first four steps in Create a connection in Sigma before selecting and configuring OAuth as your authentication method.

• Complete the procedure in Configure a Sigma OAuth application. Information from this application is required throughout the OAuth setup process.

Connect to BigQuery with connection-level OAuth

Using connection-level OAuth with BigQuery allows you to configure your connection such that BigQuery permissions are inherited in Sigma on a per-user basis, regardless of your Sigma organization's authentication method.

Connecting to BigQuery with OAuth is a multi-step process. After completing all prerequisites, to set up a unique OAuth configuration for this connection, do the following:

1. Configure a Google Cloud Workforce Identity Federation pool
2. Configure connection credentials in Sigma
3. Configure OAuth features in Sigma
4. Complete your OAuth configuration

Step 1: Configure a Google Cloud Workforce Identity Federation pool

In Google Cloud, create a workforce identity federation pool Workforce Identity Federation pool. For instructions on how to create a workforce identity federation pool, see the Google Cloud documentation on Manage workforce identity pool providers.

When creating your provider pool, use the information obtained during the creation of your Sigma OAuth application:

• When prompted for an Issuer (URL), provide the issuer URL from step 3 in Configure a Sigma OAuth application.
• When prompted for a Client ID, provide the client ID from step 1 in Configure a Sigma OAuth application.

Ensure that your workforce pool's principal group is granted the following roles and permissions, in order to have appropriate read/write access in Sigma:

• BigQuery Data Viewer
• BigQuery Job User
• serviceusage.services.use permission
• BigQuery Data Editor (This role is only required if you intend to enable write access on your connection.)

For more information on these roles and permissions, see Workforce principal identifiers for IAM policies.

After creating your workforce identity pool:

• Record the Resource name of the workforce pool provider. You need this value when configuring OAuth in Sigma.
• Verify that under Attribute mappings, the google.subject field indicates assertion.sub.

Step 2: Configure connection credentials in Sigma

To configure your BigQuery connection to use OAuth:

1. When configuring your BigQuery connection, under Connection credentials, select Authentication > OAuth.

2. In the Audience field, enter the resource name of the workforce pool provider you created in Google Cloud, prepended by //iam.googleapis.com/. The correct format is //iam.googleapis.com/<resource_name> - for example, //iam.googleapis.com/locations/global/workforcePools/<workforce_identity_pool>/providers/<workforce_identity_pool_provider>.

3. (Optional) In Additional project IDs, add any additional BigQuery project IDs. The project IDs should be comma-separated.

Step 3: Configure OAuth features in Sigma

After configuring your connection credentials, configure your desired OAuth features in the OAuth Features section:

1. For OAuth Provider, choose one of the supported providers.

2. For Scopes, enter any additional scopes to specify the access of the OAuth token.

   • The default scopes openid, profile, email are required.
   • The default scope offline_access is strongly recommended but not required. If this scope is not provided, users must log in every time their access token expires and any scheduled operations, such as materializations and scheduled exports, fail if the tasks run for a longer duration than the access token expiration configured in the IdP (such as 5 minutes).

   For more information about these scopes, see the table in Step 3: Create an OAuth authorization server in Configure a Sigma OAuth application.

3. In the Metadata URI field, enter the OAuth metadata URI from your IdP, obtained from Step 3: Create an OAuth authorization server in Configure a Sigma OAuth application).

4. In the Redirect URI field, select Copy to clipboard () and store the value somewhere. You need this value to complete the OAuth configuration in your IdP.

5. In the Client ID field, enter the client ID from your OAuth application.

6. Provide the relevant authentication details to match what you configured in your OAuth application:

   • If you configured a client secret in your OAuth application, for Client Secret, enter the client secret from your OAuth application. After you enter and save this value, Sigma does not display it.
   • If you also configured Proof Key for Code Exchange (PKCE) in your OAuth application, select the checkbox for Require PKCE.
   • If you configured your OAuth application to authenticate with a public key and private key pair, or a JWT bearer token, select the checkbox for Use JWT bearer tokens. You do not need to provide a client secret.

7. Complete the remaining steps. See Complete your OAuth configuration.

Step 4: Complete your OAuth configuration

Several additional configuration options are available:

• (Optional) Configuring a service account for your connection
• (Optional) Enabling write access for your connection
• Configuring additional connection features and completing your OAuth configuration

(Optional) Configure a service account for your connection

Determine whether you plan to use functionality in Sigma that requires a service account. Service accounts are accounts created explicitly for administrative purposes, instead of a human user.

Creating a service account in BigQuery can serve multiple purposes in Sigma:

• If you enable write access on this connection and plan to use input tables, a service account is required. Sigma uses the service account to log all edits made to all input tables on this connection.
• If you plan to use public embedding, a service account is required. Service account credentials are used to run queries on publicly embedded dashboards.
• If you want users assigned the Admin account type to configure individual workbooks to run using a service account rather than each individual’s OAuth credentials, a service account is required.

If you determine that you need a service account, first configure your service account in BigQuery:

1. Create a new user in BigQuery to use as the service account user. For instructions on creating a service account, see Create service accounts in the Google Cloud IAM documentation.

2. Create a JSON private key for the the service account. For instructions on creating a JSON private key for the service account, see Create and delete service account keys in the Google Cloud IAM documentation.

3. Grant the service account user the necessary permissions to perform service account tasks. The specific permissions depend on your use case. A suggested default is:

   • BigQuery Data Viewer
   • BigQuery Job User
   • serviceusage.services.use permission
   • BigQuery Data Editor (This role is only required if you intend to enable write access on your connection.)

After creating or configuring a user to use as the service account, complete the following steps in Sigma:

1. In the Service account configuration section, turn on the Service account toggle.
2. In the Service account* field, paste the generated service account JSON private key.

(Optional) Enable write access for your connection

See About OAuth with write access for information about how write access works on OAuth connections.

If you want to enable write access on this connection:

1. Turn on the Enable write access toggle.

2. Provide at least one write Destination where BigQuery should store write back data from Sigma. Use the format projectname.datasetname.

3. (Optional, but required to use input tables) In the Input table edit log destination field, provide an additional projectname.datasetname destination specifically to log all edits made to input tables on this connection. This projectname.datasetname should be used only for this purpose. Only your service account should have access to write to this dataset. If you leave this field blank, input tables cannot be created on this connection.

4. (Optional) If you want to enter additional destinations, select + Add another destination and repeat the steps above.

Next, see Configure connection features for additional options. Or, if you are finished configuring your connection, click Create at the top right to create your connection.

Configure connection features

To complete your connection configuration, see Step 7 onwards in Create a connection in Sigma in Connect to BiqQuery.