MariaDB 10.4.13 SSL Connect Failed

Aurelian_N · May 25, 2020, 12:09pm

Greetings,

I assume that this could be an old XOJO driver incompatibility but I need to make this connection and I need to know if it is possible in XOJO or not.

I did all the setup, created all the certificates, installed all on server side and in XOJO project side and once I try to connect I get the error [quote]SSL connection error: Unable to get private key[/quote]

In the client side I have the CA Cert file, Client Cert and Client Key all as PEM format but so far nothing works.

Latest Public XOJO , MacOS

As sample server config , you can find it here

Thanks in advance.

Attilio_Punzi · May 25, 2020, 6:15pm

I have a webapp that’s using mariadb 10.4.13 without a problem. I had to enable the other versions of TLS (tls_version = TLSv1.0,TLSv1.1,TLSv1.2,TLSv1.3) because the windows version did not connect. Have you tried to connect with another client and it works?

Aurelian_N · May 25, 2020, 7:10pm

Well apparently it works fine with same files in DBeaver but not here and I found that while https://documentation.xojo.com/api/databases/mysqlcommunityserver.html#mysqlcommunityserver-sslenabled exists in the Documentation everywhere, even in the Offline, apparently it does not work, you need to change it to db.IsSSLConnection which is not documented anywhere, so I guess some Updating in the documentation should be required asap.

So far no luck in my part .

Attilio_Punzi · May 25, 2020, 7:39pm

No, I think the documentation is right. SSLEnabled is used to activate the SSL connection, while IsSSLConnection returns you (after establishing the connection) if it is really on SSL or not. Unfortunately if you tell me that everything works with DBeaver I don’t have much advice to give you… Check that the certificates are in -----BEGIN RSA PRIVATE KEY— ----END RSA PRIVATE KEY— format. Is it possible that the path of the certificates in xojo’s path is wrong? Try putting the certificates in the document folder and use SpecialFolder.Documents.child(“name.pem”).

Aurelian_N · May 25, 2020, 8:23pm

I believe the docs are wrong, I’m using 2019R3.1 so far and if I type db.SSLEnabled I get a compiler error [quote]wMain.pbConnect2DB.Action, line 8
Type “MySQLCommunityServer” has no member named “SSLEnabled”
db.SSLEnabled = True
[/quote]

while if I put it as db.IsSSLConnection it tries to connect to the server but still I get an Unknown error . certificates are right as they get generated by the app while opening it and I test same certificates in the DBeaver and works ok .

I’ll see in the morning what other options I could have.

Thanks.

Attilio_Punzi · May 25, 2020, 8:31pm

Ok maybe I get it…If you are not using the version with the new 2.0 framework you have to use:

link

db.SSLMode = True

Aurelian_N · May 26, 2020, 6:19am

[quote=490065:@Attilio Punzi]Ok maybe I get it…If you are not using the version with the new 2.0 framework you have to use:

link

db.SSLMode = True[/quote]
It did worked with this but unfortunately same error [quote]SSL connection error: unknown error number[/quote]

Aurelian_N · May 26, 2020, 8:32am

Apparently I did tried on another pc and it works perfectly but from linux to linux with same certificates .

on Mac I see a change if I put this as cipher [quote]cipher = “ECDHE-RSA-AES256-GCM-SHA384”[/quote] I get as error [quote]SSL connection error: Failed to set ciphers to use[/quote] so what ciphers I can use on Mac for this ?

Attilio_Punzi · May 26, 2020, 9:15am

The cipher is not necessary, try to remove it. As I told you I had problems on windows because MySQLCommunityServer was trying to connect with the TLS1.x version that was disabled on the server. After I enabled all TLS protocols and restarted the mariadb service everything worked.

Aurelian_N · May 26, 2020, 9:20am

Well I did same as you with same error with ciphers disabled I get [quote]SSL connection error: unknown error number[/quote] but on the server side no details about error, just simply it disconnects the client from the server , so either something changed on the latest MariaDB that affected this or there is something fishy on the XOJO side on the driver. and while checking errors indeed on API 2.0 I get that db.SSLMode is deprecated and that I should use db.IsSSLConnection

Can you please share the config on your side on MariaDB ? maybe I’m doing something wrong but I doubt , it works on DBeaver, it works on other linux machines connecting to the server so the issue is only on the XOJO apps.

And moving the certs on Documents folder does not help either.

I’ll try installing an older version the one that comes with the repos to see if I get same issue or not.

Thanks .

Attilio_Punzi · May 26, 2020, 1:57pm

Sorry, but I was busy. The problem you have with SSLMode and SSLEnabled commands is that the 2.0 framework has not been released for all platforms. So SSLMode is deprecated for the Desktop version but not for the web version (The Webframework 2.0 will be released with the new 2020r1 release this year). Surely the problem is in the xojo code but with the information you gave me I don’t know how to help you.

My mariadb configuration is on centos7 and I left all the default values except some customization. For example:

ssl-ca = /etc/my.cnf.d/ssl/ca-cert.pem
ssl-cert = /etc/my.cnf.d/ssl/server-cert.pem
ssl-key = /etc/my.cnf.d/ssl/server-key.pem
tls_version = TLSv1.0,TLSv1.1,TLSv1.2,TLSv1.3
innodb_buffer_pool_size=1G
innodb_io_capacity=4000

but when you say it works on linux do you mean the xojo version of linux or another client?

Aurelian_N · May 26, 2020, 4:44pm

[quote=490135:@Attilio Punzi]Sorry, but I was busy. The problem you have with SSLMode and SSLEnabled commands is that the 2.0 framework has not been released for all platforms. So SSLMode is deprecated for the Desktop version but not for the web version (The Webframework 2.0 will be released with the new 2020r1 release this year). Surely the problem is in the xojo code but with the information you gave me I don’t know how to help you.

My mariadb configuration is on centos7 and I left all the default values except some customization. For example:

ssl-ca = /etc/my.cnf.d/ssl/ca-cert.pem
ssl-cert = /etc/my.cnf.d/ssl/server-cert.pem
ssl-key = /etc/my.cnf.d/ssl/server-key.pem
tls_version = TLSv1.0,TLSv1.1,TLSv1.2,TLSv1.3
innodb_buffer_pool_size=1G
innodb_io_capacity=4000

but when you say it works on linux do you mean the xojo version of linux or another client?[/quote]
Hello, no worries, same here, I did tested other apps on other linux machines, in my case Debian 10 , I will try to build a quick web app to see if connecting from one web app on another machine will work, but in my case I do need to use it on MacOS so while that could validate or invalidate the working on linux in the end I will need it to work on Mac.

I guess I’ll have to do another test and if this one fails as well in the worst case I’ll have to find another framework or platform that is more flexible and does not have so many bugs and restrictions, hard decision but a needed one in the end, We cannot tell to the customers stay with us 6 more months until Xojo fixes their bugs and loosing customers is bad for us as well.

Thanks again.

Attilio_Punzi · May 26, 2020, 5:07pm

Xojo has a lot of bugs but I think the problem here is your code and not a bug (I don’t work for xojo and I understand your point of view, so you’re free to change if you want). Could you provide a sample file with your connection code? I can try to modify the access data and see if it connects to my database.

Obviously if you are trying to connect a mysql database with an ios project this is not supported but I took it for granted.

You can connect to MySQL from Desktop, Web and Console projects, but not iOS projects.

Aurelian_N · May 26, 2020, 5:29pm

Sure, have a look here

I do hope that is just something wrong on my side.

Thanks again.

Attilio_Punzi · May 26, 2020, 5:52pm

ok the problem is in the file reference after generation. Instead of using:

syDB.IsSSLConnection = True
syDB.SSLKey = CK
syDB.SSLCertificate = CC
syDB.SSLAuthority = CAC

test:

syDB.SSLMode = True
syDB.SSLKey=SpecialFolder.Documents.Child("CAFiles").Child("ck.pem")
syDB.SSLCertificate =SpecialFolder.Documents.Child("CAFiles").Child("cc.pem")
syDB.SSLAuthority  =SpecialFolder.Documents.Child("CAFiles").Child("ca.pem")

everything works. Obviously it fixes the file paths according to how you generate them. I removed your AC folder.

Aurelian_N · May 26, 2020, 6:01pm

[quote=490160:@Attilio Punzi]syDB.SSLMode = True
syDB.SSLKey=SpecialFolder.Documents.Child(“CAFiles”).Child(“client-key.pem”)
syDB.SSLCertificate =SpecialFolder.Documents.Child(“CAFiles”).Child(“client-cert.pem”)
syDB.SSLAuthority =SpecialFolder.Documents.Child(“CAFiles”).Child(“ca-cert.pem”)[/quote]
Unfortunately even with those settings the connection still does not work I keep on getting [quote]SSL connection error: unknown error number[/quote] on XOJO side and [quote]2020-05-26 20:59:01 73 [Warning] Aborted connection 73 to db: ‘unconnected’ user: ‘unauthenticated’ host: ‘192.168.1.50’ (This connection closed normally without authentication)[/quote]

Server settings are same as I had them before , so other apps are working , this one does not . so something is fishy on the way

Attilio_Punzi · May 26, 2020, 6:02pm

I modified the post above. Try doing as I told you and you’ll see that it works.

Aurelian_N · May 26, 2020, 6:05pm

already did as you update it and still not working , I’m using Mojave 10.14.6 , that could be an issue ? but I doubt

Attilio_Punzi · May 26, 2020, 6:12pm

I’m using Catalina 10.15.4 and it works. I’m attaching the modified file even though I think you know what I mean.
link

Aurelian_N · May 26, 2020, 6:15pm

[quote=490167:@Attilio Punzi]I’m using Catalina 10.15.4 and it works. I’m attaching the modified file even though I think you know what I mean.
link[/quote]
yeah, identical code on my side and same error as above , still cannot connect to the server side.