I have a MySLQ running on a CentOS server with SSL enabled and it require SSL in order to connect to the databases. I created the certificates and keys using OpenSSL, getting this files:
- ca.pem
- ca-key.pem
- client-cert-pem
- client-key-pem
- server-cert.pem
- server-key.pem
Setup MySQL with this:
ssl-ca=/etc/certs/ca.pem
ssl-cert=/etc/certs/server-cert.pem
ssl-key=/etc/certs/server-key.pem
bind-address=*
require_secure_transport=ON
I created a user that require X509 on the MySLQ by using:
CREATE USER 'user'@'%' IDENTIFIED BY '<password>' REQUIRE X509;
Testing with the MySQL client console and MySQL Workbench providing the client certs and it works fine. Also works on a Java App that writes/reads the databases by importing certifitates to the keytores/trustores.
However, I cannot set up Power Bi Desktop version to connect to the MySQL server. I imported the certificates to the Trusted Root Autenticathion Authorities and a PKCS12 keystore and trustore (used also by the Java App). This image shows the certificate. It is in Spanish, but it says it has also the key and it is verified by the ca.pem.
This is according to the documentation, but the documentation about this is very old and very limited. Some of the process and/or tools are out of date.
This are the sources I could find: https://github.com/Microsoft/PowerBI-visuals/blob/master/tools/CreateCertificate.md#generate-certificate-manually
https://github.com/Microsoft/PowerBI-visuals/blob/master/tools/CertificateAddWindows.md
https://powerbi.microsoft.com/es-es/blog/ssl-security-error-with-data-source/
However there is not much more info about how to properly connect (or I cannot find it).
The message I get on Power Bi is "We were unable to authenticate you with the credentials provided. Try again."
I must add that disabling SSL allows me to connect to the databases using Power Bi, without any issue, it is the SSL what doesn't work as I don't know how to properly provide the certificates and I cannot find anything that decribes the process.