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 on console and MySQL Workbench providing the client certificates 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.
I followed this to import the certificates, but doesn't work.
https://powerbi.microsoft.com/es-es/blog/ssl-security-error-with-data-source/
However there is not much more information about how to properly connect to MySQL with SSL (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."
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.