Snowflake

Creating a connection to Snowflake requires to set up key-pair authentication

Steps

1. Generate a private and a public key

2. Assign the public key to a Snowflake user

3. Create and format a credentials JSON file

For more information on Snowflake key-pair authentication please refer to Snowflake documentation https://docs.snowflake.com/en/user-guide/key-pair-auth

1. Private and public key generation

Use the following command to generate a private key (called rsa_key.p8)

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

Then use the following command to generate a public key (called rsa_key.pub linked to the private key called rsa_key.pub stored in the current directory)

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

2. Assigning public key to Snowflake user

From the Snowflake interface or the Snowflake CLI run the following SQL query to assign the public key generated at step 1 (rsa_key.pub) to the snowflake user :

ALTER USER <user_name> SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Nota bene :

• Exclude the public key delimiters in the SQL statement

• To run this query you will need one of the following role/privilege

  • The MODIFY PROGRAMMATIC AUTHENTICATION METHODS or OWNERSHIP privilege on the user

  • The SECURITYADMIN role or higher

Also make sure the user also has a default warehouse, else run this query :

ALTER USER <user_name> SET DEFAULT_WAREHOUSE=<warehouse_name>

3. Edit a credentials file

Now you need to generate a JSON file with the following format :

{
"user" : "user_name",
"account" : "organinization_name-account_name",
"database" : "database",
"private_key" : "-----BEGIN PRIVATE KEY-----\nMII..."
}

• "user" : the user you associated the public key with

• "account" : the "account" property is what is called "Account identifier" in Snowflake. It's usually the organization name and the account name hyphenated

• "database" : the database containing the data you want to make available to mediarithmics

• "private key" : the private key generated in step one and associated to the user public key. Be ware of the formatting of the private key : the key must be in on a single line, this means you should use a "\n" separator at each return to line of the private key.