Data Refresh Failed: mysql doesn't support encrypted connections

[smallfires0628]

Hi,

I've been trying to fix this problem for a while, and I can't seem to find where the issue is.

I have a report connected to Alibaba Cloud's mysql, and everything works fine in PowerBI desktop. I refresh the report and everything is okay. But once I published it to powerbi service and tried to refresh there, I get the following error message: "Data source error: The MySql source doesn't support encrypted connections. (Source at ......). The exception was raised by the IDbCommand interface."

Currently, I am not using a data gateway, as I am aware that cloud connections don't require one. I've tried switching between encrypted and not encrypted connections, but the error remains the same regardless of which one I've switched on. I've also tried to use the on-premise data gateway instead, but I can't cuz I'm not an admin user. I am using power query to query the data with the mysql database. Here's the m query code:
MySQL.Database("rm XXXXXX", "coordination", [ReturnSingleDatabase=true])

[v-jtian-msft]

Hello, @aduguid ,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.
Hi,@smallfires0628 .I am glad to help you.

Currently, when MySQL is used as a data source to connect to Power BI, this is the general problem that occurs
For you to complete the data quota in Power BI
Desktop, the syntax for My SQL should be like this:

source=MySQL.Database(server
Database(server, db, [ReturnSingleDatabase=true,CreateNavigationProperties=false,Query= sql
])

The parameters here, server should be the Aliyun server address, db should be the database on the server, and sql is the query that must be executed.
You may also need to configure SSL, generate SSL certificates and configure the MySQL server to use them!
I've found some issues that have been resolved, as well as articles that I hope will help you.
URL:

Solved: Power BI Gateway MySQL source doesn't support encr... - Microsoft Fabric Community
[solved] Power BI Gateway – MySQL source doesn’t support encrypted connections | Flowpoint
Power Query MySQL database connector - Power Query | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aduguid]

Try explicitly setting the connection string to not use SSL by adding SslMode=none; to it.

Example: Server=yourserver;Database=yourdatabase;User=youruser;Password=yourpassword;SslMode=none;

[smallfires0628]

Can you clarify where I could do that? I am using power query to query the data. I am connecting it to the mysql database. MySQL.Database("rm XXXXXX", "coordination", [ReturnSingleDatabase=true])

[aduguid]

MySQL.Database("rm XXXXXX", "coordination", [ReturnSingleDatabase=true, Encrypted=false, SslMode="none"])
])

[smallfires0628]

I tried but it sent me the following error:

DataSource.Error: MySQL: This function doesn't support the query option 'Encrypted' with value 'false'.
Details:
FALSE

DataSource.Error: MySQL: This function doesn't support the query option 'Sslmode' with value '"none"'.
Details:
none
It seems like both parameters don't work.

I am working with PBI Version: 2.131.1203.0 64-bit (July 2024)

[tom_pykes]

Hi @smallfires0628 

Have you found a solution to this? Stuck on exactly the same issue...

Kind regards,

Tom

[smallfires0628 · Accepted solution]

Yes I found a solution. I have to specify for it to be non-encrypted. I do it by downloading the MySQL ODBC driver to control encryption settings.
I specified the connection string through Power Query itself. Here's the m-query code:
connectionString = "Driver={MySQL ODBC 9.0 Unicode Driver};Server=rm-XXXXX;Database=...;User=...;Option=3;SslMode=DISABLED;",
Source = Odbc.Query(connectionString, "SELECT * FROM table")

[tom_pykes]

Thanks for your quick response @smallfires0628 !
Works in PBI Desktop.
This implies set-up of a gateway I guess?
Or how do you connect from the service?

[smallfires0628]

I am using cloud connections, so no data gateway needed for that

[tom_pykes]

Thanks so much for your replies.

Ok, I see... it's a bit confusing in the settings interface.

 Details:

Anyway, don't know if you got the same issue but it asks for credentials and doesn't want to save after I filled them in. Also, it gives the impression of wanting to use an on-premises gateway (grayed out)...

What ODBC-version are you working on?
Only difference with your query is the 9.0 vs 9.1 (only one available on Oracle)

connectionString = "Driver={MySQL ODBC 9.1 Unicode Driver};Server=my_server;Database=my_db;User=my_user;Option=3;SslMode=DISABLED;",
Source = Odbc.Query(connectionString, "SELECT * FROM items")

-> works fine in PBI Desktop