Configuring OAuth connections to Snowflake

As an admin, you can enable your CARTO organization to connect to Snowflake using OAuth, instead of requiring username and password. Once this integration is enabled, the option will be available for all users whenever they try to create a new Snowflake connection.

Although it requires an initial setup, connecting CARTO and Snowflake via OAuth is the recommended approach. It has multiple benefits:

• Security and traceability is improved since OAuth consent can be revoked easily, programmatically, and it can be set up to expire. This can be centralized in your organization using External OAuth.

• Connections with username and password are disabled by policy in some Snowflake accounts.

• Users can leverage their multi-factor authentication (MFA) to connect CARTO and Snowflake.

Additionally, it will lead to performance improvements in the future as newer versions of Snowflake APIs do not support username/password-based authentication.

To enable this for all users, navigate to Settings > Advanced Settings > Integrations, where you'll find an integration to start this process. Click on "Add" to configure the integration.

Choosing an OAuth type

Snowflake offers two different types of OAuth-based authentication:

• Snowflake OAuth: connect CARTO and Snowflake using your Snowflake credentials. Follow this guide to configure Snowflake OAuth in CARTO.

• External OAuth: connect CARTO and Snowflake using your company credentials from Okta, Azure Active Directory or a different identity provider, that is also being used in Snowflake. Follow this guide to connect CARTO and Snowflake using External OAuth.

If you are not sure about which OAuth to configure or have any additional questions, please get in touch with our team, with your Snowflake administrators, or read the Snowflake's introduction to OAuth guide in their documentation.

Configuring Snowflake OAuth

Requirements

To setup this integration you need to be able to run queries with the ACCOUNTADMIN role in Snowflake. Ask an Snowflake admin in your organization to help if you are not one.

Overview

The high-level process to allow users to connect CARTO and Snowflake using their Snowflake credentials is to create a SECURITY INTEGRATION in Snowflake for a custom OAuth client. Then, pass the details of this integration to CARTO.

For the exact commands and steps in the Snowflake console, please refer to the Configure Snowflake OAuth for Custom Clients guide in the Snowflake documentation.

Step 1: Create an OAuth security integration in Snowflake

First we need to create the integration in your Snowflake console. To do this, copy and paste the provided SQL code and execute it in your Snowflake console. It will run the CREATE SECURITY INTEGRATION SQL command with all the necessary information already included.

Step 2: Get the necessary information from Snowflake and finish the setup in CARTO

Once the security integration has been created in Snowflake, you will need to fill the following fields in the CARTO integration panel:

• Snowflake Account Name: this is your Snowflake account name, in the following format: <account_name>.snowflakecomputing.com.

• Auth URL: The URL found under OAUTH_ALLOWED_AUTHORIZATION_ENDPOINTS in your newly created security integration in Snowflake. You can use the DESCRIBE INTEGRATION command to obtain it.

• Access Tokens URL: The URL found under OAUTH_ALLOWED_TOKEN_ENDPOINTS in your newly created security integration in Snowflake. You can use the DESCRIBE INTEGRATION command to obtain it.

• OAuth Client ID: The ID found under OAUTH_CLIENT_ID in your newly created security integration in Snowflake. Use the SYSTEM$SHOW_OAUTH_CLIENT_SECRETS with your integration name to obtain it.

• OAuth Client Secret: The secret found under OAUTH_CLIENT_SECRET in your newly created security integration in Snowflake. Use the SYSTEM$SHOW_OAUTH_CLIENT_SECRETS with your integration name to obtain it. Alternatively you can use the OAUTH_CLIENT_SECRET_2 in case you're rotating the secret.

Validating your setup

Once you've filled all this information, click on "Save". The integration will be saved and you will be back at the Integrations list. To validate your setup, try to create a Snowflake OAuth connection as described in our Snowflake connection guide.

External OAuth

Requirements

• You will need to be able to run queries with the ACCOUNTADMIN role in Snowflake to setup this integration. Ask an Snowflake admin in your organization to help if you are not one.

• You will also need to be able to create resources in your company's directory (the identity provider, for example Azure AD or Okta). Ask an IT admin in your organization to help if you don't have the needed permissions.

Overview

The high-level process to allow users to connect CARTO and Snowflake using an External OAuth server is to first create the necessary resources in your Identity Provider, and then use them to create a SECURITY INTEGRATION in Snowflake with type = external_oauth. Then, pass the same OAuth resources to CARTO.

Step 1: Choosing an Authorization server/Identity Provider (IdP)

Steps 1 and 2 assume that your Snowflake environment does not have anything configured relating to Okta OAuth authorization servers, OAuth clients, scopes, and necessary metadata — Or that you want to set up a brand new configuration for CARTO.

If you want CARTO to reuse an existing configuration, skip to Step 3.

CARTO and Snowflake support any valid OAuth authorization server as an identity Provider. You can find more details in the External OAuth section of the Snowflake documentation.

We have created unique flows for Azure Active Directory, Okta, and a Custom flow for any other identity provider.

Step 2: Connecting your Identity Provider and Snowflake

Snowflake has created different guides for each of the possible Identity Providers:

• Azure AD: Follow the steps in Configure Microsoft Azure AD for External OAuth.

• Okta: Follow the steps in Configure Okta for External OAuth.

• Custom: Follow the guide in Configure Custom Authorization Servers for External OAuth.

For Azure AD integrations

Please use api://snowflake-carto as your Application ID URI, as seen in the screenshot below.

For Azure AD integrations

Tips and common pitfalls

When executing the CREATE SECURITY INTEGRATION in Snowflake:

• We recommend enabling the ANY role mode as part of your setup. Read more about using ANY role with External OAuth in the Snowflake documentation.

external_oauth_any_role_mode = 'ENABLE'

• Make sure you use the right claim for your Identity Provider

external_oauth_token_user_mapping_claim = 'upn'  // Azure AD
external_oauth_token_user_mapping_claim = 'sub   // Okta

Additionally, please make sure that the LOGIN_NAME parameter in Snowflake matches the Identity Provider user that will be passed to CARTO later.

Make sure that in all cases you create an OAuth Client for this integration. You will later need to pass the Client ID and Client Secret to CARTO so that it can initiate the OAuth flow for the user.

Once you finish these steps you should have created a SECURITY INTEGRATION in Snowflake and your IdP will be connected to Snowflake using OAuth. Now we just need to pass the IdP information to CARTO to leverage this integration.

Step 3: Get the necessary information from the IdP and finish the setup in CARTO

Once the security integration has been created in Snowflake, you will need to fill the following fields in the CARTO integration panel:

• Snowflake Account Name: this is your Snowflake account name, in the following format: <account_name>.snowflakecomputing.com.

• Auth URL: The Authorization URL from your IdP that was used as external_oauth_issuer in your Snowflake security integration.

• Access Tokens URL: The URL from your IdP that was used as external_oauth_jws_keys_url in your Snowflake security integration. If that field wasn't used (for example, if you specified a public key), this needs to be an URL where you can download public keys or certificates to validate an External OAuth access token.

• OAuth Client ID: The ID for the OAuth client that was set up in your Authorization Server.

• OAuth Client Secret: The secret for the OAuth client that was set up in your Authorization Server.

Validating your setup

Once you've filled all this information, click on "Save". The integration will be saved and you will be back at the Integrations list. To validate your setup, try to create a Snowflake OAuth connection as described in our Snowflake connection guide.