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 environment variable

3. Open command prompt of openssl

4. Generate private key and set 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 command prompt)

5. Generate public key and enter private key’s encryption password in 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 command prompt)

6. In Snowflake, set 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 private key path in snowsql config file saved in your computer after installation

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

set SNOWSQL_PRIVATE_KEY_PASSPHRASE='<passphrase>’

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

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