Skip to content

How to set up Snowflake key pair authentication#

This guide covers key pair authentication for service accounts (used for scheduled dbt jobs, CI, or Airflow), rather than local development.

Snowflake is removing password authentication for service users as part of its MFA rollout. A service account can't complete an MFA prompt or an SSO browser login.

Note

If you're setting up your own account for local development, see How to set up dbt development locally instead.

Before you start#

To assign a public key to a user, you need either the MODIFY PROGRAMMATIC AUTHENTICATION METHODS privilege on that user or OWNERSHIP of it (see Snowflake's instructions). If you don't have either, ask your administrators.

Generate a key pair#

From a terminal, generate a private/public key pair (see Snowflake's instructions for more detail):

mkdir -p ~/.ssh
chmod 700 ~/.ssh
cd ~/.ssh

# Unencrypted key (simplest). PKCS#8 format, which is what dbt requires.
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out snowflake_rsa_key.p8 -nocrypt

# Encrypted key (prompts for a passphrase). Same PKCS#8 format; omits -nocrypt.
# Use this one if your secret store can supply the passphrase alongside the key.
# openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out snowflake_rsa_key.p8

openssl rsa -in snowflake_rsa_key.p8 -pubout -out snowflake_rsa_key.pub
chmod 600 snowflake_rsa_key.p8

Register the public key with Snowflake#

Register the public key on the service account's Snowflake user (see Snowflake's instructions), using the contents of snowflake_rsa_key.pub (excluding the -----BEGIN/END PUBLIC KEY----- lines and newlines):

ALTER USER your_service_account ADD KEY PAIR my_key
  PUBLIC_KEY = 'xxxx...';

This registers a named key pair, which is what Snowflake recommends: it supports role restriction, expiration, and the rotation flow below. my_key is the name you'll refer to when rotating, so pick something you'll recognize.

To confirm the key registered correctly, compare the fingerprints reported by Snowflake and by OpenSSL.

Configure the connection#

Wherever this service account's credentials are configured (e.g a deployed profiles.yml, an Airflow connection), use private_key_path (or the raw key contents, depending on how your adapter expects it) instead of password. See Snowflake's client configuration instructions for other clients:

# snowflake format, key pair auth
profile_name:
  target: prod
  outputs:
    prod:
      type: snowflake
      account:
      user: your_service_account
      private_key_path: /path/to/snowflake_rsa_key.p8
      # private_key_passphrase:  # only needed if you generated an encrypted key
      role: your_service_role
      database: analytics
      warehouse: transforming_prod
      schema: prod
      threads: 8
      client_session_keep_alive: False

Rotate keys#

Snowflake recommends rotating a named key pair, which keeps the prior key valid for a grace period so clients can transition without downtime.

  1. Generate a new key pair.
  2. Replace the public key: ALTER USER your_service_account ROTATE KEY PAIR my_key PUBLIC_KEY = '<new public key>';
  3. Update the deployed credential to use the new private key, before the grace period expires. The prior key is valid for 24 hours by default; after that, Snowflake rejects it.