Environment:

Spoon (PDI Client) 8.3 and above.

MongoDB Atlas (fully managed MongoDB in the cloud).

Question:

How to connect PDI (Spoon) to a MongoDB Atlas cluster using the X.509 authentication method over SSL?

This guide provides steps to configure the connection using MongoDB Input/Output and MongoDB Execute steps.

When configuring a MongoDB Atlas cluster and opting for the X.509 authentication method for your user, you either have your self-managed/issued certificate or you receive a certificate in the .pem format from MongoDB.  This certificate is essential for establishing a connection from the Spoon client to your MongoDB Atlas cluster server.

Java does not natively support the .pem format, necessitating the conversion to a compatible format such as JKS or PKCS12.  Verify whether your MongoDB Atlas certificate file consolidates both the certificate and private key in a single .pem file.  To confirm, open the .pem file in a text editor.  If the certificate and private key are indeed combined, the subsequent steps involve using a combination of OpenSSL and Java keytool to import them into the Java keystore/truststore. Here is a detailed guide on the process:

NOTE: This is different from the On-Premise MongoDB deployment.

Answer:

1. Prepare the MongoDB Certificate

• Confirm if your .pem file contains both the certificate and private key. Open it in a text editor:
  • Extract the certificate (-----BEGIN CERTIFICATE----- to -----END CERTIFICATE-----) into certificate.pem.
  • Extract the private key (-----BEGIN PRIVATE KEY----- to -----END PRIVATE KEY----) into key.pem.

2. Create a PKCS12 file with OpenSSL which includes both the certificate and the private key:

•  Here is a sample command:

openssl pkcs12 -export -in certificate.pem -inkey key.pem -out keystore.p12 -name mongodb

In this command:

certificate.pem is your extracted certificate

key.pem is your extracted private key

keystore.p12 is the PKCS12 file you're creating

mongodb is an alias for your certificate in keystore.

When you run this command you will be prompted to create a password for this PKCS12 file.  Remember this password, as you'll need it to import the file into the java Keystore.

NOTE: Right now with your current .PKCS12 certificate format you can successfully authenticate with MongoDB over SSL x509 auth. So you can skip step 3 and continue to Step 4.

However, if your organization, for whatever reason, requires you to use the .JKS certificate format then continue with step 3.

3. (Optional): Convert to JKS Format and import into truststore
If required by your organization, convert PKCS12 to JKS using the keytool:

keytool -importkeystore -srckeystore keystore.p12 -srcstoretype pkcs12 -destkeystore keystore.jks -destsrcstoretype JKS -alias mongodb

In this command:

keystore.p12 is the PKCS12 file you created

mykeystore.jks is the Java Keystore file you're creating

mongodb is the alias for your certificate

Upon running this command you will be prompted to enter the password for the PKCS12 file and to set a new password for the Java keystore.

4. Import Certificates to JVM Truststore (If Necessary)
If using a certificate not recognized by the default JVM truststore (cacerts in <JAVA_HOME_DIR>/lib/security/), you may need to import your certificate into the Java truststore.

Here is the command to import the certificate into cacerts keystore:

keytool -importkeystore -srckeystore keystore.p12 -srcstoretype PKCS12 -destkeystore <PATH_TO_TRUSTSTORE>/cacerts -deststoretype JKS

Replace <PATH_TO_TRUSTSTORE> with the actual path to your Java truststore directory. When executing this command, you might be prompted to enter the truststore password; the default password is often 'changeit.'

If you decide to use your own custom truststore and password other than the Java default cacerts truststore, you will need to include the path to your custom trustore and password in Java options in the spoon.bat file.

5. Configure Spoon to Use the Keystore

• Open spoon.bat in a text editor.
• Locate the line starting with: 

set OPT=%OPT% %PENTAHO_DI_JAVA_OPTIONS%

set OPT=%OPT% %PENTAHO_DI_JAVA_OPTIONS% "-Djava.library.path=%LIBSPATH%;%HADOOP_HOME%/bin" %JAVA_ENDORSED_DIRS% %JAVA_LOCALE_COMPAT% "-DKETTLE_HOME=%KETTLE_HOME%" "-DKETTLE_REPOSITORY=%KETTLE_REPOSITORY%" "-DKETTLE_USER=%KETTLE_USER%" "-DKETTLE_PASSWORD=%KETTLE_PASSWORD%" "-DKETTLE_PLUGIN_PACKAGES=%KETTLE_PLUGIN_PACKAGES%" "-DKETTLE_LOG_SIZE_LIMIT=%KETTLE_LOG_SIZE_LIMIT%" "-DKETTLE_JNDI_ROOT=%KETTLE_JNDI_ROOT%" "-Djavax.net.ssl.keyStore=<PATH_TO_KEYSTORE_LOCATION>\<keystore.jks or keystore.p12>" "-Djavax.net.ssl.keyStorePassword=<password>"

• Save and close the file.

6. Start Spoon, Create a Transformation and Test Connection.

• Add MongoDB Input, MongoDB Output, or MongoDB Execute steps to the canvas.

NOTE: Both these steps must be configured using the connection string option (do not use the configure fields options because X.509 has not been added to the authentication mechanism options and therefore it will not work).

• Paste your connection string in the connection string box and test connection. For example:

mongodb+srv://mongocluster.fuliwtz.mongodb.net/?authSource=%24external&authMechanism=MONGODB-X509&retryWrites=true&w=majority&tls=true

Navigate to the input options or output options tab and click on 'get DB', thereafter click the dropdown to select a DB and then click on 'get Collection' and click on the drop down to select a collection and you can preview your data.