Snowflake - Authorization

Finding the Correct SNOWFLAKE_ACCOUNT Value

If you're seeing a 404 Not Found error when trying to connect to Snowflake in Workflows, it may be due to an incorrect SNOWFLAKE_ACCOUNT value.

The value you see in the Snowflake URL (e.g., zzc29891 from https://app.snowflake.com/...) is not always the correct account name for connections via Workflows.

To find the correct value:

1. Open a worksheet in Snowflake.
2. Run the following: SELECT CURRENT_ACCOUNT(), CURRENT_REGION();
3. Combine the results into this format: SNOWFLAKE_ACCOUNT = <account>.<region>

Example:

SNOWFLAKE_ACCOUNT = apqj105.us-east-1

This value should be put that in Account Name.

Connecting Snowflake to Workflows requires you to have:

1. Workflows IP Addresses whitelisted
2. A Snowflake account with read/write access to the database and all associated tables/views you wish to access. We recommend setting up an account specifically for Workflows access.
3. A warehouse that the account has access to. We recommend setting up a warehouse specifically for Workflows queries.

Whitelisting Workflows IP Addresses

1. Log into your Snowflake Account.
2. Open a new worksheet.
3. Paste the following script in the worksheet

CREATE NETWORK POLICY WORKFLOWS_ACCESS
ALLOWED_IP_LIST = ('54.190.66.63', '52.42.73.100', '44.231.239.186', '44.225.245.149');

4. Click Run

1. Log into your Snowflake Account.
2. Open a new worksheet.
3. Paste the following script in the worksheet, updating {policy_name} with the correct value.

ALTER NETWORK POLICY {policy_name}
SET ALLOWED_IP_LIST = ('54.190.66.63', '52.42.73.100', '44.231.239.186', '44.225.245.149');

4. Click Run

Creating a Snowflake Role and User for Workflows

This guide will walk you through the process required to create a unique role and user account for Workflows to access your Snowflake Database.

CAUTION: You can always use your own credentials, but this gives you less control over security logging and data access.

1. Log into your Snowflake Account.
2. Open a new worksheet. Select the checkbox to run "All Queries".
3. Paste the following script in the worksheet, changing the variables at the top as needed.

BEGIN;

   -- create variables for role, user, and password (values must be in ALL_CAPS)
   SET ROLE_NAME = 'WORKFLOWS_ROLE';
   SET USER_NAME = 'WORKFLOWS_USER';
   SET USER_PASSWORD = 'randompassword789';

   -- change role to securityadmin for set role and user
   USE ROLE securityadmin;

   -- create role for shipyard
   CREATE ROLE IF NOT EXISTS identifier($role_name);
   GRANT ROLE identifier($role_name) TO ROLE SYSADMIN;

   -- create a user for shipyard
   CREATE USER IF NOT EXISTS identifier($user_name)
   PASSWORD = $user_password
   DEFAULT_ROLE = $role_name;

   GRANT ROLE identifier($role_name) TO USER identifier($user_name);

 COMMIT;

Giving the Workflows Role Warehouse Access

This guide will walk you through the process required to either create a unique warehouse for Workflows to run queries against your Snowflake Database, or give the newly created Workflows Role access to an existing warehouse.

1. Log into your Snowflake Account.
2. Open a new worksheet. Select the checkbox to run "All Queries".
3. Paste the following script in the worksheet, changing the variables at the top as needed.

begin;

 -- create variables for user / password / role / warehouse / database (needs to be uppercase for objects)
   set role_name = 'WORKFLOWS_ROLE';
   set user_name = 'WORKFLOWS_USER';
   set warehouse_name = 'WORKFLOWS_WAREHOUSE';

 -- change role to sysadmin for warehouse steps
   use role sysadmin;

-- create a warehouse for shipyard. Remove this if you would like to use an existing warehouse
   create warehouse if not exists identifier($warehouse_name)
   warehouse_size = xsmall
   warehouse_type = standard
   auto_suspend = 60
   auto_resume = true
   initially_suspended = true;

-- grant shipyard role access to warehouse
   grant USAGE
   on warehouse identifier($warehouse_name)
   to role identifier($role_name);

 -- change role to securityadmin for user updates
   use role securityadmin;

-- set default warehouse for shipyard user
   ALTER USER IF EXISTS identifier($user_name)
   SET DEFAULT_WAREHOUSE = $warehouse_name;

commit;

4. Click Run.

CAUTION: Using an existing Warehouse may result in Workflows processes contendending for resources.

1. Log into your Snowflake Account.
2. Open a new worksheet. Select the checkbox to run "All Queries".
3. Paste the following script in the worksheet, changing the variables at the top as needed.

begin;

 -- create variables for role, user, and warehouse (values must be in ALL_CAPS)
   set role_name = 'WORKFLOWS_ROLE';
   set user_name = 'WORKFLOWS_USER';
   set warehouse_name = 'WORKFLOWS_WAREHOUSE';

 -- change role to sysadmin for warehouse steps
   use role sysadmin;

-- grant shipyard role access to warehouse
   grant USAGE
   on warehouse identifier($warehouse_name)
   to role identifier($role_name);

 -- change role to securityadmin for user updates
   use role securityadmin;

-- set default warehouse for shipyard user
   ALTER USER IF EXISTS identifier($user_name)
   SET DEFAULT_WAREHOUSE = $warehouse_name;

commit;

4. Click Run.

Giving Snowflake Database Access to Workflows User

This guide will walk you through the process required to give database access to a Workflows account so you can run queries against it.

1. Log into your Snowflake Account.
2. Open a new worksheet.
3. Paste the following script in the worksheet, changing the variables at the top as needed. This script will grant all privledges to all the current and future tables/views that exist within the specified database.

begin;

 -- create variables for role and database (values must be in ALL_CAPS)
   set role_name = 'WORKFLOWS_ROLE';
   set database_name = 'DEMO_DB';

-- grant shipyard access to database
   grant ALL
   on database identifier($database_name)
   to role identifier($role_name);

   grant all
   on all tables
   in database identifier($database_name)
   to role identifier($role_name);

   grant all
   on all views
   in database identifier($database_name)
   to role identifier($role_name);

   grant all
   on future tables
   in database identifier($database_name)
   to role identifier($role_name);

   grant all
   on future views
   in database identifier($database_name)
   to role identifier($role_name);

commit;

NOTE: You can adjust this script as needed if you want Workflows to have stricter access to your database.

Authenticating with a Private Key

1. To generate a private key file, run the following in the terminal

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8

NOTE: You will need to generate a passphrase to open the private key file. Be sure to save this passphrase as you will need to pass it as an input in Workflows

2. Once the private key file is generated, created a corresponding public key by running the following in the terminal

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

3. Assign the public key to appropriate user by running an ALTER statement

ALTER USER jsmith SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

NOTE: Only security administrators (i.e. users with the SECURITYADMIN role) or higher can alter a user. Aslo be sure to exclude the public key delimiters in the SQL statement.

4. Verify the User's Public Key Fingerprint by running a DESCRIBE command

   DESC USER jsmith;
   

   The fields RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_FP should both be populated.

For more information, visit the Snowflake Documentation