Configuring SSO OAuth for Snowflake in Pyramid

Pyramid supports connecting to Snowflake as a data source using standard authentication (user and password) and Single Sign-On (SSO) via OAuth.

OAuth authentication is compatible with multiple external providers, including Azure, Okta, and Snowflake’s internal OAuth system.

This guide outlines the steps to configure SSO on Snowflake and integrate it with Pyramid.

Prerequisites

Before making changes in Snowflake, first configure Pyramid:

1. Navigate to Admin Console > Data > Global Settings.
2. Set up the Redirect URL.
3. Click Apply.

 

Snowflake Configuration

1. Create a Security Integration:

Execute the following query in Snowflake to create a security integration for OAuth authentication:

CREATE SECURITY INTEGRATION <integration_name>
TYPE = OAUTH
ENABLED = TRUE
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
OAUTH_REDIRECT_URI = '<pyramid_redirect_URL>'
OAUTH_REFRESH_TOKEN_VALIDITY = <token_timeout_in_seconds>
OAUTH_ISSUE_REFRESH_TOKENS = TRUE;

Note: Token timeout values can range from 86400 seconds (1 day) to 7776000 seconds (90 days).

2. Retrieve OAuth Endpoints:

After creating the security integration, retrieve the authorization and token URLs by running the following:

DESC SECURITY INTEGRATION <integration_name>;

Save for the values of 

• OAUTH_AUTHORIZATION_ENDPOINT
• OAUTH_TOKEN_ENDPOINT

3. Obtain Client ID and Client Secret:

Run the following query to retrieve the Client ID and Client Secret:

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('<integration_name>');

Note: The integration name in this query is case-sensitive

The result returns:

{
  "OAUTH_CLIENT_SECRET_2": "<client_secret_2>",
  "OAUTH_CLIENT_SECRET": "<client_secret>",
  "OAUTH_CLIENT_ID": "<client_id>"
}

You can use either OAUTH_CLIENT_SECRET or OAUTH_CLIENT_SECRET_2.

Pyramid Configuration

1. Go to the admin console, click Data, and select Data Source. 
   Add a new data source and choose Snowflake.
2. Give the connection a name, go to the connection tab, and configure the host and the warehouse.
3. Go to the security tab and change the Authentication Method to "Single Sign On (OAuth) - specific user".
4. Add the values from the previous step to the Client ID and Client Secret.
   The scope can be left empty, and the OAuth settings can be changed from global to custom.
5. The JSON Web Keys URI can be left empty as well.
6. In the OAuth token endpoint, enter the OAUTH_TOKEN_ENDPOINT from step 2. 
   The same applies to the OAuth authentication endpoint.
7. Press connect - a pop-up will appear with the Snowflake Consent Screen.
   You will need to log in using the login name of the Snowflake account that created the security integration.
   Then allow the connection:

   

   

8. Enter the Snowflake User Name (not the login name) and test the connection: