Database with SSL

Hi,

I'm very new to Knime. I spent my weekend watching tutorials and demos of Knime. Overwhelmed by the functionality I am now trying to set up up my first workbench.

Unfortunately, I am having troubles connection Knime to my database. Currently, I use MS Access and the MySQL ODBC driver to connect to an Amazon replica instance. This works without problems.

Now I tried to do the same with Knime. I have installed the Java MySQL connector. However, I cannot figure out how to set up the SSL connection. Where can I add the certificate to connect to the database?

Somehow I assume that I'm looking atthe wrong place since sever google search has not helped me - and it usually does. Has someone either the answer or can lead me into the right direction on where to look for the answer?

Hello,

I just had a similar issue, and saw that none of these SSL user posts had been answered in the Knime Community. So here is what worked for me.

I figured out how to connect Knime to my SSL database (in this case, it was a Virtual Database called Denodo... but should be similar for other databases). You have to install the public key for your database directly into your  Knime JRE environment using the Command Line / Terminal.  Note that Knime comes with it's own JRE enviroment, so this isn't your local JRE.  Here are the instructions:

------------
Open a command prompt (preferably as administrator)
cd to the jre\bin folder under knime install directory.  The keytool.exe should be located here.

Run the following command where "cacerts location" is the path to the cacerts file under the Knime install directory 

(i.e., C:\knime\jre\lib\security\cacerts)
And the "certificate location" is the path to the public-key file on your computer (wherever you saved it).

Here's the cammand you need to run:
keytool -import -alias <alias name> -keystore <cacerts location> -file <certificate location>  -storepass changeit

When this command runs sucessfully, you should see the output:  "Certificate was added to keystore"

Then, restart Knime, and you should stop getting SSL errors when you try to query your database (assuming everything else is configured properly)

-------------

I ran this on Knime for mac, even though these are windows instructions.

On mac, the file paths are slightly different, but it still works:
keytool.exe location: /Applications/KNIME\ 3.3.2.app/Contents/Eclipse/jre/Home/bin/keytool
<cacerts location>: /Applications/KNIME\ 3.3.2.app/Contents/Eclipse/jre/Home/lib/security/cacerts

Hope this helps someone have an easier time than I did!

 

 

2 Likes

Hi redoresto,

Can you try this? Database URL in Database Connector = jdbc:mysql://:3306/<database_name>?useOldAliasMetadataBehavior=true&useSSL=true&requireSSL=true

Regards
Magomed.

I used a database connector (instead of mysql connector in my case) with a database URL ending in ?useSSL=true&requireSSL=true as mentioned by Magomed and it worked fine (Azure MySQL in this case).

1 Like

A post was split to a new topic: Database connection with SSL