Skip to main content

Configuring Snowflake OAuth

To allow for Single Sign-On (SSO) and protect your Snowflake data, Waii can request permission to access Snowflake through an OAuth provider. Instead of sharing your password with Waii to configure a connection, the sign-on process is managed by the OAuth provider when a database connection is made. Connections are kept alive through refresh tokens, and authentication is requested as these tokens near expiry.

Waii supports one OAuth configuration file from any of the supported OAuth providers per Snowflake account. Supported OAuth providers:

  • Snowflake
  • Okta

Using Snowflake as the OAuth Provider​

These instructions must be carried out by a Snowflake account admin. The Snowflake instructions are adapted from Snowflake's documentation on custom OAuth apps. OAuth configuration is done for one Snowflake account at a time.

Step 1: Determine Redirect URI​

First, determine the OAuth redirect URI. This value is the location from which you access the Waii UI with port 9859 and the path oauth2/callback. For example, if accessing Waii on localhost (single-node deployment), this will be: http://localhost:9859/oauth2/callback. This value will be referred to as <redirect_uri> from now on.

Step 2: Set Up Snowflake Security Integration​

This Snowflake command creates an OAuth security integration. With this, Waii can use endpoints to request authentication and access tokens. Here, we set the refresh token validity to its maximum lifetime, 90 days, for a seamless experience.

CREATE SECURITY INTEGRATION WAII
    TYPE = OAUTH
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE = 'PUBLIC'
    OAUTH_REDIRECT_URI = '<redirect_uri>'
    ENABLED = TRUE
    OAUTH_ISSUE_REFRESH_TOKENS = TRUE
    OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
    OAUTH_USE_SECONDARY_ROLES = NONE
    OAUTH_ALLOW_NON_TLS_REDIRECT_URI = TRUE;

Step 3: Retrieve Client ID and Secret from Snowflake​

Waii needs a client ID and secret value to authenticate through Snowflake. These can be retrieved from the Snowflake UI with the command:

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('WAII');

The return of this function should look like:

{
    "OAUTH_CLIENT_SECRET_2": "<client secret 2>",
    "OAUTH_CLIENT_SECRET": "<client secret>",
    "OAUTH_CLIENT_ID": "<client id>"
}

Waii will need the <client id> and <client secret>.

Step 4: Create an OAuth Configuration File​

To pass these values to Waii, you must load them into a YAML file that is given to Waii upon startup. The YAML should follow this format, with no quotes:

<snowflake account identifier>:
    oauth_provider: snowflake
    redirect_uri: <redirect uri>
    client_id: <client id>
    client_secret: <client secret>

The Snowflake account identifier is the one for which the security integration was created.

Step 5: Start Up Waii Pointing to This File​

Attach the file path to the argument --oauth_config_file_path while starting Waii:

--oauth_config_file_path oauth_config.yaml

If using Saas, contact Waii to finish the configuration.

Step 6: Add Database Connections Within the Account​

To use the OAuth configuration, add a database connection within the Snowflake account, leaving the password field blank. Upon hitting submit, the user will be redirected to Snowflake to authenticate access for the role. Completing this authentication will issue Waii access and refresh tokens. These will be used by Waii to access Snowflake data behind the scenes. Tokens are issued for a Snowflake account and role. If a future connection uses the same role, then authentication is skipped. Tokens are also saved by user, so one Waii user will not be able to authenticate for another. At this point, Waii will work as normal. As the refresh token nears expiry, the user will be prompted to re-authenticate.