Using Key Pair Authentication in Snowflake

Key Pair Authentication is a secure and efficient method to enable automated, non-interactive access to Snowflake. This guide provides step-by-step instructions to set up and use Key Pair Authentication.

Generate a Key Pair

1. Generate a Private/Public Key Pair: Use OpenSSL or a similar tool to create an RSA key pair (2048 or 4096 bits recommended):

   openssl genrsa -out sf_rsa_key.pem 2048

2. Extract the Public Key: Generate the public key from the private key:

   openssl rsa -in sf_rsa_key.pem -pubout -out sf_rsa_key.pub

3. Secure the Keys:
   • Keep the private key (sf_rsa_key.pem) secure and do not share it.
   • The public key (sf_rsa_key.pub) will be added to Snowflake.

Add the Public Key to Snowflake

1. Login to Snowflake: Log in to Snowflake as an administrator or the user who will use the key pair.

2. Add the Public Key: Use the ALTER USER command to associate the public key with your Snowflake user:

   ALTER USER <username> SET RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8A...';

   • Replace <username> with your Snowflake username.
   • Paste the content of the public key (sf_rsa_key.pub) in single-line format. 
   • Replace <username> with your Snowflake username.

   Simply updating the user may not enable Key Pair authentication. Confirm with Snowflake admins that Key Pair Authentication is available.

3. Verify the Public Key: Confirm that the key has been added by running:

   SHOW USERS STARTS WITH '<username>';

   • Check the RSA_PUBLIC_KEY field for your user.

Convert the Private Key to PKCS8 Format

SnowSQL and other tools may require the private key in PKCS8 format. Convert the key using OpenSSL:

openssl pkcs8 -topk8 -inform PEM -outform PEM -in sf_rsa_key.pem -out sf_rsa_key_pkcs8.pem -nocrypt

• Use sf_rsa_key_pkcs8.pem as your private key in subsequent steps.

Configure SnowSQL

1. Store the Private Key: Save the private key (sf_rsa_key_pkcs8.pem) in a secure location.

2. Update SnowSQL Configuration: Edit or create a SnowSQL configuration file (e.g., ~/.snowsql/config):

   [connections.my_connection]
accountname = <account_name>  
username = <username>  
authenticator = SNOWFLAKE_JWT  
private_key_file = /path/to/sf_rsa_key_pkcs8.pem
    

   • Replace <account_name> and <username> with your Snowflake account name and username.
   • Specify the path to the private key file.

Test the Connection

Run SnowSQL using the configured connection:

snowsql -c my_connection

• Ensure that you can connect without being prompted for a password or MFA.

Secure and Maintain the Key Pair

1. Restrict Access to the Private Key: Limit file permissions to ensure only the script or authorized users can access the key:

   chmod 600 /path/to/sf_rsa_key_pkcs8.pem

2. Rotate the Key Periodically: Regularly generate a new key pair and update the public key in Snowflake to maintain security.
3. Backup the Key: Store a copy of the private key in a secure location for disaster recovery purposes.

Automate Securely

1. Use environment variables or a secure vault to store sensitive file paths and credentials.
2. Implement logging and monitoring for script executions.
3. Test automated scripts thoroughly to ensure they function correctly without manual intervention.