topic Re: Data Refresh Failed: mysql doesn't support encrypted connections in Report Server

Data Refresh Failed: mysql doesn't support encrypted connections

smallfires0628 — Tue, 30 Jul 2024 14:09:48 GMT

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])

Re: Data Refresh Failed: mysql doesn't support encrypted connections

aduguid — Tue, 30 Jul 2024 13:44:03 GMT

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;

Re: Data Refresh Failed: mysql doesn't support encrypted connections

smallfires0628 — Tue, 30 Jul 2024 14:11:46 GMT

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])

Re: Data Refresh Failed: mysql doesn't support encrypted connections

aduguid — Tue, 30 Jul 2024 17:01:47 GMT

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

Re: Data Refresh Failed: mysql doesn't support encrypted connections

smallfires0628 — Wed, 31 Jul 2024 05:19:23 GMT

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)

Re: Data Refresh Failed: mysql doesn't support encrypted connections

Anonymous — Wed, 31 Jul 2024 09:35:39 GMT

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.

Re: Data Refresh Failed: mysql doesn't support encrypted connections

tom_pykes — Fri, 29 Nov 2024 08:52:19 GMT

Hi smallfires0628

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

Kind regards,

Tom

Re: Data Refresh Failed: mysql doesn't support encrypted connections

smallfires0628 — Fri, 29 Nov 2024 09:00:10 GMT

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")

Re: Data Refresh Failed: mysql doesn't support encrypted connections

tom_pykes — Fri, 29 Nov 2024 09:42:18 GMT

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?

Re: Data Refresh Failed: mysql doesn't support encrypted connections

smallfires0628 — Fri, 29 Nov 2024 11:14:42 GMT

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

Re: Data Refresh Failed: mysql doesn't support encrypted connections

tom_pykes — Fri, 29 Nov 2024 13:57:00 GMT

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