Snowflake Key Pair Authentication in Matillion Data Productivity Cloud

To complement Snowflake's support for key-pair authentication, this article describes how and why this feature should be used with Matillion's product range.

Snowflake Key Pair Authentication - what and why?

Key Pair Authentication in Snowflake is an advanced security mechanism designed to offer enhanced protection for accessing and managing data.

Unlike traditional methods that rely solely on a username and password, key pair authentication takes advantage of the strength of cryptography, using a pair of uniquely linked keys: one public and one private. Only the person holds the private key, while the corresponding public key is assigned to their Snowflake user.

The two main advantages of key pair authentication over a traditional password are:

• Keys are typically longer and more complex than passwords, as you'll see in the examples below
• With key pair authentication, the server does not need to store the private key. Unlike with a password, Snowflake itself does not know your private key

This form of authentication helps ensure that only authorized users can execute transactions and manage data. Key pair authentication underpins a wider trust framework known as "public key infrastructure" (PKI). Because there are two keys, it is sometimes also known as "asymmetric", "public" or "dual" key cryptography.

Nowadays key pair authentication is considered best practice when authenticating to Snowflake. It is especially valuable for organizations requiring stringent controls over data integrity and confidentiality, providing a scalable and robust alternative to conventional authentication techniques.

So what does a private key actually look like?

PKCS#8 format example

Mathematically, a private key is just a (very) big number. There are multiple use cases in PKI, so for flexibility, rather than just write them out, keys are often stored and transmitted in an encoded format known as PKCS#8.

To generate a new private key using OpenSSL on a Linux host, run these commands:

You will see the private key in the new file, looking something like this:

To protect the data with a passphrase, omit the -nocrypt option above. You'll end up with a file that can only be accessed in combination with the passphrase:

You will need these files (and the passphrase, if you used one) in order to access Snowflake with key pair authentication in Matillion.

Snowflake Key Pair Authentication in the Matillion Data Productivity Cloud

The Matillion Data Productivity Cloud connects to Snowflake through an object known as an Environment. You'll arrive at the same configuration screen either when you are creating a first Project, or when adding a new Environment to an existing Project.

The Matillion Data Productivity Cloud offers two deployment models: Full SaaS and Hybrid SaaS.

• With Full SaaS (the default), Matillion handles all infrastructure, updates, and security, allowing for a zero-install and zero-maintenance experience. Matillion hosts all task execution, and securely manages customer secrets.
• In contrast, Hybrid SaaS (an Enterprise edition option) lets customers work within their own private cloud, offering full control over security, network isolation, and secrets storage.

Of course, private keys must be stored securely, as a cloud platform secret. Secrets are managed slightly differently depending whether you are using Full SaaS or Hybrid SaaS...

Full SaaS Key Pair

In full SaaS mode, your Projects screen will contain a section like this:

Follow "Add new environment", and in the Select authentication method screen set:

• Account: Your Organization name and Account name, separated by a hyphen
• Credentials type: Key pair
• Username: your name in Snowflake, which may be an email address
• Private key: Your private key, in PKCS#8 syntax, in the PEM base64-encoded exchange format. Include the -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY----- lines.
• Passphrase: The passphrase for your private key. Just leave it blank if there is none

For example:

If the settings are correct when you press Continue, you will arrive at the Snowflake details screen to choose a role and warehouse, plus a default database and schema.

Hybrid SaaS Key Pair

In hybrid SaaS mode, your Projects screen will contain a section like this:

Before adding an environment, you must have already saved your private key into your own cloud secret manager (for example: AWS Secrets Manager, Azure Key Vault or GCP Secret Manager). The same goes for the passphrase for your private key.

Saving the private key

Store your private key as a plain text secret, in PKCS#8 syntax, in the PEM base64-encoded exchange format. Include the -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY----- lines. for example:

Reference the value using just the name of the secret.

Saving the passphrase (optional)

Store this as a key-value secret, referencing the value with the name of the secret in combination with the name of the key.

Adding an Environment

Follow "Add new environment", and in the Select authentication method screen set:

• Account: Your Organization name and Account name, separated by a hyphen
• Credentials type: Key pair
• Username: your name in Snowflake, which may be an email address
• Private key secret name: the name of the plain text secret
• Passphrase secret name and Passphrase secret key (optional): set these to the name and key of the passphrase that you have saved in your cloud secret manager

For example:

Same as before, if the settings are correct when you press Continue, you will arrive at the Snowflake details screen to choose a role and warehouse, plus a default database and schema.

Snowflake Key Pair Authentication in Matillion ETL

For Matillion ETL users, Snowflake connections are managed using the Environments panel, which is always available on the main screen.

From a context click follow the option to add a new Environment. In the Snowflake Connection screen, set:

• Account: Your Organization name and Account name, separated by a hyphen
• Username: your name in Snowflake, which may be an email address
• Password type: Private Key
• Password: The name of your private key, that you have saved using the password manager. Use PKCS#8 syntax, in the PEM base64-encoded exchange format when storing the private key. You can include the -----BEGIN PRIVATE KEY----- and -----END PRIVATE KEY----- lines if you want, or leave them out.
• Passphrase: Leave it blank if there is no passphrase for your private key. Otherwise choose the password manager entry in which you have saved the passphrase.

For example:

This screen permits you to continue even if you provided incorrect settings. If the dropdown boxes in the following dialog are empty, either the authentication credentials are not correct, or your Snowflake user is insufficiently privileged.

Further reading

Snowflake guide to key-pair authentication.

A Guide to Connecting the Data Productivity Cloud Pipeline Designer to Snowflake, including how to find your Snowflake identifiers.

For Hybrid SaaS users, more about managing Elastic Container Services (ECS) Fargate Containers with the AWS CLI.

OpenSSL reference.