Error in refresh on service using MySQL RDS data source

[visheshjain]

Hello everyone,

My data source is MySQL hosted on AWS RDS, for all my tables.

There are 2 main tables, 1 with 6.8 million rows and another with 2.5 million rows, amongst few other small tables with 200,000 rows, 65000 rows and few more lookup tables with less than 100 rows.

I have published the report to service and I am getting this error for the refresh.

The MySql source doesn't support encrypted connections. The exception was raised by the IDbCommand interface.

I want to refresh the report on the service WITHOUT USING A GATEWAY.

I have checked the PBI desktop file and removed encryption from data source settings but still I am getting the refresh error in Service.

Please can someone help me fix this issue.

If you want any further details, please let me know.

Thank you,

Vishesh Jain

[collinq]

Hi @visheshjain ,

I do not think that you can refresh this without a gateway.  Power BI requires a gateway for MySQL connections.  If you don't want to use an ENTERPRISE gateway, and you have the right permissions, you can use a PERSONAL gateway but MySQL requires a gateway of some sort.

That said, if you really want to get into the weeds and try workarouns, you can expose MySQL endpoints to the cloud (huge potential security risk) or enable SSL or TLS on your MySQL server and create a certificate for it then you should be able to get around it as well.  I don't recommend either of those but if you absolutely do not want a gateway then those are workarounds that you can do.  BUT, as always, do so at your own risk!

[visheshjain]

Hi @collinq,

This is my configuration in the PBI service for the data source and I have entered the credentials in the 'Data Source Credentials' tab.

I tried something else; I removed all the tables except for the 6.8 million rows table and published it to the service with the same configuration and the refresh worked.

Are there any other suggestions that might be useful, apart from using a gateway?

Thank you,

Vishesh Jain

[v-nmadadi-msft]

Hi @visheshjain ,

You need to install the Oracle MySQL Connector/NET package before using this connector in Power BI Desktop. For Power Query Online (dataflows) or Power BI service, if your MySQL server isn't cloud accessible and an on-premises data gateway is needed, the component Oracle MySQL Connector/NET must also be correctly installed on the machine running the on-premises data gateway.

Power Query MySQL database connector - Power Query | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[v-nmadadi-msft]

Hi @visheshjain 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.