Skip to navigation

Snowflake

To enable usage of Solvimon’s data seamlessly in your Snowflake instance. This page describes the steps necessary to connect your Snowflake instance to Solvimon for data exporting.

Getting started

Requirements

The following access is required for us to set-up a connection from your Snowflake instance to Solvimon.

  • A Snowflake account with the ACCOUNTADMIN role. If you don’t have an account with the ACCOUNTADMIN role, contact your Snowflake administrator to set one up for you.
  • If enforcing a network policy, allow Solvimon’s IP addresses to interact with your Snowflake instance.

Step 1: Create dedicated role, user, warehouse and database

This step will ensure granular control and separates Solvimon’s data from your own data. By executing the script below, this will automatically set-up a new user and warehouse that can be used for the data exports.

By default the script creates a new warehouse and database. This ensures granular controls on the data export process. If you’d prefer to use an existing warehouse, change the solvimon_warehouse variable from SOLVIMON_WAREHOUSE to the name of the warehouse to be shared. In case of an existing database change the solvimon_database variable from SOLVIMON_WAREHOUSE to the name of the database to be shared.

When running the script below, make sure you edit the password ({PASSWORD}) with a secure password. Note that special characters should not be used in the password. We recommend to exclusively use alphanumeric characters in the password.

SQL
-- set variables (these need to be uppercase)
set solvimon_role = 'SOLVIMON_ROLE';
set solvimon_username = 'SOLVIMON_USER';
set solvimon_warehouse = 'SOLVIMON_WAREHOUSE';
set solvimon_database = 'SOLVIMON_DATABASE';
set solvimon_schema = 'SOLVIMON_SCHEMA';
-- set user password
set solvimon_password = '{PASSWORD}';
begin;
-- create Solvimon Export role
use role securityadmin;
create role if not exists identifier($solvimon_role);
grant role identifier($solvimon_role) to role SYSADMIN;
-- create Solvimon Export user
create user if not exists identifier($solvimon_username)
password = $solvimon_password
default_role = $solvimon_role
default_warehouse = $solvimon_warehouse;
grant role identifier($solvimon_role) to user identifier($solvimon_username);
-- change role to sysadmin for warehouse / database steps
use role sysadmin;
-- create Solvimon Export warehouse
create warehouse if not exists identifier($solvimon_warehouse)
warehouse_size = xsmall
warehouse_type = standard
auto_suspend = 60
auto_resume = true
initially_suspended = true;
-- create Solvimon Export database
create database if not exists identifier($solvimon_database);
-- grant Solvimon Export warehouse access
grant USAGE
on warehouse identifier($solvimon_warehouse)
to role identifier($solvimon_role);
-- grant Solvimon Export database access
grant OWNERSHIP
on database identifier($solvimon_database)
to role identifier($solvimon_role);
commit;
begin;
USE DATABASE identifier($solvimon_database);
-- create schema for Solvimon Export data
CREATE SCHEMA IF NOT EXISTS identifier($solvimon_schema);
commit;
begin;
-- grant Solvimon Export schema access
grant OWNERSHIP
on schema identifier($solvimon_schema)
to role identifier($solvimon_role);
commit;

In your Snowflake interface, select the All Queries checkbox, and click Run. This will run every query in the script at once. If successful, you will see Statement executed successfully in the query results.

You can limit this grant down to specific schemas instead of the whole database. Note that to replicate data from multiple Snowflake databases, you can re-run the command above to grant access to all the relevant schemas.

Step 2: Authentication

We support two ways of authenticating:

  • Key pair (recommended)
  • Username and Password

Snowflake is deprecating sign-in with only a username and password. Users that rely on a password alone will be blocked by Snowflake, so we advise to use key pair authentication. See Snowflake’s key-pair authentication guide for more information.

With key pair authentication you generate a private and a public key. The public key is assigned to the user created in Step 1, and you share the private key together with the username with Solvimon.

1. Generate the key pair

Run the following commands on your machine (requires OpenSSL). They create an unencrypted 2048-bit private key (rsa_key.p8) and its public key (rsa_key.pub):

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

The private key must be unencrypted (generated with -nocrypt); encrypted private keys are not supported.

2. Assign the public key to the user

Copy the contents of rsa_key.pub without the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines, and assign it to the user created in Step 1:

SQL
use role securityadmin;
alter user SOLVIMON_USER set rsa_public_key = '{PUBLIC_KEY}';

Optionally, remove the password and mark the user as a service user, so it can only sign in with the key pair:

SQL
alter user SOLVIMON_USER unset password;
alter user SOLVIMON_USER set type = service;

3. Verify the key

Check that the fingerprint Snowflake shows as RSA_PUBLIC_KEY_FP in DESC USER SOLVIMON_USER; matches the fingerprint of your public key:

openssl rsa -pubin -in rsa_key.pub -outform DER | openssl dgst -sha256 -binary | openssl enc -base64

4. Share the username and private key

Share the username (for example SOLVIMON_USER) together with the full contents of the private key file rsa_key.p8, including the -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY----- lines. Solvimon needs both: the username identifies the user and the private key authenticates it. Keep the private key secret, anyone with this file can sign in as that user.

Username and Password

In this case we will use the username and password generated in Step 1 or a user created by you with the required access.

Step 3: Set network policies

This step is optional and only required if you are actively denying external access to you Snowflake instance. By default, Snowflake allows users to connect to the service from any computer or device IP address. A security administrator (i.e. users with the SECURITYADMIN role) or higher can create a network policy to allow or deny access to a single IP address or a list of addresses.

In case you actively deny external access you will need to add Solvimon’s IP addresses to the accepted list. Please reach out to us to receive the relevant static IP addresses that should be whitelisted.

Check network policies

To determine whether a network policy is set on your account or for a specific user, execute the SHOW PARAMETERS command (make sure you replace with the relevant username in the User example):

Account

SQL
SHOW PARAMETERS LIKE 'network_policy' IN ACCOUNT;

User

SHOW PARAMETERS LIKE 'network_policy' IN USER {USERNAME};

Step 4: Share the relevant information with Solvimon

Please confidently share the following information with us:

FieldDescription
HostThe host domain of the snowflake instance (must include the account, region, cloud environment, and end with snowflakecomputing.com). Example: accountname.us-east-2.aws.snowflakecomputing.com
RoleThe role you created in Step 1 to access Snowflake. Example: SOLVIMON_USER
WarehouseThe warehouse you created in Step 1 to sync data from. Example: SOLVIMON_WAREHOUSE
DatabaseThe database you created in Step 1 to sync data from. Example: SOLVIMON_DATABASE
SchemaThe schema in which the exported tables should be written to.
UsernameThe username you created in Step 1 to allow access to the database. Example: SOLVIMON_USER
Private keyWhen choosing the key pair option (recommended): The full contents of rsa_key.p8 generated in Step 2, shared together with the Username.
PasswordWhen choosing the username and password option: The password you set in Step 1.

As soon as we receive this information we will reach out and work with you directly to set-up the relevant data exports.