Skip to main content

How to Generate Snowflake Client ID and Secret for OAuth

Acquire your Snowflake Account

Account and org names in Snowflake can be tricky. The easiest and fastest way to acquire the correct account identifier is to:

  1. Login to Snowfake
  2. In the lower left corner, click on your profile icon to show settings
  3. Hover over your account to display the account, org, and locator screen.
  4. Clock on the copy to clipboard icon as circled in the screenshot below.

To integrate your Snowflake account with DataTools Pro, you need to create a Custom Client Integration in Snowflake. This process involves setting up an OAuth-based security integration within Snowflake, which will generate a Client ID and Client Secret necessary for secure communication between Snowflake and our app.

Steps to Create a Custom Client Integration:

Create the Security Integration: Start by running the following command within your Snowflake environment to create the integration:

CREATE SECURITY INTEGRATION dtp_oauth_integration TYPE = OAUTH ENABLED = TRUE OAUTH_CLIENT = 'CUSTOM' OAUTH_CLIENT_TYPE = 'CONFIDENTIAL' OAUTH_REDIRECT_URI = 'https://app.datatoolspro/auth/snowflake/callback' OAUTH_ISSUE_REFRESH_TOKENS = TRUE OAUTH_REFRESH_TOKEN_VALIDITY = 7776000;

OAUTH_REFRESH_TOKEN_VALIDITY is setup for 90 days, which may or may not adhere to your own organization’s token refresh policy. DataTools Pro will fetch a new refresh token based on the interval you set.

Retrieve the Client ID and Client Secret: After creating the security integration, run the following command to retrieve the Client ID and Client Secret:

Note: The integration name should be capitalized in the code.SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('DTP_OAUTH_INTEGRATION');  Copy Code

    Important Notes:

    • The Redirect URI is the most important field in this process. It must exactly match the URL we provided for the OAuth callback. Any discrepancies will cause errors during the OAuth authentication process.
    • Once you have generated the Client ID and Client Secret, please enter them in the form below. This will allow us to securely link your Snowflake integration with our platform.

    By following these steps, you will successfully create a custom OAuth client integration, enabling secure and seamless communication between Snowflake and our application.

    Setting up a System User

    We recommend setting up a System user with read-only access to your Snowflake schema within the database(s) you would like to use with DataTools Pro. This ensures your least level of access privileges restricts direct access to your meta data only..

    Example code to generate the SYSTEM_DTPAPP_USER user and a role called METADATA_ACCESS_ROLE

    -- Step 1: Create the user
CREATE USER SYSTEM_DTPAPP_USER
PASSWORD = '<<YOUR_SECURE_STRONG_PASSWORD>>'
DEFAULT_ROLE = 'METADATA_ACCESS_ROLE'
COMMENT = 'User with access only to metadata for a specific database'
MUST_CHANGE_PASSWORD = FALSE;

-- Step 2: Create a role for metadata access IF ONE DOES NOT EXIST
CREATE ROLE METADATA_ACCESS_ROLE;

-- Step 3: Grant usage on the database and schema to the role
-- NOTE: Replace <<YOUR_DATABASE>> with the name of your target database
GRANT USAGE ON DATABASE <<YOUR_DATABASE>> TO ROLE METADATA_ACCESS_ROLE;
GRANT USAGE ON ALL SCHEMAS IN DATABASE <<YOUR_DATABASE>> TO ROLE METADATA_ACCESS_ROLE;

-- Step 4: Grant access to metadata (SELECT on information schema views)
-- NOTE: Replace <<YOUR_DATABASE>> with the name of your target database
GRANT SELECT ON ALL VIEWS IN SCHEMA <<YOUR_DATABASE>>.INFORMATION_SCHEMA TO ROLE METADATA_ACCESS_ROLE;

-- Step 5: Assign the role to the user
GRANT ROLE METADATA_ACCESS_ROLE TO USER SYSTEM_DTPAPP_USER;

    Snowflake Network Policy Considerations

    Your Snowflake org may have network rules and an active network policy in place already. To adhere and grant access to DataTools Pro to connect to your Snowflake instance, you will need to obtain the DataTools Pro IP address to add it to your Snowflake network policy: Learn more about configuring Snowflake Network policy

    Locate DataTools Pro IPs to add to your Snowflake Network Rules / Policy

    1. Login to DataTools Pro App
    2. Go to Settings
    3. Click on Snowflake Integration
    4. Click on the accordion menu item labeled “How to whitelist DataTools Pro IP address”
    5. Obtain the IP address to ensure you have the latest IPs for your region.

    Documentation for creating and adding IPs to a Network policy