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 should enter the Preferred format URLs into Trelica. You can find the relevant information for constructing these URLs from the Snowflake UI:

1. Account
2. Organization
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 = 7776000;

alter security integration OAUTH_TRELICA set oauth_refresh_token_validity = 63072000;

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

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.

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

Click CONNECT

You will then be asked to login to Snowflake. Use the trelica_user username and associated password that you created above.

Then consent to the access scopes requested: