Connecting to Snowflake using Keypair Authentication

SteveatSafe
SteveatSafe
  • Updated

FME Version

  • FME 2020.0

Introduction

This article is intended to assist FME Users to make use of a Key Pair to connect to their Snowflake Environment.  The first step is to generate a public and private key pair, likely performed by your IT Administrator involving the Snowflake DBA.  Once you have the public key, the DBA will relate this to the Snowflake database user account.  The FME user is given the public key and public key passphrase.  The FME User can then use this information when creating the Snowflake Database Connection by using the Advanced - JDBC Connection Property parameters “private_key_file” & “private_key_file_pwd”. 

For Windows OS you’ll need to install OpenSSL.  This article (link) may help you in that quest or speak to your IT or System Administrator.  

The following instructions are for macOS. You’ll need DBA access to your Snowflake Database.

The following is intended to familiarize the FME User with the necessary steps. Please refer to the Snowflake documentation for detailed steps in creating and configuring key pair authentication (link).

 

Create the Public and Private Key Pair

1. Create Private Key

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

You’ll be asked to enter a passphrase - don’t forget it!
 

2. Create Public Key

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

Enter the same passphrase from step 1.

 

Alter User - Log into Snowflake with SECURITYADMIN or higher

The DBA will relate the public key to either an existing user or create a new user.

To alter an existing user and set the rsa_public_key

 

1. Copy Public Key

Open the public key file in a text editor and copy the text between the public key delimiters.
 

2. Establish a connection to Snowflake Database as SECURITYADMIN role or higher.

Open SnowSQL or Snowflake Web UI connect to the database and run the following 'alter' statement

alter user <username> set rsa_public_key='<place public key test here>';

Example
alter user MYUSER set rsa_public_key='MIDACFMENSNOWIBISNOWgkEFAAOCAQ8AMIIBCgKCAQEAsqMPyASEkyzacwfujFuI';

 

In FME Desktop - Database Connection

Check the box next to "Advanced -Specify JDBC Connection" and populate the values.
Add two additional parameters:

  • private_key_file
  • private_key_file_pwd


Advanced JDBC Connection

Was this article helpful?

Comments

0 comments

Please sign in to leave a comment.