Snowflake Credentials

Owned by Amanda Schertzer
Last updated: Jan 15, 2024
2 min read

To make a Snowflake connection, the following credentials are needed:

Field Name

Required/ Optional

Description

Field Name

Required/ Optional

Description

Type

Required

Snowflake

Name

Required

Provide a unique name for this credential

Client Id

Required

Enter Client Id found on the user’s Snowflake profile

Client Secret

Required

Enter Client Secret found on the user’s Snowflake profile

Base URL

Required

Enter Snowflake Base URL found on the user’s Snowflake profile

Locator Id

Required

Enter the unique Locator Id found on the user’s Snowflake profile

Access Token

Not Applicable

Leave blank. This field will auto-populate when connected to Snowflake.

Refresh Token

Not Applicable

Leave blank. This field will auto-populate when connected to Snowflake.

 Setting Up Connection

  1. Log into Snowflake.             

  2. A User must then open a new SQL worksheet

  3. Create a database role by running the command: CREATE DATABASE ROLE IF NOT EXISTS APIUSERROLE

  4. Grant the role to the user by running: Grant Role APIUSERROLE to USER <YourUserName>

  5. Alter the default user role as such: alter USER <UserName> set default_role = 'APIUSERROLE'

  6. Create the security integration as follows:

    CREATE SECURITY INTEGRATION IF NOT EXISTS Datablend TYPE = OAUTH OAUTH_CLIENT = CUSTOM OAUTH_CLIENT_TYPE = 'PUBLIC' ENABLED = TRUE OAUTH_ISSUE_REFRESH_TOKENS = TRUE OAUTH_REFRESH_TOKEN_VALIDITY = 86400 OAUTH_REDIRECT_URI='https://ml.datablend.com/api/plugins/snowflake/oauth' OAUTH_ALLOW_NON_TLS_REDIRECT_URI = TRUE PRE_AUTHORIZED_ROLES_LIST = (APIUSERROLE)

  7. Next, open the Admin panel within Snowflake.

    1. Edit user access to correspond with the desired warehouse.

  8. Click Roles next Users. This is located at the top of the web page.

  9. Select the APIUSERROLE and click on the “Manage Grants” button on the right side.

  10. Select Grant -> Grant Role -> and select a role with sufficient privileges to read/write to the database.

  11. Get the integration info by running the following command:

    describe SECURITY INTEGRATION datablend

  12. Get the OAUTH_CLIENT_SECRET using:

    select system$SHOW_OAUTH_CLIENT_SECRETS( 'DATABLEND' )

  13. Once OAuth is obtained, open Datablend and create a new credential.

  14. Copy the client ID from step 11 in the OAUTH_CLIENT_ID field and paste it into the Client ID in Datablend Credential creation page

  15. Copy just the secret from Step 12. It will look like “OAUTH_CLIENT_SECRET”:”SomeClientSecret” and paste it into Datablend.

10.   Find the OAUTH_ALLOWED_AUTHORIZATION_ENDPOINTS from step 11. There will be two URLs to select. Pick the second one from the https:// to the “.com” and disregard everything after the base URL. Paste this into the Base URL in the Credentials.

11.   Find your account identifier in Snowflake. This is located at the bottom left of the Snowflake page. Paste the account identifier into the Locator ID field.

12.  Click the Save button.

13.  Next, click the Connect button. This will redirect the user to the Snowflake consent form. Enter the Username and Password.

15.  Once Snowflake consent is accepted, Click the Test button to complete Credential verification.