Qlik Talend Product: How to set up Key Pair Authentication for Snowflake in Talend Studio

Amar_Belgacem_Qlik · May 5, 2025 9:44:55 PM

This guide briefly offers a step-by-step process on how to set up key-pair authentication for Snowflake in Talend Studio at Job level

The process can be summarized in three steps:

Creating the .p12 file with Open SSL
Configuring Snowflake
Configuring Talend Studio at Job Level

Creating the .p12 File with Open SSL

The .p12 file contains both the private and public keys, along with the owner's details (such as name, email address, etc.), all certified by a trusted third party. With this certificate, a user can authenticate and identify themselves to any organization that recognizes the third-party certification.

Talend tSetKeyStore component itself can only take in .jks or .p12/.pfx format. If you are using PKCS8 format, you need to convert your p8 certs into a supported format.

Generate the key with the following command line prompt:
```
openssl genpkey -algorithm RSA -out private.key -aes256
```
This will generate a private key (private.key) using the RSA algorithm with AES-256 encryption. You'll be prompted to enter a passphrase to protect the private key.
Generate a self-signed certificate using the following command line prompt:
```
openssl req -new -x509 -key private.key -out certificate.crt -days 1825
```
This command generates a self-signed certificate (certificate.crt) that is valid for 5 years. You will be prompted to enter details like country, state, and organization when generating the certificate.
Once you have both the private key (private.key) and certificate (certificate.crt), please create the .p12 file using the following command line and name your key alias.
```
openssl  pkcs12 -export -out keystore.p12 -inkey private.key -in certificate.crt  -name "abe"
```
And check the created .p12 file information with below command:
```
openssl pkcs12 -info -in keystore.p12 or keytool -v -list -keystore keystore.p12
```

Generate a public key with the following command line:

openssl x509 -pubkey -noout -in certificate.crt > public.key

Configuring Snowflake :

The USERADMIN role is required to perform the Snowflake configuration. Open your Snowflake environment and ensure you have a worksheet or query editor ready to execute the following SQL statements. .

For this step, you will create the necessary Snowflake components—database, warehouse, user, and role for testing purposes. If you already have an existing setup or example, feel free to re-use it

-- Drop existing objects if they exist
DROP DATABASE IF EXISTS ABE_TALEND_DB;           -- Drop the test database
DROP WAREHOUSE IF EXISTS ABE_TALEND_WH;          -- Drop the test warehouse
DROP ROLE IF EXISTS ABE_TALEND_ROLE;             -- Drop the test role
DROP USER IF EXISTS ABE_TALEND_USER;             -- Drop the test user

-- Create necessary objects
CREATE WAREHOUSE ABE_TALEND_WH;                  -- Create the warehouse
CREATE DATABASE ABE_TALEND_DB;                   -- Create the test database
CREATE SCHEMA ABE_TALEND_DB.ABE;                 -- Create the schema "ABE" in the test database

-- Create the test user
CREATE OR REPLACE USER ABE_TALEND_USER
    PASSWORD = 'pwd!'                            -- Replace with a secure password
    LOGIN_NAME = 'ABE_TALEND_USER'
    FIRST_NAME = 't'
    LAST_NAME = 'tt'
    EMAIL = 't.tt@qlik.com'                      -- Replace with a valid email
    MUST_CHANGE_PASSWORD = FALSE
    DEFAULT_WAREHOUSE = ABE_TALEND_WH;

-- Grant necessary permissions
GRANT USAGE ON WAREHOUSE ABE_TALEND_WH TO ROLE SYSADMIN;   -- Grant warehouse access to SYSADMIN

CREATE ROLE IF NOT EXISTS ABE_TALEND_ROLE;                  -- Create the custom role
GRANT ROLE ABE_TALEND_ROLE TO USER ABE_TALEND_USER;         -- Assign the role to the user

GRANT ALL PRIVILEGES ON DATABASE ABE_TALEND_DB TO ROLE ABE_TALEND_ROLE;                -- Full access to the database
GRANT ALL PRIVILEGES ON ALL SCHEMAS IN DATABASE ABE_TALEND_DB TO ROLE ABE_TALEND_ROLE; -- Full access to all schemas
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ABE_TALEND_DB.ABE TO ROLE ABE_TALEND_ROLE;-- Full access to all tables in schema
GRANT USAGE ON WAREHOUSE ABE_TALEND_WH TO ROLE ABE_TALEND_ROLE;                        -- Grant warehouse usage to custom role

-- Verify user creation
SHOW USERS;

-- Create a test table and validate setup
CREATE TABLE ABE_TALEND_DB.ABE.ABETABLE (
    NAME VARCHAR(100)
);

-- Test data retrieval
SELECT * FROM ABE_TALEND_DB.ABE.ABETABLE;

For this step, please assign the public key to the Snowflake test user created earlier. To do this, you'll need do the following:
- Locate public.key and open it in an editor (such as Notepad++)
- Copy the public key displayed between BEGIN PUBLIC KEY and END PUBLIC KEY
- In the Snowflake environment, open a worksheet or query editor to run the following SQL statements. You will add the previously generated public key to our user and be sure to replace it with your own key.
```
DESCRIBE USER
```
  And to verify that the key was successfully added.
```
ALTER USER ABE_TALEND_USER SET RSA_PUBLIC_KEY=public key ';
DESCRIBE USER ABE_TALEND_USER;
```
Now we’ll verify that the configuration is correct. In your Snowflake environment, open a worksheet or query editor, run the following SQL statements, and copy the results (an sha256 hash of our public key ) into a Notepad or any text editor for reference.
```
DESC USER ABE_TALEND_USER;
SELECT SUBSTR((SELECT "value" FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))  WHERE "property" = 'RSA_PUBLIC_KEY_FP'), LEN('SHA256:') + 1);
```
Using OpenSSL, we will calculate the SHA-256 hash of the public key and compare it with the one previously generated by Snowflake to ensure they are matched.
To do that use the following OpenSSL command:
```
openssl rsa -pubin -in public.key -outform DER | openssl dgst -sha256 -binary | openssl enc -base64
```
If the hash matches, proceed to Talend Studio configuration.

Configuring Talend Studio at Job Level :

Launch your Talend Studio and drag both tSetKeyStore and tDBConnection(Snowflake) components from Palette to Designer Tab
In the Basic settings of tSetKeyStore component, enter the path to the keystore .p12 file in double quotation marks in the KeyStore file field :
Use the Key Alias set in the keystore. p12 file before for Snowflake DB Connection ("abe", for this example) :
Please test the connection to see if the key-pair authentication you set up works

Qlik Talend Product: How to set up Key Pair Authentication for Snowflake in Talend Studio