LibreOffice 6.x Base MySql connection using JDBC and SSL

[Lyndhurst]

Hi,

I developed quite a large application with base, it was a big learning curve, but I am finally done with it.
All my development was on localhost using a jdbc connector and a mysql database.

I am now trying to secure the connection to deploy the database on a remote or a local server.
I cannot find much information about the ssl settings using a jdbc connector or an odbc connector.
I am using linux 18.04 clients and servers only, libreoffice Version: 6.0.7.3, and mysql server 5.7.26 .

I am worried I am going to lose weeks of hard work, or use an unsecure connection (I am not sure which is worse!)

Can anyone please point me in the right direction? Thanks.

[Ratslinger]

@Lyndhurst Please do not edit question to mark as [Solved] This should be done using the close method (lower right of question). Please correct.

Also, please tick the upper left area of answer which solved your question. It helps others to know there was an accepted answer.

[Lyndhurst]

I hope I did it right this time; I cannot accept my own answer where I tried to recap all the problems I encountered and solutions that worked for me, so I accepted your pointer. Thanks again.

[Ratslinger]

@Lyndhurst Thank You for helping keep things orderly. I believe you could not accept your own answer because your ‘Karma’ level was not high enough. Have up voted your question and now it should be enough.

[Lyndhurst]

The working steps to connect with JDBC connector with ssl.
Select Connection Type JDBC, in the url parameter, add:

Jdbc:mysql://127.0.0.1:3306/sample?verifyServerCertificate=false&useSSL=true&requireSSL=true

Replace 127.0.0.1:3306 with the url to your actual server, and replace sample with the name of the database you want to use within this server.

Setting verifyServerCertificate to false allows you to use self signed certificates. The other parameters are self explanatory and should match the settings on your server.

If you created your Base document with version 6.x and newer, your linked subforms might stop working. Solution from this post :

Edit > Database > Advanced Settings > Replace named parameters with ‘?’ (Checked)

For those interested in this post, and (like me) not understanding very well how certificates work, this might help.

After successfully connecting to the database, I was worried about not specifying the path to the clients certificate in the process. This page on the mysql website helped me understand it a little better.

To sum up what I understand ( I have no expertise, and might be wrong) there are two ways of connecting with ssl. Which way you are using depend on how you created the user (logins you are using) in the mysql server. If you did not specify upon user creation that the user has to present its own certificates, the connection will still be encrypted by the server.

If you did specify that the user has to present the certificates, then you are using what the linked page above refers to as 2-Way Authentication, and you do need to point to the ssl certificates client-side. I have no idea how to set that up with Base and would be interested if someone knew.

Thanks Ratslinger for your help.

[Ratslinger]

Hello,

Have no means to try but this may be of help:

https://razorsql.com/articles/mysql_ssl_jdbc.html

[Lyndhurst]

Thank you, this link looks promising to enable ssl within the jdbc driver class.

I do not know at all how to actually tamper with those files, or where they are exactly. I did not see neither by looking at it quickly any way to point to my ssl client certs.

I cannot test it now, but I will try in a few hours to get into it. In the meantime, any additional info will be greatly appreciated if anybody does know.

Thank you for the pointer, I will update the post if I make any progress tomorrow.

[Ratslinger]

@Lyndhurst Please only use an answer to answer the original question. Otherwise use add a comment.

[Ratslinger]

My guess is that:

Jdbc:mysql://127.0.0.1:3306/sample?verifyServerCertificate=false&useSSL=true&requireSSL=true

goes in the Datasource URL (or something similar with correct IP address).

[Lyndhurst]

I reposted my ‘non-answer’ answer as a comment, I hope it is better )

I have tested your solution, and yes, you were right. I had never used JDBC connection type, but MySQL (JDBC), so I was not familiar with that wizard.

I will recap my progress as an answer and post a new question regarding new problems I encountered.