Snowflake

Setup steps

Prerequisites

* Administrative access to your Snowflake account.
* A command-line terminal with `openssl` installed.

1. Create a Service Role and User in Snowflake

Execute the following SQL commands in a Snowflake worksheet. Replace `"your_warehouse_name"` with the name of the warehouse Flows360 will use.

“`sql
— Use a role with sufficient privileges to create users and roles
USE ROLE ACCOUNTADMIN;

— 1. Create a dedicated role for Flows360
CREATE ROLE FLOWS360_ROLE;

— 2. Grant privileges to the role
— Grant usage on the target warehouse
GRANT USAGE ON WAREHOUSE "your_warehouse_name" TO ROLE FLOWS360_ROLE;

— NOTE: You must grant access to specific databases and schemas.
— For each database and schema the integration needs to access, run:
— GRANT USAGE ON DATABASE "some_database" TO ROLE FLOWS360_ROLE;
— GRANT USAGE ON SCHEMA "some_database"."some_schema" TO ROLE FLOWS360_ROLE;
— GRANT SELECT ON ALL TABLES IN SCHEMA "some_database"."some_schema" TO ROLE FLOWS360_ROLE;
— GRANT SELECT ON ALL VIEWS IN SCHEMA "some_database"."some_schema" TO ROLE FLOWS360_ROLE;

— 3. Create a dedicated user for Flows360
CREATE USER FLOWS360_USER
DEFAULT_ROLE = FLOWS360_ROLE
DEFAULT_WAREHOUSE = "your_warehouse_name"
COMMENT = 'Service user for Flows360 integration.';

— 4. Assign the role to the user
GRANT ROLE FLOWS360_ROLE TO USER FLOWS360_USER;
“`

2. Generate an RSA Key Pair

This authentication method uses a private key to sign a JWT and a corresponding public key stored in Snowflake to verify the signature.

1. Open your command-line terminal.
2. Generate a 2048-bit unencrypted private key.

“`bash
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
“`
3. Extract the public key from the private key.

“`bash
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
“`
Keep both `rsa_key.p8` (private key) and `rsa_key.pub` (public key) files.

3. Assign the Public Key to the Snowflake User

1. Open `rsa_key.pub` in a text editor and copy the entire content, excluding the `—–BEGIN PUBLIC KEY—–` and `—–END PUBLIC KEY—–` headers/footers.
2. Execute the following SQL in Snowflake, pasting the copied public key content as the value for `RSA_PUBLIC_KEY`.

“`sql
— Use a role with sufficient privileges
USE ROLE ACCOUNTADMIN;

— Assign the public key to the user
ALTER USER FLOWS360_USER
SET RSA_PUBLIC_KEY='your_public_key_string_here';
“`

4. Configure the Flows360 Connection

1. Navigate to the connections page in Flows360 and add a new Snowflake connection.
2. Fill in the required fields:
* Account Identifier: Your full Snowflake account identifier (e.g., `xy12345.us-east-1`).
* Username: `FLOWS360_USER`
* Role: `FLOWS360_ROLE`
* Warehouse: The name of the warehouse you granted access to (e.g., `"your_warehouse_name"`).
* Private Key: The full content of the `rsa_key.p8` file, including the `—–BEGIN PRIVATE KEY—–` and `—–END PRIVATE KEY—–` lines.
3. Save and test the connection.