Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tvandorffy71
Frequent Visitor

Connecting to SSL Enabled MySQL database

Hi,

 

I am trying to connect an SSL enabled mySQL database with Power BI desktop, but I am getting an error message:

"We couldn't authenticate with the credentials provided. Please try again"

 

When using MySql Workbench with the credentials provided by my mysql db admin, the connection works fine.

I got a server ip address,  my user name and password and three .pem files for the ssl configuration:

The SSL Key file (client-key.pem)

The SSL Cert file (client-cert.pem) and

The SSL CA file (server-ca.pem)

 

While trying to search the solutions on the internet, I have found, that if I load these information on my computer's certificate store, where I am using Power BI desktop, then the ssl connection should work.

The problem was that windows does not really handle .pem files, but I have found it out that it do accept .pfx files. (Also googled this up.) So I used the openssl package to create a .pfx file using my keys with the following command:

 

openssl pkcs12 -export -out dev_server.pfx -inkey client-key.pem -in client-cert.pem -certfile server-ca.pem

 

Using the resulting pfx file, I was able to upload the server certification and my keys to the storage.

Sure enough, one certificate landed in the "Trusted Root Certification Authority" with the name "Google Cloud SQL Server CA" and another in the "Personal" section of the store. I imported the pfx file into both "Current User" and the "Local Computer" stores.

 

But despite all my efforts, I am still getting the error message above.

I would really appreciate any help to solve this.

(If there IS a solution for this problem that is...)

 

Thanks a lot,

 

Tamas

 

   

 

2 REPLIES 2
v-jingzhang
Community Support
Community Support

Hi @tvandorffy71 

 

You may try importing the .pem certificates to Trusted Root Certification Authorities for Local Computer directly rather than converting them into a .pfx file. Windows can handle .pem files. When importing, you can switch the file type option to All Files(*.*) in Open window, then you are able to import them. 

vjingzhang_1-1680233261768.png

 

In addition, you may need to download the latest MySQL connector on your computer. The following link may be helpful. 

How to connect PowerBI desktop to Azure MySQL with SSL - Stack Overflow

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Hi v-jingzhang,

 

Thanks for your response.

 

I did not know, that it is possible use pem files to import certificates! Thanks for the tip.

The reason I used pfx was that this was, I was able to package all three of my keys into one file and import it to the certificate storage directly. If I do this, my client certificate will contain my private key a well:

client-cert-with-key2.jpg

As far as I know, it is not possible to import  the client-key.pem file individually to the certificate store.

 

I have updated my whole mysql installation (connectors and everything else) to the latest version: 8.0.32

I tested the scenario where I imported only pem files into the store (server-ca.pem and client-cert.pem only) but I got the same error message as before.

I also tested the scenario where I imported the server-ca.pem as itself and the client keys packaged as a pfx.T

This also resulted in the same error.

 

Then got an idea and checked if tracing is available in Power BI. Found it, turned it on  and used it to get info. I am attaching the log file to this message. But here is the most important line IMHO:

Microsoft.Mashup.Container.NetFX45.exe Error: 0 : Authentication to host '35.205.252.128' for user 'tvandorffy' using method 'mysql_native_password' failed with message: Access denied for user 'tvandorffy'@'<ip address>' (using password: YES)

 

Please note that I removed any ip address related info and replaced it with <ip address>

This error is interesting! My ip, username and password is 100% ok. So this must be a certificate related problem.

 

One more thing to note: when testing the connection with the MySQL Workbench (uses directly the pem files), I played with the "use SSL" dropdown in the SSL settings of the connection, I have found that options "Require" and "Require and Verify CA" works well. But when I choose "Require and Verify Identity" the connection test fails with the following error message:

mysql_connection_error_cert.jpg

Maybe Power BI also tries to verify the identity and this is why the connection fails.

 

I am basicly at the end of the line, no idea to try out. (Short of opening a ticket to Microsoft)

 

I wonder if you (or anybody) have any more idea or trick to try.

 

Thanks a lot,

 

Tamas

 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors