Connections

Connecting via 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.

❗️

Permissions Requirement

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

Create a security integration

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.

CREATE OR REPLACE SECURITY INTEGRATION "COALESCE_OAUTH"
    ENABLED = TRUE
    TYPE = OAUTH
    OAUTH_CLIENT = CUSTOM
    OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
    OAUTH_REDIRECT_URI = 'https://app.coalescesoftware.io/oauthredirect'
    OAUTH_ISSUE_REFRESH_TOKENS = TRUE
    OAUTH_REFRESH_TOKEN_VALIDITY = 7776000

❗️

If Using EU Accounts

If you're using an EU account - your URI will be https://app.eu.coalescesoftware.io/ - you'll need to make a separate Snowflake security integration, as the OAUTH_REDIRECT_URI will be different between US and EU accounts.

Parameter

Description

ENABLED*

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*

Coalesce requires this field to be set to OAUTH which uses Snowflake OAuth rather than External OAuth.

OAUTH_CLIENT*

Coalesce requires this field to be set toCUSTOM.

OAUTH_CLIENT_TYPE*

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*

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_TOKENS

Boolean 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_VALIDITY

Integer 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)

*Required. Additional configuration options can be found in Snowflake's Create Security Integration documentation.

Configure Snowflake OAuth

As a Coalesce Organization Admin, navigate to Build Settings Storage & Environments. Select edit on the Environment that you wish to configure.

📘

OAuth is Configured per Environment

By design, this allows for the flexibility of different Snowflake accounts per environment. 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.

Inside Edit Environment Settings, toggle on Enable OAuth and enter the Client ID and Client Secret which was generated by creating a Snowflake security integration. To retrieve these values, execute the following SQL query 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;

Enter CLIENT_ID and CLIENT_SECRET into the respective field and click Save.

Edit Environment Settings InterfaceEdit Environment Settings Interface

Edit Environment Settings Interface

Login with Snowflake OAuth

After OAuth has been configured for the desired Coalesce Environment, navigate to Build Settings Storage & Environments. Select edit on the environment that you wish to connect to Snowflake using OAuth.

Inside Edit Environment User Credentials, select Authentication Type as Snowflake OAuth. Enter desired Role then select Authenticate and follow the directions in the Snowflake OAuth popup. Once successfully authenticated, you may input a desired Warehouse, Test Connection, and Save.

❗️

ACCOUNTADMIN Role and OAuth

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

📘

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 via 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 documentation here.

🚧

The OAuth Toggle

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

You can find an example query below.

alter integration COALESCE_OAUTH set enabled = false

Removing Snowflake OAuth

To remove the integration you can drop it using the following command. More details in Snowflake's documentation here. Note that you will need to delete the configuration settings on the Coalesce side afterwards.

drop integration if exists COALESCE_OAUTH;

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.

Connecting via Username and Password


Basic Auth is the fastest way to connect your target database platform to Coalesce, however, this is not the preferred method as your Snowflake credentials are stored in your browser's local storage. Consider enabling Snowflake OAuth if possible for improved security.

Login with Snowflake Basic Auth

Navigate to Build Settings Storage & Environments. Select Edit on the environment that you wish to connect to Snowflake using Basic Auth.

Inside Edit Environment User Credentials, select Authentication Type as Username and Password. Enter Username and Password into their respective fields and Save.


Did this page help you?