Links

Snowflake

Trelica connects to Snowflake in two ways, each requiring slightly different authentication:
  • Snowflake REST SQL, authenticated by OAuth.
  • The Snowflake SCIM interface, authenticated using a token (required for provisioning and deprovisioning).

Snowflake URLs

The first piece of information you need relates to the URL for your Snowflake instance.

URL formats

Snowflake has two forms of URL.

Preferred format (account URL)

https://<organization>-<account>.snowflakecomputing.com e.g. https://txxnfzn-nb73169.snowflakecomputing.com

Legacy format (account locator URL)

https://<locator>.<region>.<provider>.snowflakecomputing.com e.g. https://xg23290.uk-south.azure.snowflakecomputing.com/

Finding information for your URLs

You can enter either Preferred or Legacy format URLs into Trelica. You can find the relevant information for constructing these URLs from the Snowflake UI:
  1. 1.
    Account
  2. 2.
    Organization
  3. 3.
    Locator
Alternatively if you are an organisation admin you can view this information as SQL:
use role ORGADMIN;
show organization accounts;
The relevant fields are:
  • account_url
  • account_locator

OAuth configuration

As a user in the accountadmin role, create a new Worksheet in Snowflake (or using the SnowSQL CLI client) and run the following scripts.

Create the integration client

Create an OAuth client for Trelica to connect to:
create or replace security integration OAUTH_TRELICA
type = oauth
enabled = true
oauth_client = custom
oauth_client_type = 'CONFIDENTIAL'
oauth_redirect_uri = 'https://app.trelica.com/IntegrationsApi/Integrations/Snowflake/AuthCallback'
oauth_issue_refresh_tokens = true
oauth_refresh_token_validity = 86400;
If your Trelica organization is hosted in the EU, then please use https://eu.trelica.com as the prefix for the oauth_redirect_uri, and not https://app.trelica.com.
Then show the details of the token to get the client id and secret. The result is shown as JSON - you will need to copy out the OAUTH_CLIENT_SECRET and OAUTH_CLIENT_ID fields.
select system$show_oauth_client_secrets('OAUTH_TRELICA');
Do not use the OAUTH_CLIENT_SECRET_2 field

Create a role and user

When you connect to Snowflake from Trelica, you will be asked to login to Snowflake. This step will create a new user, assigned to a new Trelica role in Snowflake with minimal permissions to read the user list.​
To create the role, run:
create or replace role TRELICA;
grant all on integration OAUTH_TRELICA to TRELICA;
grant manage grants on account to TRELICA;
Then create a new user account. Substitute <password> for a strong password.
create user if not exists TRELICA_USER
password = '<password>'
login_name = 'TRELICA_USER'
display_name = 'Trelica Integration User'
must_change_password = false
default_role = TRELICA;
​
grant role TRELICA to user TRELICA_USER;

Generating a SCIM token

This is only required if you want to enable provisioning and deprovisioning.
You can only deprovision a user previously provisioned using the SCIM interface.
As a user in the accountadmin role, create a new Worksheet in Snowflake, and run the following script to create a Snowflake integration and associated role, and then generate a SCIM access token:
use role accountadmin;
create role if not exists GENERIC_SCIM_PROVISIONER;
grant create user on account to role GENERIC_SCIM_PROVISIONER;
grant create role on account to role GENERIC_SCIM_PROVISIONER;
grant role GENERIC_SCIM_PROVISIONER to role accountadmin;
create or replace security integration generic_scim_provisioning
type=scim
scim_client='GENERIC'
run_as_role='GENERIC_SCIM_PROVISIONER';
select SYSTEM$GENERATE_SCIM_ACCESS_TOKEN('GENERIC_SCIM_PROVISIONING');
Note that you can easily copy the token that gets emitted:

Renewing a SCIM token

The SCIM tokens last 6 months. The following script will create a fresh token:
use role accountadmin;
select system$generate_scim_access_token('GENERIC_SCIM_PROVISIONING');

Connecting to Snowflake from Trelica

You now have all the information you need to connect.
Enter your:
  • Snowflake Account URL
  • SCIM API key (if Provisioning or Deprovisioning are enabled)
  • Client ID and Secret
  • Account Locator
Last modified 5d ago