Configure internal OAuth or OAuth with PKCE for a Snowflake connection

ThoughtSpot supports Snowflake’s internal OAuth for a Snowflake connection. With OAuth, each ThoughtSpot user authenticates with Snowflake and authorizes ThoughtSpot to query the database using their Snowflake user account.

For Snowflake connections that use internal OAuth, users must sign in when their OAuth tokens expire. The amount of time that Snowflake OAuth tokens are valid is set in Snowflake.

For OAuth, we recommend checking the Snowflake documentation to confirm any IDP support and their details. This article documents only the most frequently set-up IDP.
If your IdP supports rotation of refresh tokens with every usage, please ensure that this option is NOT chosen. ThoughtSpot does not support one-time use refresh tokens.

Configuring a Snowflake database for internal OAuth with ThoughtSpot

To add a Snowflake connection to ThoughtSpot using internal OAuth, you must set up the OAuth integration in Snowflake. This requires a Snowflake user account with ACCOUNTADMIN permission.

Each ThoughtSpot instance requires a unique Snowflake security integration. Each user in Snowflake must have a default warehouse and default role.

In your Snowflake database, do the following:

  1. In the Worksheet view, enter the following commands, and select Run:

    SHOW USERS;

SHOW SECURITY INTEGRATIONS;

CREATE OR REPLACE SECURITY INTEGRATION <enter a name for your security role>
  TYPE = OAUTH
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = <enter a client type>
  OAUTH_REDIRECT_URI = 'https://<public url of your ThoughtSpot instance>/callosum/v1/connection/generateTokens'
  OAUTH_USE_SECONDARY_ROLES = IMPLICIT
  ENABLED = TRUE
  COMMENT = '<enter a description of your security profile>'

  2. At the bottom of what you entered in step 1, add an empty line, and then enter the following to describe your security integration:

    DESCRIBE SECURITY INTEGRATION <enter description of your security integration>;

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('<enter same description of your security integration as previous line');

    Example of a full query:

    SHOW USERS;

SHOW SECURITY INTEGRATIONS;

CREATE OR REPLACE SECURITY INTEGRATION OAUTH_CONFIG
  TYPE = OAUTH
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://177.122.45.2/callosum/v1/connection/generateTokens'
  OAUTH_USE_SECONDARY_ROLES = IMPLICIT
  ENABLED = TRUE
  COMMENT = 'Profile for passthrough'

DESCRIBE SECURITY INTEGRATION OAUTH_CONFIG;

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('OAUTH_CONFIG');

  3. Select the Run button to run the full query.

    The Details window appears displaying a JSON object.

  4. Copy the JSON object and select Done to close the window.

  5. Paste the contents into a text editor.

    Example:

    {"OAUTH_CLIENT_SECRET_2":"KqKBu0xOxPtmk+RKvNP0+eIAMlFxMsu8rRh6s5q1qLY",
"OAUTH_CLIENT_SECRET":"KdKBb0aOxPzml+RJvMP1/eIEMlFxM/su6rPh2wLZ",
"OAUTH_CLIENT_ID":"aOxPzmlRJvCP5eIUMlFxMbu6rJh7mTO="}

  6. For OAUTH_CLIENT_ID, copy the information between quotes after the colon (:).

    Example: aOxPzmlRJvCP5eIUMlFxMbu6rJh7mTO=

    Make sure you include the equals sign (=), if it exists.

  7. In the Snowflake connection details page in ThoughtSpot, paste the OAuth client ID in the OAuth Client ID field.

  8. For OAUTH_CLIENT_SECRET, copy the information between quotes after the colon (:).

    Example: KdKBb0aOxPzml+RJvMP1/eIEMlFxM/su6rPh2wLZ

  9. In the Snowflake connection details page in ThoughtSpot, paste the OAuth client secret in the OAuth Client Secret field.

    Once a connection is created the token associated with the user login credentials for the connection is stored in ThoughtSpot for 90 days.

Sharing a Worksheet built from tables in a Snowflake connection that uses OAuth

When you share a ThoughtSpot object (Worksheet, Liveboard, or Answer) that references a Snowflake connection that uses OAuth authentication, the user is prompted to sign in to Snowflake before they can view any data. When viewing the Liveboard, Answer, or running a search on the Worksheet, ThoughtSpot displays a message telling the user to sign in with a button to initiate the login process.

In order for a user to access your shared Worksheet, they must have a default role assigned to their user in Snowflake.

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?
×