Snowflake Data Warehouse

Description

Used for working with Snowflake data warehouse.

Connection String Templates

Template Name Description
Snowflake The standard Snowflake connection string - requires details such as the host, account, database, warehouse, authenticator, and private key file.

When using the connection string the YOUR_FULL_HOST can contain your YOUR_ACCOUNT values as shown below:

Connection String Template:

Template Example Connection String
Snowflake Key-pair Authentication Connection Template account=YOUR_ACCOUNT;host=YOUR_FULL_HOST.snowflakecomputing.com;db=YOUR_DATABASE;warehouse=YOUR_WH;authenticator=SNOWFLAKE_JWT;private_key_file=YOUR_PRIVATE_KEY_PATH;

Connection Notes

Loome Monitor will use the SnowSQL internal stage with CSV files when staging data from an external source. If you wish to configure a specific stage or file type then you will need to ingest the data into Snowflake seperately using a Loome Integrate Data Migration task.

Please note that to enhance security and align with Snowflake’s evolving standards, we are updating our Snowflake connector to support key-pair authentication instead of username/password authentication. Learn more about setting up Snowflake Key-pair Authentication below.

How to Setup Snowflake Key-pair Authentication

Prerequisites

Before you begin, please ensure you have the following:

  • OpenSSL: A command-line tool for creating and managing certificates and keys. This is pre-installed on most Linux and macOS systems. Windows users may need to install it.
  • Snowflake Access: You’ll need a Snowflake user with the SECURITYADMIN role (or higher) to modify user accounts.

Part 1: Generate Your RSA Key-Pair

First, you’ll generate a private and public key pair in the modern, encrypted PKCS#8 format, which is recommended by Snowflake. The private key remains with your agent, and the public key is assigned to your user in Snowflake.

  1. Open your terminal or command prompt.

  2. Generate the Encrypted Private Key: Run the following command. This will generate a 2048-bit RSA key and immediately convert it into the encrypted PKCS#8 format using strong AES-256 encryption.

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

    You will be prompted to create and verify a passphrase. This passphrase is critical. Remember it, as you will need it later to configure the connector. The command will create a file named rsa_key.p8.

  3. Generate the Public Key: Next, generate the corresponding public key from the private key you just created.

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

    This command will ask for the passphrase you created in the previous step. You should now have two files: rsa_key.p8 (your secret private key) and rsa_key.pub (your shareable public key).

Important Security Note: Treat your rsa_key.p8 file and its passphrase with the same level of security as a password. Anyone who has access to both can authenticate as you in Snowflake.

Part 2: Assign the Public Key to Your Snowflake User

Now, you need to provide the public key to Snowflake so it can be associated with your user account.

  1. Open the Public Key File: Open rsa_key.pub in a plain text editor.
  2. Copy the Key Content: Copy the entire content of the file, excluding the —–BEGIN PUBLIC KEY—– header and —–END PUBLIC KEY—– footer. It should be a single, long string of characters. You may need to remove the space between each line in the file.
  3. Log in to Snowflake: Use the Snowflake web interface and log in with a user that has SECURITYADMIN privileges.

  4. Run the ALTER USER Command: Open a worksheet and execute the following SQL command, pasting your copied public key into the appropriate place.

    SQL ALTER USER your_snowflake_username SET RSA_PUBLIC_KEY='PASTE_YOUR_COPIED_PUBLIC_KEY_HERE';

    For example: SQL ALTER USER jsmith SET RSA_PUBLIC_KEY=‘MIIBIjANBgkqhkiG9w0BAQEF…your key content…IDAQAB’;

Your Snowflake user is now configured for key-pair authentication.

Part 3: Configure the Snowflake Connector in Loome

In this final phase, you’ll update your connector settings using the private key and passphrase you generated. We have reused the existing connector interface for a seamless transition.

  1. Navigate to the Connector Configuration: Open Loome and either create a new Snowflake connection or edit your existing one.

  2. Fill in the Connection Details:

    • Username: Enter your Snowflake username (e.g., jsmith).
    • Password: In this field, enter the passphrase you created for your rsa_key.p8 file.
    • Connection String: Construct your connection string with the new authenticator and private_key_file parameters.
      • authenticator must be set to SNOWFLAKE_JWT.
      • private_key_file must be the full path to where the agent can access your rsa_key.p8 file. The correct path depends on where your agent is installed (see the next section).
      • Connection String Template: account=YOUR_ACCOUNT;host=YOUR_FULL_HOST.snowflakecomputing.com;db=YOUR_DATABASE;warehouse=YOUR_WH;authenticator=SNOWFLAKE_JWT;private_key_file=YOUR_PRIVATE_KEY_PATH;

Part 4: Storing the Private Key for the Agent

The agent needs access to the rsa_key.p8 file. How you provide this access depends on your agent’s deployment environment.

Scenario A: Agent on a PC or Virtual Machine

This is the most straightforward scenario.

  1. Store the Private Key: Save your rsa_key.p8 file in a secure location on the same machine where the agent is running.

  2. Set the Path: Use the full, absolute path to the file in your connection string.

    • Windows Example: private_key_file=C:\agent\keys\rsa_key.p8
    • Linux Example: private_key_file=/home/user/agent/keys/rsa_key.p8
Scenario B: Agent in an Azure Container Service

(As a prerequisite, in Loome, when creating an agent, you also need to provide the fields for an external storage account. This is where we will be uploading the private key file) When using a containerized agent, you must use the linked Azure Storage Account to securely provide the key file.

  1. Upload the Private Key: Upload your rsa_key.p8 file to the Azure Storage Account that is configured for your agent’s container service.

  2. Set the Path: The path in the connection string must use the special prefix /external/aci/ followed by the path to the file within your Azure Storage Account.

    • Example: If you uploaded the key file to the data/keys/ folder in your storage account, the full path for the connection string would be: private_key_file=/external/aci/data/keys/rsa_key.p8

Once you save the connection with the updated settings, Loome will begin using key-pair authentication for the saved Snowflake connection.