In our previous article, we have discussed about how to set up Key Pair Authentication in Snowflake. But if you wanted to connect Snowflake via Snowflake SQL REST API using key pair authentication, it expects a valid JWT (JSON Web Token).
In this article let us understand what JWT token is, how to generate it and pre-requisites to generate it.
What is JWT Token?
JWT or JSON Web Token is an open industry standard for securely transmitting information between parties as a JSON object most commonly used to identify an authenticated user.
Once the user is logged in, each subsequent request will also include the JWT. The JWT tokens are valid usually for only a certain period for about 60 minutes and they need to be regenerated after the token expired.
Pre-requisites for generating JWT Token for Snowflake Authentication
Below are the pre-requisites for generating JWT Token for Snowflake Key Pair Authentication.
- Generate Public-Private Key pair using OpenSSL.
- Assign the public key to your Snowflake user.
- The generated private key should be stored in a file and available locally on machine where JWT is generated.
Refer our previous article for more details on configuring Key Pair authentication.
Generating JWT Token using Snowflake SnowSQL
SnowSQL is a command line tool for connecting to Snowflake. Snowflake SnowSQL lets you execute all SQL queries and perform DDL and DML operations, including loading data into and unloading data out of database tables.
Refer our previous article to learn more about Snowflake SnowSQL.
SnowSQL has a parameter –generate-jwt, which would generate the JWT Token when used in conjunction with following parameters.
- -a <account_identifier> : It is the unique name assigned to your account. It can be extracted from the URL to login to Snowflake account as shown below.
- -u <username> : It is the user name with which you connect to the specified account.
- –private-key-path <path>: It is the location where the generated private key file is placed.
Below is the command to generate JWT token in SnowSQL.
snowsql --generate-jwt -a <account_identifier> -u <username> --private-key-path <path>/rsa_key.pem
The below image shows generating JWT using SnowSQL command line tool.
If the generated private key file has an encrypted password, you will be prompted to enter the password, else press enter.
Generating JWT Token using Python
JWT token can be generated using Python. Snowflake provides a readily available in-built script which can be downloaded and used for generating JWT tokens in your machine.
The script uses
cryptography python libraries for generating the token. Install these libraries in your python run time environment before running the script.
pip install pyjwt pip install cryptography
The python script sql-api-generate-jwt.py can be downloaded from Snowflake documentation page using this link.
To generate JWT, pass values to the following input arguments while running the Python script.
- account : It is the unique name assigned to your account. It can be extracted from the URL to login to Snowflake account as shown below.
- user : It is the user name with which you connect to the specified account..
- private_key_file_path : It is the location where the generated private key file is placed.
To generate JWT, run the python script with input arguments from command line as shown below.
python sql-api-generate-jwt.py --account=<account_identifier> --user=<username> --private_key_file_path=<path>\rsa_key.pem
The below image shows generating JWT using Python Script.
The JWT tokens can also be generated using Java and Node.js. Snowflake provides sample scripts in these languages to generate tokens. For more details refer Snowflake Documentation.
Subscribe to our Newsletter !!
Snowflake allows catching exceptions and their error information that occur in stored procedures by defining exceptions.
The stored procedures in Snowflake runs either with caller’s or owner’s rights which defines the privileges with which the procedure executes.
Snowflake supports Key Pair authentication which uses a combination of public-private key pair for enhanced security.