Snowflake key-pair authentication for service users: setup and zero-downtime key rotation

#Goal

Your ETL tools, dbt jobs and scripts log in to Snowflake with a user name and password. Snowflake is phasing out single-factor password sign-in for many user types (check the current timeline for your account), and passwords in pipeline config are a security finding anyway. Key-pair authentication replaces the password with an RSA private key that only the client holds. Snowflake stores the public key.

This guide covers creating the key, attaching it to a service user, configuring common clients, rotating the key without an outage, and checking who still uses passwords.

#Step 1: Generate the key pair

Run this on a trusted machine, not on a shared jump host. An encrypted private key is the safer default:

# Encrypted PKCS#8 private key (you will be prompted for a passphrase)
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

# Public key
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

If a tool can't handle an encrypted key, generate an unencrypted one with -nocrypt instead of -v2 des3, and protect the file with your secrets manager and file permissions.

chmod 600 rsa_key.p8

Warning

The private key is the credential. Store it in a vault (Azure Key Vault, AWS Secrets Manager, HashiCorp Vault), never in Git, and don't email or paste it into tickets. Only the public key goes to Snowflake.

#Step 2: Create or update the service user

Copy the public key body without the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines:

grep -v "PUBLIC KEY" rsa_key.pub | tr -d '\n'

Then, as SECURITYADMIN (or a role that owns the user):

USE ROLE SECURITYADMIN;

-- New service user: TYPE = SERVICE cannot have a password or use interactive login
CREATE USER IF NOT EXISTS svc_etl_prod
  TYPE = SERVICE
  DEFAULT_ROLE = ETL_PROD_ROLE
  DEFAULT_WAREHOUSE = ETL_PROD_WH
  RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA...'
  COMMENT = 'ETL pipelines, owner: data-platform team';

GRANT ROLE ETL_PROD_ROLE TO USER svc_etl_prod;

For an existing user that currently uses a password:

ALTER USER svc_etl_prod SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA...';

Don't unset the password yet. Switch the clients first (Step 3), confirm in login history (Step 5), then remove it.

#Confirm the key Snowflake holds matches your file

DESC USER svc_etl_prod;
-- look at RSA_PUBLIC_KEY_FP, e.g. SHA256:abc123...

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

The OpenSSL output should equal the value after SHA256: in RSA_PUBLIC_KEY_FP. If it doesn't, the client and Snowflake are using different keys.

#Step 3: Configure the clients

#Snowflake CLI (snow)

In ~/.snowflake/config.toml (permissions must be 0600 on Linux and macOS):

[connections.etl_prod]
account = "myorg-myaccount"
user = "svc_etl_prod"
authenticator = "SNOWFLAKE_JWT"
private_key_path = "/secure/keys/rsa_key.p8"
role = "ETL_PROD_ROLE"
warehouse = "ETL_PROD_WH"

Supply the passphrase through the PRIVATE_KEY_PASSPHRASE environment variable, then test:

export PRIVATE_KEY_PASSPHRASE='...'   # from your secrets manager
snow connection test -c etl_prod

Some CLI versions also accept private_key_file; key names vary by version, so check your build's documentation.

#SnowSQL

export PRIVATE_KEY_PASSPHRASE='...'
snowsql -a myorg-myaccount -u svc_etl_prod --private-key-path /secure/keys/rsa_key.p8 \
  -q "SELECT CURRENT_USER(), CURRENT_ROLE();"

#Python connector

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

with open("/secure/keys/rsa_key.p8", "rb") as f:
    key = serialization.load_pem_private_key(
        f.read(), password=os.environ["PRIVATE_KEY_PASSPHRASE"].encode()
    )

pkb = key.private_bytes(
    encoding=serialization.Encoding.DER,
    format=serialization.PrivateFormat.PKCS8,
    encryption_algorithm=serialization.NoEncryption(),
)

conn = snowflake.connector.connect(
    account="myorg-myaccount",
    user="svc_etl_prod",
    private_key=pkb,
    role="ETL_PROD_ROLE",
    warehouse="ETL_PROD_WH",
)
print(conn.cursor().execute("SELECT CURRENT_USER()").fetchone())

#dbt (dbt-snowflake)

# profiles.yml
my_project:
  target: prod
  outputs:
    prod:
      type: snowflake
      account: myorg-myaccount
      user: svc_etl_prod
      private_key_path: /secure/keys/rsa_key.p8
      private_key_passphrase: "{{ env_var('PRIVATE_KEY_PASSPHRASE') }}"
      role: ETL_PROD_ROLE
      warehouse: ETL_PROD_WH
      database: ANALYTICS
      schema: MARTS

#Step 4: Rotate the key without downtime

Each user has two public key slots, RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2. Either key is accepted, so you can overlap old and new:

1. Generate a new pair (Step 1) and store the new private key in the vault next to the old one.
2. Add the new public key to the empty slot:

ALTER USER svc_etl_prod SET RSA_PUBLIC_KEY_2 = 'MIIBIjANBgkqh...new...';

3. Roll the new private key out to every client that uses this user, and restart long-running services.
4. Watch login history (Step 5) until no logins use the old key. Logins don't record which slot matched, so the practical signal is that every client config now references the new key and no authentication failures appear.
5. Remove the old key:

ALTER USER svc_etl_prod UNSET RSA_PUBLIC_KEY;

Next rotation, use the RSA_PUBLIC_KEY slot for the new key and unset RSA_PUBLIC_KEY_2.

Caution

Unsetting the old key before every client has the new one causes immediate login failures for those clients. On a shared service user that can stop every pipeline at once. Keep one service user per system so a rotation has a small blast radius.

#Step 5: Find who still uses passwords

-- Logins in the last 7 days by authentication method (ACCOUNT_USAGE has some latency)
SELECT user_name,
       first_authentication_factor,
       reported_client_type,
       COUNT(*) AS logins,
       SUM(IFF(is_success = 'NO', 1, 0)) AS failures
FROM snowflake.account_usage.login_history
WHERE event_timestamp > DATEADD(day, -7, CURRENT_TIMESTAMP())
GROUP BY 1, 2, 3
ORDER BY user_name, logins DESC;

Users showing PASSWORD are the migration backlog; RSA_KEYPAIR shows key-pair logins. After a user has moved completely:

ALTER USER svc_etl_prod UNSET PASSWORD;
ALTER USER svc_etl_prod SET TYPE = SERVICE;

#Common errors

#Prevent it next time

• Create all new non-human users as TYPE = SERVICE with a key, never a password.
• Put the rotation steps in a scheduled runbook with a named owner, and record key creation dates in the vault.
• Alert on is_success = 'NO' spikes in LOGIN_HISTORY for service users; a failed rotation shows up there first.
• Combine key-pair auth with a network policy on service users so a leaked key alone isn't enough to log in.