Hello, I think I have the client key and cert added correctly, not certain but I followed the instructions from Connection to MySQL with SSL (.pem + .key) posting. I also have the server-ca.pem file. I tried on the command line: keytool -import -file mysqlserver-ca.pem -alias cloudCA -keystore cloudTrustStore and I’m attaching the configuration of the node. I’ve tried it with and without the sslMode in the list. Console says: Execute failed: Access denied for user ‘myuser’@‘myIP’ (using password: YES) I have MySQLWorkbench installed and connect to the cloud server with ssl on this computer. Thanks for the help. 
Hello cmerron,
do you use the same user to connect from MySQLWorkbench and KNIME? The error message indicates that myuser is not allowed to log into the DB server from myIP. For more details on this have a look at the https://dev.mysql.com/doc/refman/8.0/en/alter-user.html statement of MySQL.
Bye
Tobias
P.s. You can also use the Credential Configuration node to enter the client and trust store password and then select these variables in the JDBC Parameter tab by changing the Type to Credential - passwords (see tool tip) as described here.
2 Likes
Thank you. Yes, I’m using the same user and password as I do in MySQLWorkbench with a successful login. With that app, I just point to my client-cert.pem, client-key.pem and server-ca.pem. Everything works.
I would like to connect in the simplest way first. So, maybe I should add the credential node once I get this working? I put the MySQL username and password in the MySQL Connector, Connection Settings. Then I put the path to the keystore and the truststore along with the password for them in the JDBC Parameters window as I showed.
The example that you link me to is for H2 and not MySQL, I don’t know if they are the same because it doesn’t match the name:value pairs I have found for MySQL ssl and I show in the image I attached. I replaced the ones I had and added those without success. I’m on a mac, I think I have the correct syntax for the url value, maybe that is wrong? Since I’m on a mac, I don’t see the knime.ini file either. Do I have one? I’m connecting to google’s cloud sql, so I did create the p12 key to put in the keystore. Do I need the Google API node or some other feature to connect? Other ideas?
I did find Knime.ini and added:
-Djavax.net.ssl.keyStore=/Library/MySQL/keystore
-Djavax.net.ssl.keyStorePassword=mypassword
I removed the keystore parameters, restarted knime and it still fails with the same message. My configs now look like the attached.
Thanks
Hi,
the example I pointed to was a dummy example on how to set the JDBC parameter. According to the MySQL documentation the JDBC parameter should be like in your first screenshot e.g.:
clientCertificateKeyStoreUrl=file: file:path_to_keystore_file
clientCertificateKeyStorePassword= mypassword
trustCertificateKeyStoreUrl=file: file:path_to_truststore_file
trustCertificateKeyStorePassword= mypassword
If you connect to a MySQL Server 8.0.12 and earlier you also need to use this additional parameter:
useSSL=true
You can also set all trust and key store parameters via the knime.ini file e.g.:
-Djavax.net.ssl.keyStore= path_to_keystore_file
-Djavax.net.ssl.keyStorePassword= mypassword
-Djavax.net.ssl.trustStore= path_to_truststore_file
-Djavax.net.ssl.trustStorePassword= mypassword
The error message still indicates to a problem with the user rights. Can double check that the user you try to connect has the right to do so from the IP address or host name of the machine KNIME Analytics Platform is running on by executing the following query:
SELECT user,host FROM mysql.user;
Bye
Tobias
1 Like
I am using the exact same user, password, host, computer IP, network connection that works with mysqlworkbench to connect to google’s cloudsql. In order to connect to that db I have to allow my computer’s ip to connect to the server, which I have done successfully.
I’m trying to get started with knime and database functionality. Can you describe where I’m supposed to do the query? In mysqlworkbench, the query returns the user that I’m using with % as the host.
To thoroughly check what I’ve done, here are my steps:
I downloaded mysql connector java 5.1.48
I ran these in terminal:
openssl pkcs12 -export -in mysqlclient-cert.pem -inkey mysqlclient-key.pem -name “mysqlclient” -passout pass:mypassword -out client-keystore.p12
keytool -importkeystore -srckeystore client-keystore.p12 -srcstoretype pkcs12 -srcstorepass mypassword -destkeystore keystore -deststoretype JKS -deststorepass mypassword
keytool -import -file mysqlserver-ca.pem -alias cloudCA -keystore cloudTrustStore
Just in case, I did a chmod on cloudTrustStore and keystore to be read and execute to all.
My current config for the connection on mac to mysql is in the images.
Separately, I can connect to a local mysql database through knime. This local db doesn’t need any certificates, very simple connection.
The cloud and local mysqls are v5.7
This all looks good to me. So we need to get more information what the problem is. To do so we can enable logging of the network interaction by adding the following line to the knime.ini file:
-Djavax.net.debug=all
Once this is added start KNIME Analytics Platform via the command line since the network log messages will only be visible in the console.
Bye
Tobias
1 Like
Console shows this:
ERROR MySQL Connector 0:1 Execute failed: Cannot open file:/Library/Mysql/keystore [DerInputStream.getLength(): lengthTag=109, too big.]
Tobias, I tried to remove the length error response to you, but it is still there. I got that message because I had left some test lines in the JDBC Parameter tab. Anyway, with the config I posted last time, I’m still getting the same message in the console. ERROR MySQL Connector 0:1 Execute failed: Access denied for user ‘myuser’@‘myIP’ (using password: YES)
Here’s the final lines from the log file in the attached.end of log.txt (3.0 KB)
Could I possibly get in touch with another user that connects Knime to Google CloudSQL? It doesn’t seem that you can help me via forum. If you could set up a meeting to discuss, screenshare or other way to move this forward, maybe that would be an alternate route to success? Please let me know any options for help? Thank you!
Hello cmerron,
I’m sorry but I can not setup a meeting via KNIME forum. To do so you need to contact the KNIME support.
It seems that MySQL also shows the Access denied error message if the client side ssl setup is not correct according to this post (point 6).
So can you please double check that the ssl settings are set correctly as explained in my previous post.
Make sure to enable the network debugging as described in my previous post.
If you still get the keystore exception Java can not use the keystore file you are providing. You can test the keystore file using the Java keytool. Which is part of the JRE that come with KNIME. The keytool is located in <KNIME_INSTALLATION>\plugins\org.knime.binary.jre.win32.x86_64_1.8.0.202-b08\jre\bin
Try to list all available certificates via
keytool -list -keystore keystore.jks
Bye
Tobias
2 Likes
Tobias, Thank you for your help. I did finally get it working. The final configuration that works is to have the Connector’s JDBC Parameter with only useSSL=true. All the other parameters work when moved into the Knime.ini file instead of the JDBC Parameter list.
-Djavax.net.debug=all
-Djavax.net.ssl.keyStore=/Library/MyCerts/keystore
-Djavax.net.ssl.keyStorePassword=mypass
-Djavax.net.ssl.trustStore=/Library/MyCerts/truststore
-Djavax.net.ssl.trustStorePassword=mypass
I also found that the mac’s keychain access app showed the google cloud sql server CA was highlighted as not trusted. I changed that to always trust. I don’t know if that had any impact, but it works now.
Cheers!
3 Likes
Hi cmerron,
I’m glad it works now. Closing this thread.
Bye
Tobias
1 Like