Skip to main content

Snowflake OAuth

Coalesce supports OAuth authentication with Snowflake. When Snowflake OAuth is enabled, users can authorize their Development credentials using Single Sign-On (SSO) via Snowflake rather than submitting a username and password to Coalesce directly.

OAuth is configured per Environment and Workspace, this allows for the flexibility of different Snowflake accounts per environment and workspace. If your organization uses the same Snowflake account and OAuth configuration for multiple environments, you will need to manually copy your configuration to each environment respectively. Workspaces can be easily duplicated including their settings.

Permissions Requirement

Only a Snowflake ACCOUNTADMIN role or a role with the global CREATE INTEGRATION privilege can create security integrations.

Snowflake OAuth New Workspace​

If you want to create a new Workspace, follow these instructions.

  1. Select the Project you want to create the Workspace in.

  2. Click Create Workspace. You'll be taken to the Workspace creator.

  3. Give your Workspace a Name and Description(optional).

    Step 1 of 5 workspace creation interface for Coalesce, showing fields for workspace name and description, with explanation that workspaces allow development of data transformations without impacting team members

  4. Enter your Snowflake Account URL.

    Snowflake connection setup page showing URL input field with https:// prefix and .snowflakecomputing.com suffix, OAuth toggle currently disabled, and Previous/Next navigation buttons. Link provided for finding Snowflake Account URL.

    1. Obtain your Snowflake URL, by opening the account selector in Snowflake.

      Add your Snowflake credentials in the build settings

  5. Click the toggle under Snowflake OAuth to enable it for your Workspace. This will open a new window where you'll need to enter your Client ID and Secret from Snowflake.

    Configure Snowflake OAuth

    1. In Snowflake, create a new Worksheet.

    2. Run the first part of the code, Create Security Integration. Replace <COALESCE_APP_DOMAIN> with your Coalesce app domain. For example, app.coalescesoftware.io. Make sure it runs successfully. Then run the second part of the code to get the OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET.

      Create Security Integration
      -- Create Security Integration 
      CREATE OR REPLACE SECURITY INTEGRATION "COALESCE_OAUTH" 
      ENABLED = TRUE 
      TYPE = OAUTH 
      OAUTH_CLIENT = CUSTOM 
      OAUTH_CLIENT_TYPE = 'CONFIDENTIAL' 
      OAUTH_REDIRECT_URI = 'https://<COALESCE_APP_DOMAIN>/oauthredirect' 
      OAUTH_ISSUE_REFRESH_TOKENS = TRUE 
      OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;

  6. Copy the CLIENT_ID and CLIENT_SECRET that are returned.

  7. Enter them into Coalesce. Click Next. If needed, enter the Role and Warehouse. Click Authenticate.

  8. You'll be asked to sign into Snowflake and give Coalesce access. On success, you'll be taken back to the connection screen and Snowflake OAuth will be enabled. Click Next.

    Step 2 of 5 Coalesce workspace setup showing Snowflake connection configuration. Features URL input field with example domain 'snowflakecomputing.com', OAuth toggle enabled with success message, and Advanced Options dropdown. Navigation includes Previous and Next buttons.

  9. You'll connect a repo to this Workspace. Your repository was connected during Project setup. Workspaces let you work on a branch. You will need to select a branch and commit to make a new branch. For example, if you want to create a branch off main, select main, then select the commit in main to create your branch from.

  10. Next, add Storage Locations and Storage Mappings to your Workspace. If you have any existing Storage Locations and Storage Mappings from Git, they will be listed here.

    Storage Locations and Mappings configuration interface showing two locations (WORK and EDW), with EDW expanded to display database mapping to EDW_APAC and schema mapping to DEV_EDW. Interface includes toggle for manual mapping and option to set WORK as default storage location.

  11. Click Create Workspace. You've just created your Workspace. Get started by adding some data.

If you need the Client ID and Client Secret again, you can run the following in Snowflake.

WITH INTEGRATION_SECRETS AS (
  SELECT parse_json(system$show_oauth_client_secrets('COALESCE_OAUTH')) AS SECRETS
) 
SELECT
  SECRETS:"OAUTH_CLIENT_ID"::STRING     AS CLIENT_ID,
  SECRETS:"OAUTH_CLIENT_SECRET"::STRING AS CLIENT_SECRET
FROM 
  INTEGRATION_SECRETS;

Snowflake OAuth Existing Workspace​

If you already have a Workspace created and want to add OAuth, follow these instructions.

Step 1: Add Your Snowflake Account Identifier​

  1. Edit your Workspace by selecting the cog icon, , next to the Workspace name.

  2. On the Settings page, enter your Snowflake Account.

    1. Obtain your Snowflake URL, by opening the account selector in Snowflake.
    Snowflake account name

Step 2: Create a Security Integration in Snowflake​

In Snowflake, execute a query to create a security integration. Please see the complete documentation for this in Snowflake's Create Security Integration documentation. You can find a sample CREATE OR REPLACE security integration query below. This will be different if you're using SSO, or an EU account. You'll need the Client ID and Client Secret.

Replace <COALESCE_APP_DOMAIN> with your Coalesce app domain. For example, app.coalescesoftware.io.

Run the first part of the code, Create Security Integration. Update the OAUTH_REDIRECT_URI to your app URL. Make sure it runs successfully. Then run the second part of the code to get the OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET.

Create Security Integration
-- Create Security Integration 
CREATE OR REPLACE SECURITY INTEGRATION "COALESCE_OAUTH" 
ENABLED = TRUE 
TYPE = OAUTH 
OAUTH_CLIENT = CUSTOM 
OAUTH_CLIENT_TYPE = 'CONFIDENTIAL' 
OAUTH_REDIRECT_URI = 'https://<COALESCE_APP_DOMAIN>/oauthredirect' 
OAUTH_ISSUE_REFRESH_TOKENS = TRUE 
OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;

Step 3: Enter the Client ID and Client Secret​

As a Coalesce Organization Admin:

  1. Edit your Workspace or Environment by selecting the cog icon, , next to the Workspace or Environment name.
  2. Go to OAuth Settings, click Enable OAuth, and click Edit.
  3. Enter the Client ID and Client Secret. Click Save and then Save again.
Enter the Client ID and Client Secret from the Snowflake security integration

If you need to get the values again, you can use the following:

WITH INTEGRATION_SECRETS AS (
  SELECT parse_json(system$show_oauth_client_secrets('COALESCE_OAUTH')) AS SECRETS
) 
SELECT
  SECRETS:"OAUTH_CLIENT_ID"::STRING     AS CLIENT_ID,
  SECRETS:"OAUTH_CLIENT_SECRET"::STRING AS CLIENT_SECRET
FROM 
  INTEGRATION_SECRETS;

Step 4: Login with Snowflake OAuth​

  1. Go to the Authentication Type, set Authentication Type to Snowflake OAuth.
  2. Enter desired Role then select Authenticate and follow the directions in the Snowflake OAuth popup.
  3. Once successfully authenticated, you may input a desired Warehouse, Test Connection, and Save.
Example OAuth Setup in User Credentials

After OAuth has been configured for the desired Coalesce Environment or Workspace, you need to log in using Snowflake OAuth.

ACCOUNTADMIN Role and OAuth

Snowflake does not allow login using OAuth while specifying a role of ACCOUNTADMIN for security reasons.

Security Integration Parameters​

For more configuration options, see Snowflake's Create Security Integration documentation.

ParameterDescription
ENABLED (Required)Coalesce requires this field to be set to TRUE to enable Coalesce's Snowflake OAuth support. Enabled specifies whether to initiate operation of the integration or suspend it. Note: Although not required in Snowflake documentation, ENABLED defaults to FALSE.
TYPE (Required)Coalesce requires this field to be set to OAUTH which uses Snowflake OAuth rather than External OAuth.
OAUTH_CLIENT (Required)Coalesce requires this field to be set toCUSTOM.
OAUTH_CLIENT_TYPE (Required)Coalesce requires this field to be set to CONFIDENTIAL. Client type specifies the type of client being registered. Confidential clients can store a secret. They run in a protected area where end users cannot access them.
OAUTH_REDIRECT_URI (Required)Coalesce requires this field to be set to <https://app.coalescesoftware.io/oauthredirect>. Specifies the client URI. After a user is authenticated, the web browser is redirected to this URI.
OAUTH_ISSUE_REFRESH_TOKENSBoolean that specifies whether to allow the client to exchange a refresh token for an access token when the current access token has expired. If set to FALSE, a refresh token is not issued regardless of the integer value set in OAUTH_REFRESH_TOKEN_VALIDITY. User consent is revoked, and the user must confirm authorization again. Default: TRUE
OAUTH_REFRESH_TOKEN_VALIDITYInteger that specifies how long refresh tokens should be valid (in seconds). This can be used to expire the refresh token periodically. Note that OAUTH_ISSUE_REFRESH_TOKENS must be set to TRUE. Use a smaller value to force users to re-authenticate with Snowflake more frequently. Default: 7776000 (90 days)

OAuth and Snowflake Role​

The role will be fixed for each OAuth connection established. If you'd like to change role, you'll need to disconnect, change role, and then reconnect.

SSO OAuth​

Once a user has authorized Coalesce with Snowflake using their identity provider, Snowflake will return a Refresh Token to Coalesce. Coalesce is then able to exchange this refresh token for an Access Token which can then be used to open a Snowflake connection and execute queries in Coalesce on behalf of users.

Disabling Snowflake OAuth​

To disable Snowflake OAuth, set the ENABLED parameter to FALSE. More details in Snowflake's ALTER SECURITY INTEGRATION.

The OAuth Toggle

Disabling OAuth using the Enable OAuth toggle will only prevent new OAuth connections from being created; existing connections will not be affected.

Example Snowflake Query to Disable Integration
alter integration COALESCE_OAUTH set enabled = false

Removing Snowflake OAuth​

To remove the integration you can drop it using the following command, review Snowflake's, Drop Integration. Then you need to delete the configuration settings in Coalesce.

Example Snowflake Query to Remove Integration
drop integration if exists COALESCE_OAUTH;

MFA Accounts​

If you have MFA enabled on your Snowflake account, you should use OAuth authentication.

Troubleshooting​

Invalid consent request: When authenticating with Snowflake, OAuth successfully redirects you to the Snowflake login page, but you receive an Invalid consent request error, your Snowflake user may not have access to the Snowflake role configured when authorizing OAuth. Double-check that you have access to that role and if the role name has been correctly entered in as Snowflake is case-sensitive.