Key Pair Authentication
Key Pair Authentication is a secure authentication method that uses a 2048-bit (or stronger) RSA key pair instead of a standard username and password.
This method is considered much more secure than password authentication and is the industry standard for service accounts and automated pipelines (like dbt, Airflow, or Fivetran) connecting to Snowflake.
Why Use Key Pair Authentication?
- Enhanced Security: Cryptographically much stronger than passwords, eliminating the risk of brute-force dictionary attacks.
- Automation-Friendly: You don't need to rotate or store passwords in CI/CD pipelines.
- No MFA Interference: Service accounts cannot answer Multi-Factor Authentication (MFA) prompts. Key Pair Authentication inherently satisfies strong authentication requirements without requiring an interactive MFA token.
Hands-On Demonstration: Setting Up Key Pair Authentication
In this demonstration, we will generate an RSA key pair on your local machine, assign the public key to a Snowflake service user, and demonstrate how to authenticate using Python.
1. Database & Schema Setup
First, let's create a database, schema, and a target table that our service account will eventually write to.
Run this in your Snowflake worksheet:
USE ROLE SYSADMIN;
CREATE DATABASE IF NOT EXISTS BEEMS_TRAINING_DB;
USE DATABASE BEEMS_TRAINING_DB;
CREATE SCHEMA IF NOT EXISTS SECURITY_LAB;
USE SCHEMA SECURITY_LAB;
CREATE OR REPLACE TABLE SECURITY_LAB.SERVICE_INGEST_LOG (
LOG_ID INT AUTOINCREMENT,
EVENT_MESSAGE STRING,
INGESTED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP()
);
2. Generate the RSA Key Pair
You must generate the keys locally. Do not generate keys inside Snowflake. Open your terminal/command prompt and run the following OpenSSL commands:
# 1. Generate an unencrypted private key (For highly secure production environments, use an encrypted key)
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
# 2. Generate the corresponding public key
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
You now have two files: rsa_key.p8 (Private) and rsa_key.pub (Public).
CRITICAL: Never share your private key (.p8). The public key (.pub) is what you give to Snowflake.
3. Create the Service User and Assign the Public Key
Open rsa_key.pub in a text editor. Copy the contents, omitting the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines.
Run this in your Snowflake worksheet:
USE ROLE SECURITYADMIN;
-- Create the Service Account
CREATE OR REPLACE USER PIPELINE_SVC_USER
COMMENT = 'Service account for automated data pipelines'
DEFAULT_ROLE = 'SYSADMIN' -- For demonstration. In production, assign a custom functional role.
-- Paste your public key string below
RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA...<PASTE_KEY_HERE>...';
-- Verify the key was added correctly
DESCRIBE USER PIPELINE_SVC_USER;
Note: You will see RSA_PUBLIC_KEY_FP (Fingerprint) populated in the output if successful.
4. Authenticate Using Python
Now, let's use the private key to authenticate to Snowflake via the Python Connector, without supplying a password.
Save this Python script locally and run it:
import snowflake.connector
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives.asymmetric import rsa
from cryptography.hazmat.primitives.asymmetric import dsa
from cryptography.hazmat.primitives import serialization
# 1. Read the private key
with open("rsa_key.p8", "rb") as key:
p_key= serialization.load_pem_private_key(
key.read(),
password=None, # Update if you encrypted the private key
backend=default_backend()
)
pkb = p_key.private_bytes(
encoding=serialization.Encoding.DER,
format=serialization.PrivateFormat.PKCS8,
encryption_algorithm=serialization.NoEncryption())
# 2. Connect to Snowflake
ctx = snowflake.connector.connect(
user='PIPELINE_SVC_USER',
account='<YOUR_ACCOUNT_IDENTIFIER>', # e.g. xy12345.us-east-1
private_key=pkb,
warehouse='COMPUTE_WH',
database='BEEMS_TRAINING_DB',
schema='SECURITY_LAB'
)
# 3. Execute a query to prove it works
cs = ctx.cursor()
try:
cs.execute("INSERT INTO SERVICE_INGEST_LOG (EVENT_MESSAGE) VALUES ('Authenticated via RSA Key Pair!')")
cs.execute("SELECT * FROM SERVICE_INGEST_LOG")
print(cs.fetchone())
finally:
cs.close()
ctx.close()
Key Rotation Best Practices
For maximum security, you should regularly rotate your keys. Snowflake supports assigning two public keys to a single user (RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2) to allow for zero-downtime rotation.
Rotation Workflow:
- Generate a new key pair locally.
- Assign the new public key to
RSA_PUBLIC_KEY_2in Snowflake (ALTER USER PIPELINE_SVC_USER SET RSA_PUBLIC_KEY_2 = '...'). - Update your pipeline/application to start using the new private key. (The application connects successfully using Key 2).
- Remove the old key from Snowflake (
ALTER USER PIPELINE_SVC_USER UNSET RSA_PUBLIC_KEY). - (Optional) Move Key 2 to Key 1 so you are ready for the next rotation cycle.
Summary
- Key Pair Authentication replaces passwords with RSA cryptographic keys.
- It is the standard for Service Accounts.
- The Public Key lives in Snowflake; the Private Key lives securely in your application environment.
- Support zero-downtime Key Rotation utilizing
RSA_PUBLIC_KEY_2.