Snowflake connection with private key and passphrase

I have been provided a private key file p8 and a passphrase from my Snowflake support team. As well as all of the proper credentials/configurations for the Snowflake connector. However, I am getting this error now:

Execute failed: Private key provided is invalid or not supported: C:\Users\AEhrenwo\OneDrive - Kenvue\Desktop\rsa_key.p8: PBE parameter parsing error: expecting the object identifier for AES cipher

Any suggestions on how to troubleshoot this, and if it the issue is with the key or how it has been configured.

Note: I have reviewed all of the node documentation and believe I have things set up properly.

Interesting, @aehrenworth. I’ve yet to set our snowflake user up for private key, but was reading up on it recently, and had paused because of other deadlines, but will be needing to come back to it, so perhaps we can assist each other here :wink:

How/where are you specifying the path to the p8 key file?

In the snowflake documentation I have read, it says to use forward slashes in the path name, regardless of the OS, so have you tried it as

C:/Users/AEhrenwo/OneDrive - Kenvue/Desktop/rsa_key.p8

Hmm-- When I changed the variable to string and replaced the backslashes with forward slashes, but when I put it back to Path (the Connector parameter seems to require this) the slashes go back the other way again.



I have some good news and possibly some bad news depending on how much support you can get from your Snowflake support team, and how much you are able to do yourself!

First piece of good news is that I found when I tried it that the direction of the slashes doesn’t matter :wink:

The second piece of good news is that after much trial and error and long conversations with chatGPT, I managed to connect KNIME (5.4) to snowflake with public/private key.

The bad news is that I only managed to do it if the private key file is not encrypted. And if your support team followed the Snowflake documentation, they encrypted it, hence you have a password to put in the private_key_file_pwd.

Good news again - it is possible to create a decrypted version of the private key file without changing the public key!

Do you have openssl available? If so, in a command prompt, enter the following:

openssl rsa -in rsa_key.p8 -out rsa_key_unencrypted.p8

You’ll need to run this in the folder containing your rsa_key.p8 file, and enter the password for the .p8 file when prompted

(replace rsa_key.p8 and rsa_key_unencrypted.p8 with your original p8 filename and an output filename of your choosing, having first made sure you have a backup of the rsa_key.p8 file :wink: )

Once you have that, in your snowflake config, set private_key_file to your new filename, and completely remove (or rename) the “private_key_file_pwd” key from JDBC parameters. If the private_key_file_pwd setting is there, it will still try to decrypt.

btw, you can check if a .p8 file is actually encrypted by using the following command:

openssl rsa -in rsa_key.p8 -check

If it prompts for password it is encrypted. If it doesn’t, and it displays the file contents, then it isn’t encrypted.

Let me know how you get on. If you get further errors, I’ll see if I can figure it out. Meanwhile perhaps a KNIME team member can determine if encrypted private keys are possible with KNIME.

4 Likes

Thank you very much for this detailed overview and steps. I will see if I can replicate them and get this to work and follow up.

1 Like

Hope it works. @aehrenworth

I’ve just been reading around the issue of encrypted private keys on the DBeaver community site, where people have also had fun with snowflake.

The following setting, was introduced in the knime.ini file in the most recent version of KNIME:

-Dnet.snowflake.jdbc.enableBouncyCastle=true

5.4. changelog:

  • AP-23511: Support OpenSSL V3 private keys in Snowflake Connector node by adding the Bouncy Castle JVM argument to knime.ini file
  • AP-23114: Update Snowflake driver to version 3.20

Now from what I have gathered on the DBeaver site, is that this setting allows the Snowflake JDBC driver to use an encrypted private key file, but is only present in their driver version 3.16 and later (e.g. 3.20). The latest version of the snowflake driver present in KNIME 5.4 as can be seen from the changelog is 3.20, so it’s possible that encrypted private keys will work with that, and I shall try it at some point.

However I reverted to an earlier driver 3.14 (deprecated in 5.4) because of a reported problem with using DB Loader with Snowflake KNIME and the 3.20 driver .

This would explain why I am not seeing the encrypted private file working! As I say, I have yet to try using the 3.20 driver.

Which version of KNIME are you using, and which snowflake driver version?

1 Like

Yes, this would probably explain my issue. I am on 5.3.2 for Knime and version 3.14 of the driver. Do I need to update KNIME (we are limited based on the version of the enterprise Business data Hub) or can I simply use the 3.16 version of the Snowflake driver manually?

Update: Manually using the 3.16 driver did not have any impact on the error.

My take on it (and I’m not sure) is that KNIME 5.4 introduced the additional settings to work with latest snowflake driver both in terms of the “bouncycastle” api, and the additional setting in knime.ini.

You can add the knime.ini line I mentioned earlier manually but I don’t know if everything else is in place in KNIME 5.3.2 for that to work.

Did you manage to try with an unencrypted private key file? If you did, what happened?

I can’t recommend on updating KNIME AP given your hub version because I don’t know the answer to that. I would certainly hold off on updating 5.3.2 until the 5.4.1 release is out (soon I hope) because I have day to day problems with viewing knime tables in the 5.4 release (nothing to do with this snowflake issue) and so cannot currently recommend moving to 5.4.

2 Likes

We are not allowed to install OpenSSL (and other open-source tools like it) on our work machines. This one triggered all kinds of warnings. Not sure if there is a different way to generate an unencrypted version of the key. I have a few options on my end now that I can try so I will do a little experimenting this week and see if I can get it working.

1 Like

Hello @aehrenworth ,
I think @takbb already provided most of the information needed (thanks a lot) I will try to summarize it here.
If you can update the KNIME AP version 5.4 it should work out of the box as described in the Snowflake documentation.

However if you are not able to use KNIME AP version 5.4 or use the built-in Snowflake driver version 3.20 due to the reported problem with the DB Loader node you can do the following:

  • Manually register version 3.16 as described here. No newer version since they are all affected by the bug that makes problem with the DB Loader node
  • Add the following line to the knime.ini file which is located in the root folder of your KNINE AP installation:
    -Dnet.snowflake.jdbc.enableBouncyCastle=true
  • Once the setup is done configure the Snowflake Connector node as described in the Snowflake documentation

Bye
Tobias

2 Likes