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).
The first piece of information you need relates to the URL for your Snowflake instance.
Snowflake has two forms of URL.
Preferred format (account URL)
Legacy format (account locator URL)
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:
Alternatively if you are an organisation admin you can view this information as SQL:
use role ORGADMIN; show organization accounts;
The relevant fields are:
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;
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.
By default you will need to reconnect to Snowflake every 90 days (60s x 60m x 24h x 90d = 7776000).
You can request Snowflake Support to change the refresh token timeout for your Snowflake environment. For example, to request a timeout of 2 years, request the maximum OAUTH_REFRESH_TOKEN_VALIDITY for a SECURITY INTEGRATION to be increased to 63072000 seconds.
You can alter the refresh token validity using the command:
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.
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.
- Snowflake Account URL
- SCIM API key (if Provisioning or Deprovisioning are enabled)
- Client ID and Secret
- Account Locator
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:
Please sign in to leave a comment.