Normally I would log into Snowflake with Signle Sign On or with Username and Password, but I wanted to know more about Key Pair Authentication, especially in relation with Snowsql. I have a couple of scripts that I use for installing into Snowflake. And with Snowflake you can do this with the command line tool Snowsql.
Key pair authentication
As the help pages indicate, Snowflake supports key pair authentication for enhanced authentication security as an alternative to basic authentication (i.e. username and password). The authentication method uses a 20248 RSA key pair. It seems that it is possible to generate Private key with OpenSSL. Let's try this out.
And the Client tools like Snowsql (but I also saw it in Visual Studio Code) can use this Key Pair Authentication method to connect. In this blogpost I will investigate the Key Pair authentication with SnowSQL and with Visual Studio Code.
There are six steps to take in order to use the Key Pair Authentication successfully :
- Step 1: Generate the Private Key
- Step 2: Generate a Public Key
- Step 3: Store the Private and Public Keys Securely
- Step 4: Assign the Public Key to a Snowflake User
- Step 5: Verify the User’s Public Key Fingerprint
- Step 6: Configure the Snowflake Client to Use Key Pair Authentication
Step 1: Generate the Private Key
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
At first glance, it seems that nothing happened but there is a small file created on the disk :
And in this file the encryption key is stored (I show here a small snippet of the key) :
Public keys and private keys are the working parts of Public-key cryptography. Together, they encrypt and decrypt data. The public key is public and can be shared widely while the private key should be known only to the owner. So be careful with the private key.
This was the first step of key pair authentication and it was about generating the private key. Now let's have a look at generating public keys.
Step 2: Generate a Public Key
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
You need to enter the pass phrase again in order to generate the public key. When this succeeds a new file is generated on the disk. In this case rsa.pub.
And in this file the public key is stored :
Step 3: Store the Private and Public Keys Securely
Step 4: Assign the Public Key to a Snowflake User
USE ROLE SECURITYADMIN;
// create user with password authentication
CREATE USER INSTALL_USER_PRD
RSA_PUBLIC_KEY = 'MIdfsasdfsdfadsRT6xJnEB8+p
DEFAULT_ROLE = SYSADMIN;
// grant usage on the default role
GRANT ROLE SYSADMIN TO USER INSTALL_USER_PRD;
Step 5: Verify the User’s Public Key Fingerprint
Step 6: Configure the Snowflake Client to Use Key Pair Authentication
Experiments with SnowSQL
snowsql -f D:\Git\DeployTest\CREATETEST2.sql
Visual Studio Code
Allthough happy that it works I'm not completely happy that the "Private Key Pass Phrase" is stored in the Environment variable. There are a couple of problems with that:
- In company controlled systems you can't adjust the environment variables because that is part of the windows system.
- Is it save to store passwords in Environment variables or in files in plain text? This is something that I saw in the config file :
My 2 cents,