How to setup Snowflake key pair authentication

Key-pair authentication in Snowflake represents a significant enhancement in security by replacing the traditional username-password method with a more robust and secure approach. It is particularly important for machine users where enabling MFA is not practical.

This authentication method leverages public and private cryptographic keys, ensuring that access to the Snowflake environment is restricted to authorized users who possess the corresponding private key. By eliminating the reliance on passwords, which can be vulnerable to attacks such as phishing, brute force, and credential stuffing, key-pair authentication mitigates the risk of unauthorized access and enhances the overall security posture of your data operations in Snowflake.

In this post, I'll cover how to set up key-pair authentication in <10 minutes, step by step with screenshots and code snippets you can use directly.

Let's dive in.

Step 1: Generate a private key

Open your terminal and run the following command to create an encrypted private key file. If you’re using a mac, openssl should be installed by default, no additional setup required.

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

You’ll be prompted to enter an encryption password twice:

Step 2: Generate a public key

Next, you’ll generate the public key file by reference the private key created in the previous step. Run the following command in your terminal:

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

Again, you’ll be prompted to enter the passphrase (encryption password) from before:

Step 3: Store your private key, public key, and encryption password

This commands above will create the private key (rsa_key.p8 ) and public key (rsa_key.pub) files in whatever directory you ran the command above from. You can run open . in your terminal to open the current folder you’re in. You should see something like the screenshot below:

You should securely store both files along with the encryption password for future reference.

If you’re creating the key-pair for use in a SaaS tools like SELECT, you’ll be prompted to enter the private key file contents and encryption password:

Step 4: Assign the public key to a Snowflake user

Open the rsa_key.pub public key file in a text editor, or run cat rsa_key.pub from your terminal. Copy the text in between the ----BEGIN ...---- and ----END...----:

Next, assign the public key to a Snowflake user. In this example, the user name is machine_user.

alter user machine_user set rsa_public_key='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA3nD038XL7myESbMhTxzN
af1dUU2jkfeJby5/l5rQ6gy5uivtdgysdACKuMWmahhmdT1x77lbHa6LxlIebeVN
EJhOCg8wH0c2v+TvAcv9RQgvRy1Er2dz188ETExPhUfq7CJ3AqJ6RYiREPiE1iTE
ikkTXt0Vq8m3tP/DbO45g5v43ZWvPyQ+MhvCN3mECRTwzE6uueA+QiK4K5jOn1R1
CWsKDzzLUd3IvGnBoqoigOq5zQJfQN7ZaIOTYakab48oXi4iFZKBrhhGDw0Cnl+S
olfm4uu+DshVbiR9gY4Xmh/KOX9fpLjGH6wCVfOgtOa3/gkFDJzrfwb+v8R27HJx
rQIDAQAB';

If the Snowflake user previously had a password set, you should disable that login method by running the following command:

alter user machine_user unset password

Step 5: Validate public key was set correctly (OPTIONAL)

If you would like to validate everything was setup correctly, run the command desc user machine_user and make note of the output under RSA_PUBLIC_KEY_FP:

Then head back to the terminal and run the following command:

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

If the value in the output (ANpiIIzQDezLa6SvoU+zOAWX8tY9R8eLCC2KYJjjJLw=) matches the output of RSA_PUBLIC_KEY_FP shown in the screenshot, then everything was setup correctly.

Connecting a Key-Pair User via Python

If the Snowflake machine user is for use in a Python application, you can connect the user using the private key file and passphrase using the Snowflake Connector for Python. The scripts below show how this can be done.

First, read and decrypt the private key file:

import snowflake.connector
import os
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives import serialization

with open("<path>/rsa_key.p8", "rb") as key:
    private_key = serialization.load_pem_private_key(
        key.read(),
        password=os.environ['ENCRYPTION_PASSWORD'].encode(),
        backend=default_backend()
    )

private_key_bytes = private_key.private_bytes(
    encoding=serialization.Encoding.DER,
    format=serialization.PrivateFormat.PKCS8,
    encryption_algorithm=serialization.NoEncryption()
)

Next, connect to Snowflake using the same connect method as is used with username/password authentication, but instead of a password supply the private key:

conn = snowflake.connector.connect(
    user='<user>',
    account='<account_identifier>',
    private_key=private_key_bytes,
    )

cs = conn.cursor()

You can find your account identifier by following the instructions here.