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. Note this will be different if you're using SSO, or an EU account, this is only an example -

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

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

📘

Dynamic Example in App

You can also find sample SQL in the app to make creating your OAuth integration easier.

*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 → Environments or Build Settings →Development Workspaces . Select edit on the Environment or Workspace that you wish to configure.

📘

OAuth is Configured per Environment/Workspace

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

Inside Edit Environment/Workspace → User Credentials, select Snowflake OAuth, enter your role and Snowflake compute warehouse, then Save.

Now obtain 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;

Now go to OAuth Settings and populate your CLIENT_ID and CLIENT_SECRET into the respective fields and click Save.

Now return to User Credentials to authenticate your integration.

Login with Snowflake OAuth

After OAuth has been configured for the desired Coalesce Environment/Workspace, navigate to Build Settings Environments/Development Workspaces. Select edit on the environment/workspace that you wish to connect to Snowflake using OAuth.

Inside Edit Environment/Workspace 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, and the credential can be stored either in your local browser's storage (not recommended) or in Coalesce's cloud secrets vault.

Login with Snowflake Basic Auth

Navigate to Build Settings Environments/Development Workspaces. Select Edit on the environment/workspace that you wish to connect to Snowflake using Basic Auth.

Inside Edit Environment/Workspace User Credentials, select Authentication Type as Username and Password (Cloud) or Username and Password (Browser Storage). Enter Username and Password into their respective fields and Save.