Integrating Snowflake with Azure AD via OAuth

The following steps will walk you through the process of integrating Azure Active Directory (Azure AD) with Snowflake for authentication using OAuth.

1. Setting Up a Snowflake OAuth Resource in Azure AD

Sign in to the Microsoft Azure Portal

1.1. App registration for Snowflake OAuth resource

• Navigate to Azure Active Directory.

• Choose App registrations and then select New registration.

• Enter a suitable name. For example, Tellius Snowflake OAuth Resource.

• Under Supported account types, choose Accounts in this organizational directory only (Tellius only - Single Tenant).

Setting up Snowflake OAuth resource in Azure AD

1.2 Modifying the API

• Once registered, select Expose an API from the left-hand pane.

• Next to Application ID URI, click on Set. Change the default value (api://<alphanumeric value>) to one of the following and click on Save.

  • https://<alphanumeric value>.yourcompany.com (or)

  • https://yourcompany.com/<alphanumeric value>

• For example: https://a1a79972-aecd-4b87-b28b-1bcf94aca1bf.xyz.com Please make sure your domain name is verified in Azure AD.

• Remember to note down this URI as it will be used in future steps and is referred to as <SNOWFLAKE_APPLICATION_ID_URI>.

Editing application ID

1.3 Adding a new scope

• Click on Add a scope.

• Under Scope name, type session:role-any. This allows Snowflake users to use any role they are granted.

• Opt for both Admin and Users to give consent.

Adding a scope

2. Creating a Snowflake OAuth Client App in Azure AD

2.1 App registration for Snowflake OAuth Client

• Again, navigate to Azure Active Directory.

• Choose App Registrations and then New registration.

• Provide a suitable name (for example: Tellius Snowflake OAuth Client) and under Supported account types, choose Accounts in this organizational directory only (Tellius only - Single Tenant).

Registring for Snowflake OAuth Client

• Once registered, click on Overview.

• Find the Application (client) ID field and copy the ID. It's denoted as <OAUTH_CLIENT_ID> for future steps.

2.2 Setting up Authentication

• Go to Authentication. Under the Web section, provide the redirect URI in this format: https://<Tellius URL>/dataset/wizard/snowflake.

2.3 Generating Client Secret

• Navigate to Certificates & secrets.

Clent secrets

• Click on New client secret and choose a suitable expiry time.

• Copy the secret's value. It will be referred to as <OAUTH_CLIENT_SECRET> in upcoming steps.

2.4 Defining API permissions

• Go to API permissions and select Add a permission.

• Pick My APIs and choose the Snowflake OAuth Resource you set up earlier.

• On the Request API permissions page, check the Delegated permissions box. Then, select the permission related to the scope defined in the application from the list.

Requesting API permissions

• Click on Add permissions.

Requesting API permissions

• Under Configured permissions, select Grant admin consent for Default Directory and click Yes on the confirmation message.

Configured permissions

3. Collecting Azure AD OAuth Information

3.1 Accessing OAuth Details

• Navigate back to the Snowflake OAuth Resource App.

• In the Overview section, select Endpoints.

• On the displayed panel, copy the OAuth 2.0 token endpoint (v2) for OpenID Connect metadata and Federation Connect metadata. This will be referred to as <AZURE_AD_OAUTH_TOKEN_ENDPOINT> in subsequent steps.

• The endpoint should be similar to https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token/.

Accessing OAuth

3.2. Gathering OpenID Connect Metadata

• Open the URL for OpenID Connect metadata in a new browser tab.

• Find and copy the value of the "jwks_uri" parameter, which will be referred as <AZURE_AD_JWS_KEY_ENDPOINT> in the subsequent steps.

• The endpoint should be similar to https://login.microsoftonline.com/<tenant_id>/discovery/v2.0/keys.

Endpoints tab

3.3. Fetching Federation Metadata

• Launch the URL for the Federation metadata document in a new browser tab.

• In the displayed XML, locate the "entityID" parameter in the XML Root Element and copy its value. This will be referred to as <AZURE_AD_ISSUER> in the subsequent steps.

• The entityID value should be similar to https://sts.windows.net/<tenant_id>/.

The OAuth 2.0 authorization endpoint (v2) should be similar to https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/authorize which will be referred as <AZURE_AD_OAUTH_AUTH_ENDPOINT>

4. Configuring the OAuth Authorization server on Snowflake

1. The following are the required values for configuring Snowflake to create a security integration and connect to Azure AD:

• AZURE_AD_ISSUER - Refer this section

• AZURE_AD_JWS_KEY_ENDPOINT - Refer this section

• SNOWFLAKE_APPLICATION_ID_URI - Refer this section

• Mapping Attribute - 'EMAIL_ADDRESS' or 'LOGIN_NAME'

• Azure AD uses the email address as the username. If Snowflake has the same email set as the login name, any of these attributes can be used.

1. To allow Snowflake to utilize the OAuth tokens from Azure AD, execute the following command:

create security integration external_oauth_azure
    type = external_oauth
    enabled = true
    external_oauth_type = azure
    external_oauth_issuer = '<AZURE_AD_ISSUER>'
    external_oauth_jws_keys_url = '<AZURE_AD_JWS_KEY_ENDPOINT>'
    external_oauth_audience_list = ('<SNOWFLAKE_APPLICATION_ID_URI>')
    external_oauth_token_user_mapping_claim = 'upn'
    external_oauth_any_role_mode = 'ENABLE'
    external_oauth_snowflake_user_mapping_attribute = 'EMAIL_ADDRESS';

5. Configuring in Tellius

In Tellius, navigate to Data --> Connect --> Snowflake --> OAuth.

Under Authentication type, choose Azure AD from the dropdown.

Snowflake URL - URL of Snowflake account (without "https://")

User - The username or service account, (For example, Snowflake user email)

Client ID - Copy and paste the <OAUTH_CLIENT_ID> from this section

Client secret - Copy and paste the <OAUTH_CLIENT_SECRET> from this section

Authorization URL - Copy and paste the <AZURE_AD_OAUTH_AUTH_ENDPOINT> from this section

Access token URL - Copy and paste the <AZURE_AD_OAUTH_TOKEN_ENDPOINT> from this section

Scope - The permissions being requested, (for example"user.read" or a custom scope related to Snowflake

Role - Enter the role to be used for accessing Snowflake

Datasource Name - Specify the name of the datasource

Configuring in Tellius