Key Pair Authentication (snowsql)

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 command prompt for open SSL and then Generate private key and set 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


Eg: C:\Users\Admin\rsa_key.p8 (path location is the location in command prompt)




5) Generate public key and enter private key’s encryption password in passphrase and clickEnter


openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub



-> Public key file are created in your system




6. Now we enter in Snowflake, and set 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 private key path in snowsql config file saved in your computer after installation(config file are going to be in .snowsql folder)


-> Also enter the #password ,#username field too if error occurs



9. Now Open command prompt and give private key passphrase (the same encryption password we set up for private key)


set SNOWSQL_PRIVATE_KEY_PASSPHRASE='<passphrase>’



and then open powershell for Login to snowsql and enter private key passphrase


Note- I have used the PowerShell as I had encountered few errors while running the cmd. You can try with cmd prompt too


Note: Provide 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


15 views0 comments

Recent Posts

See All