Configure Azure AD external OAuth for a Snowflake connection

ThoughtSpot supports Microsoft Azure Active Directory (AD) OAuth for a Snowflake connection. With Azure AD OAuth, the authorization server generates an access token from Azure AD on behalf of the ThoughtSpot user which authenticates them with Snowflake and authorizes ThoughtSpot to query the database using their Snowflake user account.

The steps provided here are an example of how you can configure Azure AD OAuth for external OAuth. You can use any OAuth flow provided that you can get the information required for the security integration. The following steps provide a guide for getting the information required to create the security integration for Snowflake.
Be sure to consult your organization’s security policies for configuring an authorization server to make sure you meet all requirements.

Creating an external OAuth token using Azure AD on behalf of the user

Part 1: Creating a Snowflake OAuth resource

To create a Snowflake OAuth resource, do the following:

  1. Sign in to the Microsoft Azure Portal.

  2. Navigate to Azure Active Directory.

    Under Azure services

  3. Select App registrations, then New registration.

    Select App registrations > New registration

  4. For Name, enter a name for your application.

    Example:Snowflake Oauth User Resource

  5. Make sure Supported account types is set to Single Tenant.

  6. Select Register.

    Click Register

  7. After the application is created, select Expose an API.

    Click Expose an API

  8. Next to Application ID URI, select Set and change its value from api://<alphanumeric value> to https://<alphanumeric value> and select Save.

    Update the App ID URI
    If the Application ID URI is not used, you must create a security integration with audiences using the Snowflake Account URL (that is, <account_identifier>.snowflakecomputing.com).
    The Application ID URI must be unique within your organization’s directory, such as https://your.company.com/4d2a8c2b-a5f4-4b86-93ca-ack45667.

  9. Select Add a scope.

  10. For Scope name, enter the name of the Snowflake role (example: session:role-any).

  11. Select who can consent.

  12. Enter an Admin consent display name (example: Any).

  13. Enter an Admin consent description.

  14. Select Add Scope.

    Add a scope modal image
When you create the scope in your Azure AD application, you must set it to “any” so that a user can later switch to any of their allowed roles when making a JDBC connection using the obtained token.

Part 2: Creating a Snowflake OAuth client app

To create a Snowflake OAuth client app, do the following:

  1. Sign in to the Microsoft Azure Portal, and navigate to Azure Active Directory, if needed.

  2. Select App registrations, and then select New registration.

  3. For Name, enter a name for the client (example: Snowflake OAuth User).

  4. For Supported account types, make sure it is set to Single tenant.

  5. Select Register.

  6. Once the app is created, select Overview.

  7. From the Application (client) ID field, copy the ID. This ID is referred to as the <OAUTH_CLIENT_ID> in the steps that follow.

    Copy the Application (client) ID

  8. Select Authentication, and under Web, specify the redirect URI using the following format:

    <https://<public url of your ThoughtSpot Instance>/callosum/v1/connection/generateTokens

    Add a redirect URI

  9. Select Certificates & secrets and then New client secret.

    Create a client secret

  10. Copy the Value of the secret you just created. This is referred to as <OAUTH_CLIENT_SECRET> in the steps that follow.

    Copy the value of the secret

  11. Select API permissions, then click Add a permission.

    Add an API permission

  12. Select My APIs.

  13. Select the name of the Snowflake OAuth Resource you created in Part 1.

    Click on the OAuth Resource

  14. On the Request API permissions page, click the Delegated permissions box, and select the permission related to scope you defined in the application you want to grant to this client.

  15. Select Add permissions.

    Add permissions

  16. Under Configured permissions, select Grant admin consent for Default Directory, and then click Yes in the confirmation message.

    Grant admin consent

Part 3: Collecting Azure AD information for Snowflake

To collect Azure AD information for Snowflake, do the following:

  1. Sign in to the Microsoft Azure Portal, and navigate to Azure Active Directory, if needed.

  2. Go back to the Snowflake OAuth Resource App (Snowflake Oauth User Resource) to collect the following information:

    1. Select Endpoints in the Overview interface.

    2. On the right side, copy the OAuth 2.0 token endpoint (v2) and note the URLs for OpenID Connect metadata and Federation Connect metadata.

      1. The OAuth 2.0 token endpoint (v2) is referred to as the <AZURE_AD_OAUTH_TOKEN_ENDPOINT> in the following configuration steps. The endpoint should be similar to https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token/.

      2. For the OpenID Connect metadata, open in a new browser window.

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

        2. This parameter value will be known as the <AZURE_AD_JWS_KEY_ENDPOINT> in the following configuration steps. The endpoint should be similar to https://login.microsoftonline.com/<tenant_id>/discovery/v2.0/keys.

  3. For the Federation metadata document, open the URL in a new browser window.

    1. Locate the "entityID" parameter in the XML Root Element and copy its value.

    2. This parameter value will be known as the <AZURE_AD_ISSUER> in the following configuration steps. The entityID value should be similar to https://sts.windows.net/<tenant_id>/.

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

    View information in Overview > Endpoints

    View the OAuth 2.0 authorization endpoint (v2)

Part 4: Creating an OAuth authorization server in Snowflake

In this part you must do the following:

  • Create a security integration in Snowflake to ensure that Snowflake can securely communicate with Microsoft Azure AD.

  • Validate the tokens from Azure AD.

  • Provide the appropriate Snowflake data access to users based on the user role associated with the OAuth token.

If you use SESSION:ROLE-ANY in scope, you must configure the following flag in the security integration: external_oauth_any_role_mode = ‘ENABLE’. This is shown in the optional line of the security integration format example.

Security integration format

create security integration external_oauth_azure_2
    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' (optional)
    external_oauth_snowflake_user_mapping_attribute = 'login_name';

Example:

Security integration format example
When you create the Snowflake OAuth Resource Application in Azure AD, if you enter an Application ID URI that is not the Snowflake Account URL (that is, <account_identifier>.snowflakecomputing.com), you must add the external_oauth_audience_list parameter to the command with the value <SNOWFLAKE_APPLICATION_ID_URI>.

Snowflake commands

Create user as Azure AD user

CREATE USER testuser PASSWORD = '' LOGIN_NAME = 'testuser@thoughtspot.com' DISPLAY_NAME = 'AD_TEST_USER';

Validate access token

select system$verify_external_oauth_token('<ACCESS_TOKEN>');

Grant sysadmin role to TESTUSER

GRANT ROLE sysadmin TO USER TESTUSER;

Altering user

ALTER USER testuser SET DEFAULT_ROLE = SYSADMIN;

(Optional) Validating your Azure configuration

To ensure your Azure configuration is correct for use with ThoughtSpot, you can generate an access token.

This following example is for Azure. The process for other providers is similar.

You can use either of the following methods to generate your access token:

  • Postman

  • cURL

Method 1: Postman

To validate your configuration using Postman, do the following:

  1. Sign in to Postman.

  2. Go to the Authorization tab.

  3. For Token Name, enter a token name.

  4. For Grant Type, select Authorization Code from the menu.

  5. For Callback URL, select Authorize using browser.

    This should be defined in your OAuth User app(Ex: Snowflake OAuthUser). The default is https://oauth.pstmn.io/v1/callback.

  6. For Auth URL, enter the OAuth 2.0 authorization endpoint (v2) value from “Endpoints” in the app.

    Example: https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/authorize

  7. For Access Token URL, enter the access token URL.

    Example: https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token/

  8. For Scope, you must provide “offline_access” as the scope, along with the actual scope. The refresh token is only provided if the offline_access scope was requested.

    Configure a new token

    Example: https://<application_id>/session:role-any offline_access

    When you create the scope in the Azure AD application setup, it must be set as “any” so that a user can later switch to any of his allowed roles when making a JDBC connection using the obtained token.

  9. Select Get New Access Token.

  10. Sign in to your Microsoft Azure account.

    Sign in to Microsoft Azure

  11. On the Token Details page, select Use Token.

    Use token

    Use token more detail

  12. Verify the validity of the generated access token by running this SQL in snowflake:

    select system$verify_external_oauth_token('<access_token>');

Method 2: cURL

To validate your configuration using cURL, do the following:

  1. Execute this command to get access token with password grant_type:

     curl -X POST -H "Content-Type: application/x-www-form-urlencoded;charset=UTF-8" \
   --data-urlencode "client_id=<OAUTH_CLIENT_ID>" \
   --data-urlencode "client_secret=<OAUTH_CLIENT_SECRET>" \
   --data-urlencode "username=<AZURE_AD_USER>" \
   --data-urlencode "password=<AZURE_AD_USER_PASSWORD>" \
   --data-urlencode "grant_type=password" \
   --data-urlencode "scope=<SCOPE_AS_IT_APPEARS_IN_AZURE_APP>" \
   '<AZURE_AD_OAUTH_TOKEN_ENDPOINT>'

    Example:

     curl -X POST -H "Content-Type: application/x-www-form-urlencoded;charset=UTF-8" \
 --data-urlencode "client_id=<client_id>" \
 --data-urlencode "client_secret=<client_secret>" \
 --data-urlencode "username=testuser@thoughtspot.com" \
 --data-urlencode "password=*****" \
 --data-urlencode "grant_type=password" \
 --data-urlencode "scope=https://<application_id>/session:role-any offline_access"\
 `https://login.microsoftonline.com/ <tenant_id>/oauth2/v2.0/token'
    Image of curl command in terminal

  2. Execute the following command for getting access token with refresh_token as grant_type:

     curl -X POST -H "Content-Type: application/x-www-form-urlencoded;charset=UTF-8" \
   --data-urlencode "client_id=<client_id>" \
   --data-urlencode "client_secret=<client_secret>" \
     --data-urlencode "grant_type=refresh_token" \
     --data-urlencode "refresh_token=<Replace_Refresh_Token>" \
     --data-urlencode "scope=https://<application_id>/session:role-any offline_access" \
   'https://login.microsoftonline.com/<tenant_id>/oauth2/v2.0/token'
    Image of additional curl command in terminal

  3. Verify the validity of the generated access token by running this SQL in snowflake:

    select system$verify_external_oauth_token('<access_token>');

Logging in to a connection created by another user using OAuth

As an admin user, you may run into an issue logging in to connections created using OAuth. To resolve this issue, complete the following steps:

  1. Search on a table belonging to the connection you are trying to edit. The following error appears:

    Error reading “Error in loading data. Connection to Snowflake could not be established. OAuth login required. Login”

  2. Click Login. You will be directed to the IDP login page.

  3. Enter your login credentials.

  4. You will now have access to edit the connection.

OAuth connection improvements

If you do not have a valid OAuth access token, you can now directly navigate to the OAuth authorization screen when performing one of the following actions on a connection shared with you:

  • View sample data

  • Create a custom SQL view

  • Edit the connection

  • Upload a CSV file

Related information

Was this page helpful?
×