Connect to Snowflake from Python with Key-Pair Authentication

Overview

Setting up secure, MFA-compatible authentication for Snowflake using Python and Snowpark. This walkthrough covers environment setup, RSA key-pair generation, and connecting to Snowflake without interactive authentication prompts.

Setup

Create a Python environment via any method as needed. Then install the following packages via pip:

pip install snowflake-snowpark-python python-dotenv cryptography

Generating RSA Key Pair

Snowflake uses RSA key-pair authentication for programmatic access. You’ll generate a private key (stays with you) and a public key (uploaded to Snowflake).

Navigate to your project directory and create a keys folder:

mkdir keys
cd keys

Generate the key pair using OpenSSL:

# Generate private key
openssl genrsa -out rsa_key.pem 2048

# Convert to PKCS8 format (required by Snowflake) - unencrypted for development
openssl pkcs8 -topk8 -inform PEM -in rsa_key.pem -out rsa_key.p8 -nocrypt

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

For production environments, Snowflake strongly recommends encrypting the private key with a passphrase:

# Encrypted private key (recommended for production)
openssl pkcs8 -topk8 -inform PEM -in rsa_key.pem -out rsa_key.p8 -v2 aes-256-cbc

You’ll be prompted to set a passphrase. See the Python code section below for handling encrypted keys.

Important: Add keys/ to your .gitignore to prevent committing private keys!

# .gitignore
keys/
*.p8
*.pem

Register Public Key in Snowflake

Copy the public key content (excluding the -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY----- lines) into a single string.

In Snowsight, run the following SQL to register your public key:

ALTER USER YOUR_USERNAME SET RSA_PUBLIC_KEY_2='xxx123...';

Why RSAPUBLICKEY_2? Snowflake supports two RSA keys per user. Using _2 preserves your existing key (if you have one saved elsewhere), giving you a backup authentication method.

Verify the key was registered:

DESCRIBE USER YOUR_USERNAME;

Look for RSAPUBLICKEY2FP in the results - this confirms your key is active.

Configure Environment Variables

Create a .env file in your project root with your Snowflake credentials:

SNOWFLAKE_ACCOUNT=your_account_identifier
SNOWFLAKE_USER=your_username
SNOWFLAKE_ROLE=your_role
SNOWFLAKE_WAREHOUSE=your_warehouse
SNOWFLAKE_DATABASE=your_database
SNOWFLAKE_SCHEMA=your_schema
SNOWFLAKE_PRIVATE_KEY_PATH=keys/rsa_key.p8
SNOWFLAKE_PRIVATE_KEY_PASSPHRASE=  # Only needed if using encrypted key

Note on account identifier format: Modern Snowflake accounts use the orgname-accountname format:

# Legacy format
SNOWFLAKE_ACCOUNT=xy12345.us-east-1

# Newer organization format
SNOWFLAKE_ACCOUNT=myorg-myaccount

Connecting with Snowpark

Create a new notebook with something like the following to test out:

import os
from dotenv import load_dotenv
from snowflake.snowpark import Session
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives import serialization

# Load environment variables
load_dotenv()

# Build absolute path to key file
key_path = os.environ.get("SNOWFLAKE_PRIVATE_KEY_PATH")

# Handle encrypted or unencrypted private keys
passphrase = os.environ.get("SNOWFLAKE_PRIVATE_KEY_PASSPHRASE")
password = passphrase.encode() if passphrase else None

# Load private key
with open(key_path, "rb") as key_file:
    private_key = serialization.load_pem_private_key(
        key_file.read(),
        password=password,
        backend=default_backend()
    )

# Convert to bytes format Snowflake expects
pkb = private_key.private_bytes(
    encoding=serialization.Encoding.DER,
    format=serialization.PrivateFormat.PKCS8,
    encryption_algorithm=serialization.NoEncryption()
)

# Create connection parameters
connection_parameters = {
    "account": os.environ.get("SNOWFLAKE_ACCOUNT"),
    "user": os.environ.get("SNOWFLAKE_USER"),
    "role": os.environ.get("SNOWFLAKE_ROLE"),
    "database": os.environ.get("SNOWFLAKE_DATABASE"),
    "schema": os.environ.get("SNOWFLAKE_SCHEMA"),
    "warehouse": os.environ.get("SNOWFLAKE_WAREHOUSE"),
    "private_key": pkb
}

# Create session
session = Session.builder.configs(connection_parameters).create()
print(f"Connected to: {session.get_current_account()}")

Then test out pulling some data:

# List all tables in the current database/schema
tables = session.sql("SHOW TABLES").collect()
for table in tables:
    print(table["name"])

Summary

RSA key-pair authentication provides a secure, automated way to connect to Snowflake from Python applications without interactive prompts. This approach is particularly valuable in:

MFA-enabled environments where traditional username/password authentication would require manual interaction
CI/CD pipelines and automated scripts that need unattended Snowflake access
Production applications requiring robust, programmatic database connections

Key Benefits:

No interactive authentication prompts
Compatible with MFA requirements
More secure than embedded passwords
Supports key rotation for enhanced security

Next Steps: Consider implementing key rotation policies and explore Snowflake’s other authentication methods like OAuth for different use cases. You can also extend this setup to work with multiple Snowflake environments by using different key pairs per environment.