Snowflake

Configure a snowflake account with RSA Key pair ( Public & Private )

• Login to your Snowflake account and switch role to ACCOUNTADMIN.

• Click on Account tab.

• Navigate to Users tab.

• Click on Create... to create a user. In the General section provide User Name and Password details, then click Next.

• In Advanced section fill in the details specific to your organization, these are examples only for Zilla. Click Next.

• In Preferences section, select ACCOUNTADMIN as Default Role and click Finish.

• Before proceeding, login with the above user to verify the user has been created and has the default role ACCOUNTADMIN.

Configure Key Pair Authentication for the new created user

• Step 1: Generate the Private Key

To generate an unencrypted version, use the following command:

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

To generate an encrypted version, use the following command (which omits “-nocrypt”):

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

• Step 2: Generate a Public Key

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

• Step 3: Store the Private and Public Keys Securely: These are sensitive keys, store securely so that no one can access.

• Step 4: Assign the Public Key to the new created Snowflake user with the following SQL query:

// Execute this query to enable admin privilege, so that we can modify user
USE ROLE ACCOUNTADMIN;

• Then execute the following query replacing {User Name} with the user name created above:

// Make change to user to associate RSApublic key
ALTER USER {User Name} SET RSA_PUBLIC_KEY='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKC........;

Note: To print the contents of the public key type $ cat rsa_key.pub on the terminal.

Note: The public key assigned is without the beginning and end prefix and post-fix, i.e. -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- respectively.

Now we have all the artifacts we need to configure the Snowflake integration.

Configure A Snowflake integration on Zilla Dashboard

• Login to Zilla with your admin account.

• Go to Applications tab and click Add Application.

• Type “Snowflake” in the search box. Click Add to Applications button to the right of the Snowflake entry.

• Fill in the form details and click Add to Applications.

• The Snowflake application entry shall appear in the Applications tab (if not visible, search for it). Click the app name link.

• Click Sync Now in the upper right of the application details page. You will see the configuration dialog. Click the slider below API Configuration to enable it.

• Fill in the details as follows:

  • RSA Private Key: The PVT RSA key generated in the above step, You can print the key on the terminal using $cat rsa_key.p8 command.

• • RSA Private Key Passphrase: If you created an encrypted RSA PVT key then a passphrase to decrypt the key must be provided here. If you have not created an encrypted key leave this blank.

  • Snowflake Account Id: If your Snowflake URL is https://unaXXXXX.us-east-1.snowflakecomputing.com/ then unaXXXXX.us-east-1 is your account id.

  • Account Admin Username: The User Name created above, in our example it’s Zilla.

  • Warehouse name ( Where SNOWFLAKE db & ACCOUNT_USAGE schema is located ): Name of the warehouse which has the SNOWFLAKE database & ACCOUNT_USAGE schema. Use show warehouses; command to see available warehouses in your account.

    

• After filling in the above details click Next.

• You will see configuration stored success toast as well as a small dialog saying In the next step, you will be asked to log in to Snowflake - 1, and then the data sync will start automatically. Click Next.