How to connect with SSL

Covers details on how configuring Transformations in Pentaho Spoon
Post Reply
hudatolah
Site Admin
Posts: 143
Joined: Thu Apr 04, 2013 8:10 pm
Are You a Headhunter?: Affirmative
Surfer?: Yes

How to connect with SSL

Post by hudatolah » Thu Mar 05, 2020 11:44 am

First you need import a certificate that is associated with the root authority of the environment you're in.
For example if that is BLACKHOLESURFER.COM then export that root cert from the Microsoft Management tool for Certificates (mmc)
Look for a youtube video on how to do that, there is plenty of it out there, but the important part is that you should pick the first option for the export. DER encoded binary 509 (this should match the way the cert was created so it's not a "defaut").

Export this as .cer extension.

Find the java that runs Spoon. Given that the Spoon bat is bit convoluted, just run the patch file from a command line once and it will show the java path. Go to that folder and change dir to jre\lib\security. You should see a cacert (Central Authority certificate store) already there.
See what certs it has:

keytool -list -keystore cacerts -storepass changeit

NOTE: ("changeit" is the default password and from what I've seen out there nobody ever changes it because the Change Request procedure to do it is painful and you can RAAAARELY find anyone in the company that actually knows what that is. )

If you don't see your CA cert in there (the list maybe very long) then you'll need to import it.
Spoon should also give an error when you Test the connection:
Error: Caused by: javax.net.ssl.SSLHandshakeException: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find certification path to requested target

This tells you with certainty that your cert is not found and you need to do it (again, right, or what ever you did to break it).
In my recent use case I was connecting to DB2 and it gave me a specific IBM code: ERRORCODE=-4499, SQLSTATE=08001.

OK, so how to do it:

keytool -import -alias BlackHoleSurfer file bhs.cer -keystore cacert -storepass changeit


NOTEs:
1. "changeit" is the default and from what I've seen out there nobody ever changes it because the Change Request procedure to do it is painful and you can RAAAARELY find anyone in the company that actually knows what that is.
2. "-alias" is for YOU to remember what the alias was. You can use it to search for it after you import it.

OK, now comes the grueling java loader change that makes sure you're loading the proper stuff.
Edit the Spoon.bat and reference the certificate store in the last line.
Spoon will load by a batch file but the actual "KETTLE" loader is done in armonitor.cfg for each(!) AR Server in the server group, QA, Prod and Dev if you want to give it proper validation testing.

Running the Spoon.bat from a CLI will show this output:

start "Spoon" "C:\Java\JDK\jre\bin\java.exe" "-Xms1024m" "-Xmx2048m" "-XX:MaxPermSize=512m" -Djsse.enableSNIExtension=false "-Djavax.net.ssl.trustStore=C:\Java\JDK\jre\lib\security\cacerts" -Djavax.net.ssl.trustStorePassword=changeit "Dhttps.protocols=TLSv1.2"

That's almost all of it, but there is a chance that you will need to add an authentication file (for MSSQL authentication for example) into the data-integration\libswt\win64 folder. I rarely find someone that knows what I am talking about. Mostly people just want to make it complicated for your because they don't understand it. They see "security" or "authentication" and go into panic mode and start laying obstacles in your way like peer review and validation testing. The problem here is that since your project needs to make progress, so you should probably start with this first.
In the end, nobody will want to talk to you about it because "bigger" problems will end up on their plate and these jdbc driver issues really don't having anything to do with breaching security. If the attacker is on the system already then a locally deployed certificate is the least of your worry.

With that said. With this you should be ready to test with SSL (assuming you know the Database name, UserId, Host, Port and password of the database).

Good Luck! ;)
The Blackholesurfer. My surfboard has teeth.

Post Reply