Author: Shashank Sanehi
Introduction:
Snowflake uses key pair authentication for more enhanced authentication security other than basic authentication (i.e., username and password method).
This authentication method requires a 2048-bit RSA key pair at the minimum.
We can know the client's identity by using asymmetric cryptography algorithms, with public and personal keys, instead of using username and password
Steps To Follow:
1. Install open SSL
We have to put in OpenSSL, which is able to generate the Privacy Enhanced Mail (i.e., PEM) private-public key pair,
https://slproweb.com/products/Win32OpenSSL.html (link)
2. Set up environment variables
Set up environment variables by visiting Search-> Edit the System Environment Variables-> System Properties -> Environment Variables->Path->Edit->New->Paste the location of your Open SSL.
3. Open the command prompt for open SSL and then Generate a private key and set the encryption password (as per your choice)
4. It will ask to verify the password.
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8
Note: Private key file is going to be created in your system
E.g.: C:\Users\Admin\rsa_key.p8 (path location is the location in command prompt)
5) Generate the public key and enter the private key’s encryption password in the passphrase and clickEnter
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
-> Public key files are created in your system
6. Now we enter Snowflake, and set the public key by altering the user
alter user xyz set rsa_public_key= 'xxxxx'
7. Mostly, we all have SnowSQL installed during training; if not, we can download it from the Snowflake interface.8) Enter the private key path in snowsql config file saved in your computer after installation (config files is going to be in .snowsql folder)
-> Also enter the #password ,#username field too if error occurs
9. Now Open the command prompt and give the private key passphrase (the same encryption password we set up for the private key)
set SNOWSQL_PRIVATE_KEY_PASSPHRASE='<passphrase>’
And then open powershell for log in to snowsql and enter the private key passphrase
Note- I have used PowerShell as I had encountered a few errors while running the cmd. You can try with the cmd prompt too
Note: Provide the correct username while writing snowsql command
Key Pair Rotation:
For key pair rotation, Complete all steps in Configuring Key Pair Authentication with the subsequent updates:
->Generate a replacement private and public key set.
->Assign the general public key to the user. Set public key value to either RSA_PUBLIC_KEY or RSA_PUBLIC_KEY_2, which we are not using. For example:
alter user xyz set rsa_public_key_2='<new public key here>';
->Update the code for connecting to Snowflake. Specify the new private key.
Using the ALTER USER command, we are able to remove the previous public key generated
alter user xyz unset rsa_public_key;
Conclusion:
Above are the steps to successfully implement key pair authentication and key rotation process using SnowSql. Thus additional authentication can be enabled for any snowflake account.
References:
1. https://docs.snowflake.com/en/user-guide/key-pair-auth.html
2. https://community.snowflake.com/s/article/How-to-verify-the-Public-Private-Key-Connectivity-using-SnowSQL