OAuth with Snowflake and Azure AD

Step 1: Register an App for Sigma in Azure AD

  1. Navigate to the Microsoft Azure Portal and authenticate.
  2. Navigate to Azure Active Directory.
  3. Click on App Registrations.
  4. Click on New Registration.
  5. Enter “Sigma Oauth App” or similar. 
  6. Verify the Supported account types are set to Single Tenant.
  7. Click Register.
  8. From the Overview page click on Add a Redirect URI
  9. Click on Expose an API
  10. Click on the Set link next to Application ID URI to set the Application ID URI. Note: The default option in Azure for the Application ID URI begins with api://. This must be changed to begin with https://
    Ex. api://<app_id> change to https://<app_id>
  11. Click on Add a scope to add a scope representing the Snowflake role.
    • Set “Scope name” to “session:role-any”
    • Select “Admins only” for consent. 
    • Enter a recognizable name for consent display name: ex. “Sigma - Snowflake consent”
    • Consent description:
      Ex. “Allows Sigma to inherit Snowflake user roles”
    • Click on the “Add scope” button at the bottom. 
  12.  Click on Certificates & secrets and then New client secret.
    • Enter description ex. “Sigma Client Secret”
    • Select expiration period.
      Note: For testing purposes select “Never” 
    • Click Add
    • Copy the secret, aka “Client Secret” to be used in Sigma later.
      Note: Copy the new client secret value. You won't be able to retrieve it after you perform another operation or leave this blade. You can always create a new one.  
  13. Click on API permissions from the navigation bar on the left. 
    • You should already have the User.Read permission from the Microsoft Graph API listed. 
    • Click on Microsoft Graph API
    • On the blade opening on the right click on Delegated permissions.
    • On the Permission list below expand the OpenID permissions.
    • Select email, offline_access, openid, profile permissions.
    • Click on the Update permissions button at the bottom. 
    • Click on Grant admin consent 
    • Click on the Yes button.

Step 2: Collect Azure Ad Information 

  1. Navigate to the Microsoft Azure Portal and authenticate.
  2. Navigate to Azure Active Directory.
  3. Click on App Registrations.
  4. Click on the  “Sigma Oauth App”  app created in Step 1. 
    1. From the Overview interface click on Endpoints and copy the OpenID Connect metadata document
    2. From the Overview interface copy the Application (client) ID
    3. Client secret from Step 1.11
      Note: If you did not make a copy earlier, create a new secret.
    1. From the Overview interface click on Endpoints.
      1. Copy the Federation metadata document url and open in a new browser.
      2. Locate the “entityID” parameter and copy its value.
      3. This is known as the “external_oauth_issuer” in Snowflake.
    2. From the same Endpoints view:
      1. Copy the OpenID Connect metadata document url and open in a new browser. 
      2. Locate the “jwks_uri” parameter and copy its value. 
      3. This is known as “external_oauth_jws_keys_url” in Snowflake.
    3. From the Overview interface copy the Application ID URI which should be in the form of “https://<app id>” 
    1. Items for Sigma: 
    2. 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.

Important:
Note that 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 is possible that the access token will 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 then create the object again with the correct Issuer value (using CREATE SECURITY INTEGRATION).

In order to run the following SQL statement in Snowflake please have the values from Step 2 - 4.b ready:

  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 client ID 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.