Forum Discussion
Data Refresh Failed: mysql doesn't support encrypted connections
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])
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")
10 Replies
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 LearnI 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.
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;
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])
MySQL.Database("rm XXXXXX", "coordination", [ReturnSingleDatabase=true, Encrypted=false, SslMode="none"]) ])
