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
Add OAuth Security Integration to Snowflake
Add Snowflake Pack configuration
Test Snowflake configuration
Troubleshoot errors
FAQs

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.

Instance URL: This URL should look something like https://{ACCOUNT_IDENTIFIER}.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.

First, you will need to log into your Snowflake instance using an account that has the ACCOUNTADMIN role.
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.

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  // 90 days
  // Optional: Block additional roles.
  BLOCKED_ROLES_LIST = (
    'SYSADMIN', 'ACCOUNTADMIN', 'ORGADMIN', 'SECURITYADMIN'
  )
  // Optional: Preapprove certain roles.
  PRE_AUTHORIZED_ROLES_LIST = (
    'ALLOWED_ROLE_ONE', 'ALLOWED_ROLE_TWO'
  );

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');
```
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.

Navigate from your Coda workspace documents list to More options ... → Organization settings → Pack approvals.
Find the Snowflake Pack, expand it, and click the Add new configuration button.
In the resulting dialog enter the following information:
1. Configuration name: A descriptive name for the configuration. It may be shown to the user when they are installing the Pack.
2. Permissions: Decide if the configuration can be used by everyone or only specific people. If the latter, enter the names of the users and groups that should have accces.
3. URL: The account identifier you retrieved earlier, to form the instance URL.
4. Role: The Snowflake role to connect with, as determined in the previous section.
Click the Save configuration button to continue.
Locate the Pack configuration you just created, and click the link Finish setting up OAuth that appears next to it.
In the resulting dialog enter the following information:
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.
Press the Save button.

The configuration is now complete and can be used in a Coda doc to connect to your Snowflake data.

If you need to edit the configuration hover over it and use the three-dots menu to the left to update the policy or OAuth settings. The policy settings dialog also has option to allow you to edit the underlying JSON directly it you want to enable additional features.

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.

Type /snowflake into the canvas of your doc, and select the Snowflake Pack from the options
Click the Add to doc button
You should then see a button prompting you to add
Follow the prompts to connect to your Snowflake account
Once the OAuth flow is completed, you should have a new Snowflake account that shows the account and role of the user
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.
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.

Advanced options

To set more advanced configuration options for the Snowflake Pack, click the link at the bottom of the policy dialog labeled manually edit policy JSON. The full set of configuration options can be found in the Administration tab of the Snowflake Pack listing page.

Prevent sharing of Snowflake data

The permissions defined on the Snowflake Pack configuration determine who can use the Pack, but by default don't limit how a doc containing Snowflake data can be shared. You may want to place tighter controls over some or all of your data, to ensure it isn't shared more broadly than desired.

You can prevent docs that contains Snowflake data from being shared with people that don’t have access to that Pack configuration by adding the following setting to the policy JSON:

{  
  "doc": {
    "sharing": {
      "enforcePackConfigurationPermissions": true
    }
  },
  // The rest of the config ...
}

For each doc using this configuration, all users not on the permissions list will be removed from the doc, as well as prevent future attempts to share the doc with those users as well.

Disable two-way edits

While the primary use case for the Snowflake Pack is to query data, the Pack also provides the ability to edit or modify Snowflake tables as well. You can disable these features by adding the following setting to the policy JSON:

{  
  "pack": {
    "fetches": "allow",
  },
  // The rest of the config ...
}

This setting explicitly allows fetching data, but disallows actions that would modify data.

Role validation

The Pack configuration specifies the role to use when authenticating the user to Snowflake, but by manipulating the login URL a malicious user could attempt to login with a different role instead. This can be prevented by adding the following setting to the policy JSON:

{
  "connection": {
    "identifier": {
      "allowedConnectionName": {
        "regex": "[(]role ROLE_NAME[)]$"
      }
    },
    // The rest of the connection config ...
  },
  // The rest of the config ...
}

Replace the placeholder ROLE_NAME with the name of the role used in that configuration. Once configured, if a user attempts to authenticate with a different role than the one specified it will be rejected by Coda.

Troubleshoot errors

I'm seeing an error that says "Failed to connect to Snowflake; please try again." How do I resolve this?

This error could be caused by a few different scenarios:

1) The client secret was entered incorrectly during setup. Have your Coda admin double check that these values were copied correctly.

2) The Snowflake instance has a network policy that prevents connections from unknown IP addresses. If so you’ll need the Snowflake admin to add Coda’s IP addresses to their network policy:

52.37.21.175
54.214.147.89
35.155.255.238

3) The user trying to sign in doesn’t have access to the Snowflake Pack configuration in use in the doc (only one configuration can be used per-doc). Either have the Coda admin share the Pack configuration with the user, or the user will need to create a new doc and install the Pack with a different configuration.

When trying to sign in to Snowflake I get the error “Invalid consent request.” How can I resolve this?

This error is known to occur in two different scenarios:

1) The Pack configuration specifies a role to use, but the user doesn’t have that role assigned in Snowflake. The user either needs to have the role assigned in Snowflake, or they should chose a different configuration to use when installing the Pack. Also check that the role is spelled correctly, with correct capitalization.

2) The configuration doesn’t specify which role to use, and the user’s default role in Snowflake is in the BLOCKED_ROLES_LIST of the Snowflake integration. The user can either change their default role in Snowflake or the Coda admin should update the Pack configuration to specify the role to use explicitly.

I’ve entered my query but I’m getting the error “Sync settings are invalid." How do I resolve this?

Before you can start syncing your data you need to select a value for the UNIQUE COLUMNS parameter. Choose the set of columns that, when taken together for a given row, represent a unique identified for that row. What these columns should be depends on the dataset being queried.

Once the unique columns are selected the Sync now button should be enabled.

The UNIQUE COLUMNS parameter shows “No matches found” and a red error dot. How do I fix this?

This means there is an error in your query that prevents the Pack from determining the set of columns in the result.

Hover over the dropdown list to see the error message from Snowflake, and use that information to fix your query.

Once you’ve adjust your query, click into the UNIQUE COLUMNS dropdown list again. It may take a few seconds for the list of columns to appear, or for the error message to refresh.

I get the error “Personal control” under my query and I can’t sync the table. How do I resolve this?

Personal controls can have a different value for each user, which doesn’t work when you are trying to define a stable query. For this reason Pack parameters can’t use personal controls.

To resolve the error switch the control to use the “Collaborative” mode, so that all users share the same value. Learn more here.

Why do I get the a “SQL compilation error” when trying to use the SET command?

Unfortunately the Pack doesn’t support setting session variables via the SET command, as it’s not supported by the underlying integration technology.

An alternative is to use bind variables in your query, which you populate via the Values parameter.

FAQs

Why did I receive a request to approve access to the Snowflake Pack even though Pack approvals isn’t enabled for my org?

Because the Snowflake Pack requires setup from the Coda org admin and Snowflake admin before it can be used, we prevent users from directly installing it until that is complete. The only available option for the users in this case is to request access, which will use the Pack approvals infrastructure to notify the admin. Coda org admins may be confused to see these requests when the Pack approvals toggle is disabled.

How do I ensure sensitive Snowflake data isn’t shared with users that don’t have access to it?

Setting up the Snowflake Pack requires using the Pack configurations feature, and there are additional configuration options you can add to the configuration to restrict how the Pack and it’s data is used and shared.

You can prevent docs that contains Snowflake data from being shared with people that don’t have access to that Pack configuration by adding the following setting to the policy JSON:

{  "doc": {
     "sharing": {
       "enforcePackConfigurationPermissions": true
    }
  },
  // The rest of the config ...
}

The full set of configuration options can be found in the Administration tab of the Snowflake Pack listing page.

Why do rows appear to be missing in the sync table in Coda?

This can happen if the UNIQUE COLUMNS parameter is inaccurate, and multiple rows have the same values for the selected combination of columns. Rows with the same values for those columns will “collide” and only one of them will appear in the table.

To resolve the issue the user should add additional columns to the list of unique columns. This may require that they alter the query to select more columns so that they can define a truly unique value per-row.

I’ve changed the sorting in my query, but the table in Coda didn’t change. How can I fix this?

This is a known UX issue. The row order is used in the initial sync when adding rows to the sync table, but later syncs won’t reorder rows already in the table (and new rows are added to the end).

To get the table sorting to match the query the user must replicate the query sorting in the table sort settings, and keep those in sync as they change.

How do I use two different Snowflake roles in the same doc?

Unfortunately this isn’t possible at the moment. A Pack configuration can specify only a single role, or be left blank to use the user’s default role. Additionally a doc can only use a single Pack configuration, meaning it’s not possible to use multiple roles in the same doc.

To query information under different roles you’ll need to create separate docs, each with a different Pack configuration applied.