How to set up Snowflake key Pair Authentication in Spotfire

How to set up Snowflake key Pair Authentication in Spotfire

book

Article ID: KB0137610

calendar_today

Updated On:

Products Versions
Spotfire Server 14.0
Spotfire Analytics Server 14.0

Description

Key pair authentication offers a more secure alternative to traditional username and password logins. Instead of relying on static credentials that can be stolen or exposed, this method leverages asymmetric encryption to validate user identity.

In this setup:

  • The private key is generated and securely stored by the user.

  • The corresponding public key is uploaded to the user's profile in Snowflake.

During authentication, Snowflake uses the public key to validate that the connecting client possesses the correct private key, without ever transmitting the private key itself.

Resolution

Step 1: Generate Key Pair Using OpenSSL

  • For Linux: Run the following commands directly in the terminal.

  • For Windows: Install OpenSSL and execute the commands below in the Command Prompt.

Generate Private Key:

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

Generate Public Key (corresponding to the private key):

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

Step 2: Register the Public Key in Snowflake

  • Log in to Snowflake.

  • Run the following command to associate the public key with your Snowflake user:

ALTER USER <username> SET rsa_public_key='<contents_of_public_key_file>';

Step 3: Place the Private Key File

  • Copy the generated private key file to the following directory on the Spotfire server:

<installation directory>\spotfire\<version>\tomcat\certs\

Step 4: Configure JDBC Driver

 
<installation root>\tomcat\custom-ext\informationservices

Step 5: Configure Data Source Template in Spotfire Configuration Tool

  • Open the Spotfire Configuration Tool.

  • Add a new data source template for Snowflake. Below is a sample template (modify as per your specific requirements):

 
<jdbc-type-settings>
<type-name>Snowflake</type-name>
<driver>net.snowflake.client.jdbc.SnowflakeDriver</driver>
<connection-url-pattern>jdbc:snowflake://&lt;ACCOUNT&gt;.snowflakecomputing.com</connection-url-pattern> <supports-catalogs>true</supports-catalogs>
<supports-schemas>true</supports-schemas>
<supports-procedures>false</supports-procedures>
<fetch-size>10000</fetch-size>
<batch-size>100</batch-size>
<use-ansii-style-outer-join>true</use-ansii-style-outer-join>
</jdbc-type-settings>
 
Note: Ensure that the template status shows "XML Parsed OK."

Sample Connection URL:

jdbc:snowflake://<Server_Name>.snowflakecomputing.com/?private_key_file=<PATH>&authenticator=snowflake_jwt&User=<USERNAME>&private_key_file_pwd=<PASSWORD>

Step 6: Add Extra Java Parameter (Spotfire Versions 12 and Above)

Starting from Spotfire 12, Information Services run as a separate Java process. Additional Java parameters are required to enable private key authentication.

Instructions:

  1. Export the Spotfire server configuration:

    config.bat export-config

  2. Add the following JVM parameter to the Information Services Java process:

    config.bat config-external-information-services-process -A-Dnet.snowflake.jdbc.enableBouncyCastle=true

  3. Import the configuration:

    config.bat import-config -c "Added Snowflake parameter to Information Services"

  4. Restart the Spotfire server.

Step 7: Establish Connection from Spotfire Analyst

  • Open Spotfire Analyst.

  • Navigate to Information Designer and establish the Snowflake connection using the configured connection URL.

  • Refer to the example connection URL provided in Step 5.

  • image.png

Important Considerations:

  • Ensure the private key file is accessible from the Spotfire environment.
  • Confirm the Snowflake user is correctly associated with the public key.
  • Ensure the connection URL accurately references the correct path to the private key file.
  • Ensure no typos exist in the connection URL or data source template.

Issue/Introduction

This article provides a step-by-step guide to configuring Snowflake key pair authentication within Spotfire.

Additional Information

Powered by Wolken Software