Talend Job using key pair authentication for Snowflake fails with a ‘Missing Keystore location’ exception

[TalendSolutionExpert]

Running a Talend Job using a key pair authentication for Snowflake fails with the exception:

Starting job Snowflake_CreateTable at 09:21 19/07/2021. [statistics] connecting to socket on port 3725 [statistics] connected Exception in component tDBConnection_2 (Snowflake_CreateTable) java.lang.RuntimeException: java.io.IOException: Missing Keystore location at edw_demo.snowflake_createtable_0_1.Snowflake_CreateTable.tDBConnection_2Process(Snowflake_CreateTable.java:619) at edw_demo.snowflake_createtable_0_1.Snowflake_CreateTable.runJobInTOS(Snowflake_CreateTable.java:3881) at edw_demo.snowflake_createtable_0_1.Snowflake_CreateTable.main(Snowflake_CreateTable.java:3651) [FATAL] 09:21:38 edw_demo.snowflake_createtable_0_1.Snowflake_CreateTable- tDBConnection_2 java.io.IOException: Missing Keystore location java.lang.RuntimeException: java.io.IOException: Missing Keystore location at edw_demo.snowflake_createtable_0_1.Snowflake_CreateTable.tDBConnection_2Process(Snowflake_CreateTable.java:619) [classes/:?] at edw_demo.snowflake_createtable_0_1.Snowflake_CreateTable.runJobInTOS(Snowflake_CreateTable.java:3881) [classes/:?] at edw_demo.snowflake_createtable_0_1.Snowflake_CreateTable.main(Snowflake_CreateTable.java:3651) [classes/:?]

Cause

The Keystore path is not configured correctly at the Job or Studio level before connecting to Snowflake on the metadata and using the same metadata connection in the Jobs.

Resolution

To use key pair authentication for Snowflake, they Keystone settings must be configured in Talend Studio before connecting to Snowflake.

Configuring the Keystore at the Studio level

Perform one of the following options.

Option 1:

Update the appropriate Studio initialization file (Talend-Studio-win-x86_64.ini,Talend-Studio-linux-gtk-x86_64.ini,or Talend-Studio-macosx-cocoa.ini depending on your operating system), with the following settings:

-Djavax.net.ssl.keyStore={yourPathToKeyStore}
-Djavax.net.ssl.keyStoreType={PKCS12}/{JKS}
-Djavax.net.ssl.keyStorePassword={keyStorePassword}

Option 2:

1. Update the Keystore configuration in Studio SSL preferences with the required Path, Password, and Keystore Type.

   

   
    

2. Add the Key Alias to the Snowflake metadata.

   

    

Configuring the Keystore at the Job level

Update the tSetKeystore components in your Job, if you plan to run the Job when the target execution is local, Remote Engine, or JobServer (the versions do not matter). Before selecting the Key Pair option for the tSnowflakeConnection component, configure the key pair authentication on the Basic settings tab of the tSetKeystore component:

1. Select JKS from the TrustStore type pull-down list.

2. Enter " " in the TrustStore file field.

3. Clear the TrustStore password field.

4. Select the Need Client authentication check box.

5. Enter the path to the Keystore file in double quotation marks in the KeyStore file field.

6. Enter the Keystore password in the KeyStore password field.

   

[jdela]

Hi Jamie - I tried both the options you listed above, however i am still getting the same error message. Need help!

Thanks

Jodi

[Xiaodi_Shi]

Hello @jdela 

Could you please indicate in which Talend version/edition you are? What does your certificate file look like? Is it in a correct format?

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

Best regards

Sabrina

[jdela]

Hi Sabrina,
My answers in Blue below

Original

Hello @jdela Could you please indicate in which Talend version/edition you are? What does your certificate file look like? Is it in the correct format? Note: Talend tsetkeystore component itself can only take pkcs12 or a jks component, I have attached a document that our team created, that has detailed steps on how to create a keystore and import the cert for Snowflake. Best regards Sabrina
I am using Talend version 7.3.1
I am assuming when you say certification file it is snowflake private file , it ends with .p8
Yes, I am using JKS in tsetkeystore component
I did not see any attachment with detailed steps in the email
Thank you
Jodi

[Xiaodi_Shi]

Hello @jdela 

As the tSetKeyStore component doesn’t accept the PKCS8 format and only takes in .jks or .p12/.pfx format, suggesting to convert your p8 certs into a supported format.
Please see https://knowledge.digicert.com/solution/SO26449.html

Best regards

Sabrina

[MdFazil]

Hi,

I tried uploading the key at Studio's preference. But the SSL certificate is not uploaded, and I have one more doubt here, which is in the "KEY ALIAS" at snowflakes metadata connection what do I need to provide after uploading the key store? Any reply would help. Thanks in advance. 

Regards

Fazil M

[Xiaodi_Shi]

Hello @MdFazil 

Key Alias field is available if Key Pair is selected from the Authentication Type drop-down list and it is the alias name associated with your KeyStore.

Please take a look in the documentation of the component :

https://help.talend.com/r/en-US/8.0/snowflake/tsnowflakeconnection-standard-properties

As it's described, you need first to create your keystore and use another component to load it

https://help.talend.com/r/en-US/8.0/keystore/tsetkeystore

When you create your keystore, normally you create it using an alias.

Feel free to let me know if it helps.

Best regards

Sabrina

[MdFazil]

Hi @Xiaodi_Shi 

I want to connect using the Key-pair authentication. As described in this thread, I have configured tKeystore to hold the keys and connected. But, I'm struck at the place where the Key alias need to be given. Can you please tell me how can I set the Key alias or where can I find the alias to enter in the field. Help is appreciated. Thanks

Regards
Fazil M

[Xiaodi_Shi]

Hello @MdFazil 

Do you have Certificate, Certificate Authority and Private key files?

You need to provide the required key information when creating a new Keystore file at first step.

For example:

keytool -genkey -alias mydomain -keyalg RSA -keystore KeyStore.jks -keysize 2048

Secondly, Import the certificate to that keystore 

keytool -import -trustcacerts -alias mydomain -file mycompany.crt -keystore KeyStore.jks

 Don't hesitate to post your issue here.

Best regards

Sabrina

[MdFazil]

Hello @Xiaodi_Shi 
Thank your for the reply.

I have been trying to load SSL file, but failed in the process and yes  have both private key and truststore files(I'll attach the screenshot) after following all these steps, I get error like reject type tag 45. I don't know what can be the cause. 

Starting job OnBoardingDemoJob at 09:58 22/03/2024.
[statistics] connecting to socket on port 3650
[statistics] connected
Exception in component tDBConnection_1 (OnBoardingDemoJob)
java.lang.RuntimeException: java.io.IOException: toDerInputStream rejects tag type 45
	at talend_snowflake_streaming.onboardingdemojob_0_1.OnBoardingDemoJob.tDBConnection_1Process(OnBoardingDemoJob.java:679)
	at talend_snowflake_streaming.onboardingdemojob_0_1.OnBoardingDemoJob.tSetKeystore_1Process(OnBoardingDemoJob.java:484)
	at talend_snowflake_streaming.onboardingdemojob_0_1.OnBoardingDemoJob$5.run(OnBoardingDemoJob.java:1369)
[FATAL] 09:58:23 talend_snowflake_streaming.onboardingdemojob_0_1.OnBoardingDemoJob- tDBConnection_1 java.io.IOException: toDerInputStream rejects tag type 45
java.lang.RuntimeException: java.io.IOException: toDerInputStream rejects tag type 45
	at talend_snowflake_streaming.onboardingdemojob_0_1.OnBoardingDemoJob.tDBConnection_1Process(OnBoardingDemoJob.java:679) [classes/:?]
	at talend_snowflake_streaming.onboardingdemojob_0_1.OnBoardingDemoJob.tSetKeystore_1Process(OnBoardingDemoJob.java:484) [classes/:?]
	at talend_snowflake_streaming.onboardingdemojob_0_1.OnBoardingDemoJob$5.run(OnBoardingDemoJob.java:1369) [classes/:?]
[statistics] disconnected

Job OnBoardingDemoJob ended at 09:58 22/03/2024. [Exit code  = 1]

I have also uploaded the tKeystore configuration here

 Can you please provide me with any solution, Thanks in advance.


Regards 

Fazil M

[Xiaodi_Shi]

Hello @MdFazil 

We see you've created a topic on talend technical forum and we could move there for further investigation on your own use case.

https://community.qlik.com/t5/Design-and-Development/Couldn-t-connect-Snowflake-to-Talend-through-SS...

Best regards

Sabrina