FME Version
Summary
When following Snowflake's key-pair instructions to create an encrypted private key with OpenSSL, FME is not able to decrypt the private key properly and fails to connect to the database. If the private key was created using the -nocrypt flag, there are no issues using the private key in FME.
There are several requirements if you wish to use an encrypted private key to connect to the Snowflake Database using FME:
- Must use the Advanced Connection Parameters in the Snowflake Database Connection
- Add private_key_file and add the path to the p8 file.
- Add private_key_file_pwd and set the passphrase of the p8 file.
- It is necessary to add the JAVA_TOOL_OPTIONS environment variable for all versions of FME.
- Ensure FME is using at least version 3.16.1 or newer of the Snowflake driver (ex. snowflake-jdbc-3.16.1.jar)
- FME 2021.0 to FME 2024.0 require an update to the Snowflake driver.
- FME 2024.1 and newer do not require an update to the Snowflake Driver.
Create Environment Variable
Setting the JAVA_TOOL_OPTIONS enables Bouncy Castle to support the Snowflake driver in decrypting the private key file.
To set the the JAVA_TOOL_OPTOINS, on Windows OS, open a command window ('run as administrator') and run this command:
setx JAVA_TOOL_OPTIONS -Dnet.snowflake.jdbc.enableBouncyCastle=true -m
Restart FME Form and test.
Update Snowflake Driver
If you need to update the Snowflake driver in FME, visit the Maven Repo for the Snowflake Driver and locate the desired version, for example, 3.19.1. Inside this folder, locate the file called snowflake-jdbc-3.19.1.jar and click the link to download the file (most browsers).
- Place the new driver (.jar) in the plugins folder of the FME installation. For example, C:/Program Files/FME/plugins.
- Remove the old Snowflake driver (snowflake-jdbc-x.yy.x.jar)
- Open FME Workbench and retest the connection.
Summary of Requirements
| Version | Tested | Env Var | Driver Update | Notes |
| 2021 | Yes | Yes | Yes | Tested with v3.19.1 |
| 2022 | Yes | Yes | Yes | Tested with v3.19.1 |
| 2023 | Yes | Yes | Yes | Tested with v3.19.1 |
| 2024.0 | Yes | Yes | Yes | Tested with v3.19.1 |
| 2024.1 | Yes | Yes | No | Comes with v3.19.0 |
| 2024.2 | Yes | Yes | No | |
| 2025.0 | Yes | Yes | No |
* Improvements coming for 2025.1
Caveats
- To use an encrypted private key in FME will require setting the passphrase when generating the private key file with OpenSSL. The passphrase is set to the value of private_key_file_pwd in the connection.
- Ensure OpenSSL v3.x is used to create the private and public keys
- You may wish to check the 'Mask Connection String' in the Database Connection when using the Advanced Connection Parameters.
FME Flow Considerations
- Ensure the path where the key file is stored is accessible by the Windows Services 'Log on As' user. This could be LOCALSYSTEM or a domain service account.
- At this time (FME Flow 2025.0.0 and older), there is an issue referencing the FME Flow Published Parameters like $(SHAREDRESOURCE_DATA), so this cannot be used in the private_key_file connection property. Use a full path instead. i.e. c:\mysafe\private-key.p8.
- You may wish to tighten the security on the folder where the private key is stored.
- For Linux installs of FME Flow, be sure to reduce the permissions on the p8 file to read-only; otherwise, you'll receive errors accessing the file.
- Consider checking the 'mask connection details' in the Database Connection to hide details that are printed to the log file.
- private_key_file_pwd is not printed out in the connection string in the FME Flow Job Log regardless of the 'mask connection details' settings.
- The same environment variable, JAVA_TOOL_OPTIONS will work for FME Flow Engines.
Additional Information
Please follow the Snowflake Documentation for creating the private and public keys, as well as assigning to the Snowflake user that will be used in the key-pair authentication.
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 private key pair and public key, likely performed by your IT Administrator involving the Snowflake DBA. The DBA will relate the public key to the Snowflake database user account. The FME user is given the private key and private key passphrase. The FME User can then use this information when creating the Snowflake Database Connection by using the Advanced - JDBC Connection Property parameters, adding “private_key_file” & “private_key_file_pwd”.
To generate keys on Windows OS, you’ll need to install OpenSSL. This article may help you in that quest or speak to your IT or System Administrator. For stronger encryption, try to use a newer 3.x version of OpenSSL. Cygwin can also be extended by installing the OpenSSL package.
If you have macOS or Linux, OpenSSL is likely readily available in the command line/terminal. However, you’ll need DBA access to the Snowflake Database to update the user's rsa_public_key field.
Please refer to the Snowflake documentation for detailed steps in creating and configuring key-pair authentication.
In FME Form - Database Connection
Check the box next to "Advanced -Specify JDBC Connection".
Review the 'Connection String' and set the correct <accountname>.
Populate the default and required connection properties (see below).
Delete the password connection property. It is not required when using key-pair.
Add two additional connection properties:
- private_key_file
- private_key_file_pwd
Troubleshooting
Private key not supported 1
Private key provided is invalid or not supported:
/path/to/private_key/rsa_key.p8:
PBE parameter parsing error: expecting the object identifier for AES cipher
This indicates that the encryption for the private key requires bouncy castle to be enabled.
Ensure the JAVA_TOOLS_OPTIONS is properly set by reviewing the system Environment Variables. Open a command line and type set and the enter key. You should see a list of output similar to the following. Confirm the JAVA_TOOL_OPTIONS appears as below.
Restart FME Form and test again. It the issue persists please contact Safe Software Support.
Private key not supported 2
Example Error: FME Desktop 2021.1.2 (snowflake-jdbc-3.12.17.jar)
net.snowflake.client.jdbc.SnowflakeSQLLoggedException:
Private key provided is invalid or not supported: C:\SnowKeyPair\support_key_1.p8:
ObjectIdentifier() -- data isn't an object ID (tag = 48)
Solution
Update Snowflake Driver to at least snowflake-jdbc-3.16.1.jar or newer.
Private key not supported 3
Example Error: FME Form 2023.0.3
net.snowflake.client.jdbc.SnowflakeSQLLoggedException:
Private key provided is invalid or not supported:
C:\SnowKeyPair\support_key_1.p8: C:\SnowKeyPair\support_key_1.p8
The above error may indicate that a private key file was not found. Confirm the location of the file. If you are using Linux, confirm that the permissions are properly set to on the file; read-only is required.
Bad or Missing Connection Parameter
net.snowflake.client.jdbc.SnowflakeSQLLoggedException:
Invalid parameter value null for parameter type {1}.
This usually indicates that the driver cannot find the path to the private key file or there is an invalid connection property.
Comments
We are currently working on improving key-pair authentication for Snowflake in FME. Please let us know how we can help make this better for you. (updates coming spring 2025).
Comments
7 comments
I'm not able to get this to work even with unencrypted key. I keep getting the error “net.snowflake.client.jdbc.SnowflakeSQLLoggedException: Missing password” But if I add a Snowflake password in the Advanced connection property area it connects.
Please share the version of FME you are using… the OS you are using. Have you tested the Key Pair in SnowSQL or another tool?
I'm assuming you are creating a new connection… when you click “Test…” button, could you then click on the "Show Log…" button and review the contents?
You should see a line like the following:
Snowflake Non-Spatial Reader: Creating connection using connection string 'jdbc:snowflake://<mysnowflakeaccount>.snowflakecomputing.com/' and properties '{schema=PUBLIC, private_key_file=/Users/<my user>/Snowflake/pem_user.p8, private_key_file_pwd=F6<redacted>W9R, role=<TEST>_RL, application=SafeSoftwareFME, GEOGRAPHY_OUTPUT_FORMAT=WKB, warehouse=<TEST>_WH, user=<PEM_USER>, GEOMETRY_OUTPUT_FORMAT=EWKB, db=<TEST>_DB}'If you see more than I have above in the example connection string, tweak it and test again. This was tested using 2024.1.4 on macOS.
Does this also work from FME Flow? These instructions seem fairly easy to follow for FME Form, but it feels like a missing step without information about uploading this to an FME Flow server.
I did get it to work from Form. It was a Snowflake test user privilege issue. And no I cannot publish and run it from Flow either. What what that take? The error on Flow…
“COM.safe.fme.jdbc.TranslationFailureException: net.snowflake.client.jdbc.SnowflakeSQLLoggedException: Private key provided is invalid or not supported: \Users\myuser\rsa_key.p8: \Users\myuser\rsa_key.p8”
Can you confirm the versions of FME Flow and FME Form are the same.
Recall that those files need to be accessible by the FME Engine. Is it running as LOCALSYSTEM or a service account? Either way, the engine needs to access those to get in. You are close.
It may make sense for you to open a ticket at https://www.safe.com/support for us to flush out the details.
Hi, I have and issue trying to connect to snowflake DB using keypair authentication, it is not recognising my schema . I'm using FME(R) 2024.1.2.1 (20240906 - Build 24624 - WIN64). I can connect fine in DBeaver using the keypairs. I'm using the advance JDBC settings to add the parameters in a new snowflake database connection. The error I get is ' COM.safe.fme.jdbc.TranslationFailureException: java.lang.IllegalStateException: Connection established with no current schema. Make sure that schema 'dbo' specified in connection settings exists and is accessible by the user ‘ . I know you need to have the case correct in the parameter I have checked with the DBA and have been told that the schema is definitely lowercase. I can’t show all the jdbc connection string, but this is the schema bit ‘ schema=dbo ’ so it is passing the lowercase parameter. Again, I am using the lowercase in DBeaver and connecting fine.
Please sign in to leave a comment.