Key Pair Authentication With SnowSQL

Author: Geetika Bansal & Hima Naga Bindu Narni

This blog provided information on using key pair authentication for enhanced authentication security as an alternative to basic authentication (i.e. username and password).

Introduction

Key-pair authentication method requires a minimum of a 2048-bit RSA key pair. We need to generate the Privacy Enhanced Mail (i.e. PEM) private-public key pair using OpenSSL.

The public key is then assigned to a Snowflake user who uses the Snowflake client to connect and authenticate to Snowflake. Some of the supported Snowflake Client allows using encrypted private keys to connect to Snowflake.

Steps to be followed:

1. Install OpenSSL

To install the OpenSSL, we chose the below link:

https://slproweb.com/products/Win32OpenSSL.html

However, here are some other sources from which you can download OpenSSL for your system.

• https://www.filehorse.com/download-openssl-64/

• https://kb.firedaemon.com/support/solutions/articles/4000121705-openssl-3-0-and-1-1-1-binary-distributions-for-microsoft-windows

• https://wiki.openssl.org/index.php/Binaries

2. Set up an environment variable

3. Open the command prompt of OpenSSL

4. Generate a private key and set the encryption password (as per your choice)

openssl genrsa 2048|openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8

Note: Private key file will be created in your system

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

5. Generate the public key and enter the private key’s encryption password in the passphrase, click on Enter

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

Note: Publickey file will be created in your system

Eg: C:\Users\Admin\rsa_key (path location will be the location mentioned in the command prompt)

6. In Snowflake, set the public key by altering the user

alter user geetika set rsa_public_key= ‘xxxx’;

7. Install SnowSQL

Snowflake -> Help -> Downloads -> CLI Client (SnowSQL)

8. Enter the private key path in Snowsql config file saved in your computer after installation

9. 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>’

10. Login to Snowsql and enter the private key passphrase set in the above step.

Note: Provide the correct username while writing Snowsql command

References

1. https://domohelp.domo.com/hc/en-us/articles/360042931854-Snowflake-Using-Key-Pair-Authentication-Connector

2. https://docs.snowflake.com/en/user-guide/key-pair-auth.html