OAuth with Snowflake and Azure AD

Step 1: Register an app for Sigma in Azure AD

  1. Go to the Microsoft Azure Portal and authenticate.

  2. Go to to Azure Active Directory.

  3. Click App Registrations.

  4. Click New Registration.

  5. Enter "Sigma OAuth App" or a similar name to identify the Sigma app in Azure AD.

  6. Verify that Supported account types is set to Single Tenant.

  7. Click Register.

  8. From the Overview page, click Add a Redirect URI, then do the following:

    1. Click Add a platform.

    2. Click Web and enter one of the following:

    3. Select Access tokens.

    4. Click Configure.

    5. Select the "Single tenant" account type.

  9. Click Expose an API.

  10. Click Set next to Application ID URI and set the Application ID URI to the following, replacing <your-azure-verified-domain-name> and <your-application-(client)-id> with the relevant values:
    https://<your-azure-verified-domain-name>.com/<your-application-(client)-id>

  11. Click Add a scope to add a scope representing the Snowflake role.

    1. Set Scope name to "session:role-any".
    2. Select "Admins only" for consent.
    3. Enter a recognizable name for consent display name. For example, "Sigma - Snowflake consent".
    4. Enter a Consent description, for example, "Allows Sigma to inherit Snowflake user roles".
    5. Click Add Scope.
  12. Click Certificates & secrets, and then select New client secret.

    1. Enter a description. For example, "Sigma Client Secret".
    2. Select an expiration period. For testing purposes, you might select "Never".
    3. Click Add.
    4. Copy the client secret to use in Sigma later. You cannot retrieve the client secret after you perform another operation or leave this blade. If you lose the client secret, you can create another one.
  13. Click API permissions from the navigation bar on the left.

    1. Verify that the User.Read permission from the Microsoft Graph API is listed.
    2. Click Microsoft Graph API.
    3. On the blade that opens to the right, click Delegated permissions.
    4. On the Permission list, expand the OpenID permissions section.
    5. Select the email, offline_access, openid, and profile permissions.
    6. Click Update permissions.
    7. Click Grant admin consent, then click Yes.

Step 2: Collect Azure Information

  1. Navigate to the Microsoft Azure Portal and authenticate.

  2. Navigate to Azure Active Directory.

  3. Click App Registrations.

  4. Click the "Sigma OAuth App" app created in Step 1.

    1. From the Overview interface, click Endpoints and copy the OpenID Connect metadata document.

    2. From the Overview interface, copy the Application ID URI.

    3. Client secret from Step 1.11

      ๐Ÿ“˜

      If you did not make a copy earlier, create a new secret.

    4. From the Overview interface, click Endpoints.

    5. Copy the Federation metadata document URL and open it in a new browser tab.

    6. Locate the "entityID" parameter and copy its value.

      This is known as the external_oauth_issuer in Snowflake.

    7. From the same Endpoints view, do the following:

      1. Copy the OpenID Connect metadata document URL and open in a new browser.

      2. Locate the "jwks_uri" parameter and copy its value.

        This is known as the external_oauth_jws_keys_url in Snowflake.

    8. From the Overview interface, copy the Application ID URI in the form of https://<app id>.

  5. Items for Sigma:

  6. Items for Snowflake:

Step 3: Create a Security Integration in Snowflake

This step involves creating a security integration in Snowflake to ensure that Snowflake can communicate with Microsoft Azure AD securely, validate the tokens from Azure AD, and provide the appropriate Snowflake data access to Sigma users based on the user role associated with the OAuth token.

๐Ÿšฉ

Only account administrators (i.e. users with the ACCOUNTADMIN role) or a role with the global CREATE INTEGRATION privilege can execute this SQL command.

The security integration parameter values are case-sensitive, and the values you put into the security integration must match those values in your environment. If the case does not match, it's possible that the access token might not be validated, resulting in a failed authentication attempt.

Verify all values are an exact match. For example, if the Issuer value does not end with a backslash and the security integration is created with a backslash character at the end of the URL, an error message will occur. It would then be necessary to drop the security integration object (using DROP INTEGRATION) and create the object again with the correct Issuer value (using CREATE SECURITY INTEGRATION).

In order to run the following SQL statement in Snowflake, locate the values from Step 2 - 4.b:

  1. Execute create statement:
 create security integration <enter_a_name_for_integration>

 type = external_oauth  
  
 enabled = true  
  
 external_oauth_type = azure  
  
 external_oauth_issuer = '<external_oauth_issuer>' //entityID from 4.b.i  
  
 external_oauth_jws_keys_url = โ€˜<external_oauth_jws_keys_url>โ€™ //jwks_uri from 4.b.ii  
  
 external_oauth_audience_list = (โ€˜<application_id_uri>') //application_id_uri from 4.b.iii  
  
 external_oauth_token_user_mapping_claim = 'upn'  
  
 external_oauth_snowflake_user_mapping_attribute = 'login_name'  
  
 external_oauth_any_role_mode = 'ENABLE';

Step 4: Configure OAuth in Sigma

  1. Open your Admin Portal by selecting Administration in the user menu at the top right of your screen.
  2. Select the Authentication page from the left hand panel.
  3. Click the blue Edit button under Authentication Method and Options.
  4. Select โ€˜OAuth or Passwordโ€™ from the Authentication Method dropdown menu.
  5. Under Metadata URI, enter the OAuth metadata URI from Step 2: 4.a.i
  6. Under Client ID, enter the Application ID URI from Step 2: 4.a.ii
  7. Under Client Secret, enter the client secret from Step 2: 4.a.iii
  8. Click Save.
  9. Test your OAuth configuration by logging out and logging back into Sigma. Your organizationโ€™s login page should now display a Log in with SSO button.