Set up the Snowflake Pack

For Enterprise only: Learn how to configure the Snowflake Pack

Updated today

With Pack configurations, Coda organization administrators gain the ability to customize how Packs work at a granular level. In addition to customizations mentioned in this article, Pack configurations allow IT administrators the ability to configure certain Packs that connect to on-premise instances that support OAuth authentication.

This article outlines how to configure the Snowflake Pack, a Pack bundled in our Enterprise plans that allows users the ability to access organizational data in a Snowflake data-warehouse.

Within this article you'll find...


Gather Snowflake account identifier

First, gather and document your Snowflake account identifier and instance URL and note these down.

Account identifier: This identifier should look something like XY12345. If you do not know your identifier, you can run the query SELECT CURRENT_ACCOUNT() in Snowflake.

Cloud region ID: This region helps you identify your instance URL if you don't know it already. Running the query SELECT CURRENT_REGION() in Snowflake will give you your Snowflake region ID (for example, aws_us_west_2).

You can then use the Region IDs table to find the Cloud region ID that will be a part of your instance URL (for aws_us_west_2, this maps to us-west-2).

Instance URL: This URL should look something like https://{ACCOUNT_IDENTIFIER}.{CLOUD_REGION_ID}.snowflakecomputing.com, with some variation depending upon how your Snowflake instance was configured.

For more information, see the following Snowflake documentation: Account Identifiers | Snowflake Documentation

Add OAuth Security Integration to Snowflake

This step creates an OAuth integration into your Snowflake instance that Coda will use to securely authenticate end-users to your Snowflake instance.

  1. First, you will need to log into your Snowflake instance using an account that has the ACCOUNTADMIN role.

  2. Decide what existing Snowflake security roles you’d like to explicitly allow and disallow to be via the Snowflake pack. This can be used to restrict Coda’s access to sensitive data within Snowflake.

  3. Run the following command that will create the OAuth security integration that Coda can leverage.

    CREATE SECURITY INTEGRATION OAUTH_CODA_PACK  
    type = oauth
    enabled = true
    oauth_client = custom
    oauth_client_type = 'CONFIDENTIAL'
    oauth_redirect_uri = 'https://coda.io/packsAuth/oauth2/24936'
    oauth_issue_refresh_tokens = true
    oauth_refresh_token_validity = 7776000
    blocked_roles_list = ('SYSADMIN', 'ACCOUNTADMIN', 'ORGADMIN', 'SECURITYADMIN')
    pre_authorized_roles_list = ('ALLOWED_ROLE_ONE', 'ALLOWED_ROLE_TWO');

  4. Run the following command which will emit the set of Snowflake-generated OAuth secrets that can be used to configure the Snowflake pack.

    SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS( 'OAUTH_CODA_PACK' );

  5. This will return a JSON object containing the client ID and client secrets. In particular, you want to note down the values of the OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET that you will use when configuring Coda.

    {"OAUTH_CLIENT_SECRET_2":"SECRET2_ABC123","OAUTH_CLIENT_SECRET":"SECRET_ABC123","OAUTH_CLIENT_ID":"CLIENT_ID_ABC123"}

Add Snowflake Pack configuration and Configure OAuth Secrets

This step will add a new Pack configuration for the Snowflake pack and input the OAuth secrets from Snowflake.

  1. Go to your organization’s Pack approvals page

  2. Click “Allow a new Pack”, and find the Snowflake Pack

  3. Create a new Snowflake Pack configuration with the following policy:

    {  "connection": {    
    "oauth": {
    "scopes": [
    "refresh_token",
    "session:role:ALLOWED_ROLE_ONE"
    ]
    },
    "endpoint": {
    "url": "https://xy12345.us-west-2.snowflakecomputing.com"
    }
    }
    }

  4. For the endpoint URL, use the account URL you identified previously. For the OAuth scopes, include refresh_token but add the list of allowed session roles you configured on the Snowflake security integration.

  5. Once this Pack configuration is created, click on the Edit OAuth button.

  6. In the resulting dialog, input the following things:

    1. CLIENT ID: Use the OAUTH_CLIENT_ID output from the Snowflake OAuth security integration

    2. CLIENT_SECRET: Use the OAUTH_CLIENT_SECRET output from the Snowflake OAuth security integration.

    3. AUTHORIZATION URL: Use a URL like the following, substituting your Snowflake account identifier and cloud region ID (similar to : https://xy12345.us-west-2.snowflakecomputing.com/oauth/authorize

    4. TOKEN URL: Use a URL like the following, substituting your Snowflake account identifier: https://xy12345.us-west-2.snowflakecomputing.com/oauth/token-request

  7. Press Save

Share the Pack configuration with whichever Coda accounts you’d like to use in order to test the integration (see details on this here).

Test Snowflake integration

After the Snowflake Pack has been configured, you should be able to install the Snowflake Pack in a doc and connect to Snowflake.

  1. Type /snowflake into the canvas of your doc, and select the Snowflake Pack from the options

  2. Click the Add to doc button

  3. You should then see a button prompting you to add

  4. Follow the prompts to connect to your Snowflake account

  5. Once the OAuth flow is completed, you should have a new Snowflake account that shows the account and role of the user

  6. At this point you can add a new Query table. Just type /query in the canvas of your doc, and select the Query table from the options.

  7. In the Query field in the right-hand panel, type in your Snowflake query. Select the columns used to uniquify the request. Then run the query, and see the results in Coda.

Troubleshooting

"Failed to connect to Snowflake; please try again."

This error can appear when trying to connect to your Snowflake account, after signing in and approving access in Snowflake.

First verify that the client ID and secret you entered in the Pack configuration match the values generated by your Snowflake admin. You won't be able to view the current values, so try copying them in fresh and attempting another sign in.

This error could also be due to a network policy in Snowflake that prevents access from unknown IP addresses. Check with your Snowflake admin to see if a security policy is in effect, and if so ask them to add the following IP addresses to the allowlist:

52.37.21.175
54.214.147.89
35.155.255.238

These IP addresses can be also be found by looking up the DNS configuration of egress.coda.io.


Related resources

Did this answer your question?