MySQL: Retrieval of the RSA public key is not enabled for insecure connections.

[pundip44]

I am unable to refresh a MySQL 8 datasource in a on prem version of Power BI reporting server. When I go in the datasouce and enter credentials under with Basic Authentication type and test I get the error: 

MySQL: Retrieval of the RSA public key is not enabled for insecure connections.

My versions are as follows:

PBRIS on premisis version 15.0.1112.48

Mysql .NET 8.0.28

I am able to refresh the datasource in PowerBI Desktop RS version Version: 2.117.984.0 64-bit (May 2023)

This is apprently a common issue with MySQL and the common fix quoted is add allowPublicKeyRetrieval=true to the connection string except I cant see how the edit the connection string. 

[Anonymous]

Hi @pundip44 ,

To change the connection string, you might need to use PowerShell scripts to interact with the Report Server's API.

Please try below steps:

1. Open PowerShell and ensure you have the Reporting Services Tools module installed. If you don't have it, you can install it using the following command:

Install-Module -Name ReportingServicesTools

2. Import the ReportingServicesTools module

Import-Module ReportingServicesTools

3. Connect to your Power BI Reporting Server using the `Connect-RsReportServer` cmdlet.

4. Use the `Set-RsDataSource` cmdlet to update the data source connection string. The exact command will depend on your server setup, but it will be something like:

Set-RsDataSource -ReportServerUri 'http://<YourReportServerURL>' -DataSourcePath '/path/to/your/datasource' -DataSourceUsername 'yourusername' -DataSourcePassword 'yourpassword' -ConnectionString "your connection string;allowPublicKeyRetrieval=true;"

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ca-mi-lo]

Set-RsDataSource : No se encuentra ningún parámetro que coincida con el nombre del parámetro 'ConnectionString'.

[pundip44]

I am not sure how to do step 3. " Connect to your Power BI Reporting Server using the `Connect-RsReportServer` cmdlet." Also I just want to check that the -DataSourceUsername references the MySQL username?  I ask as I am getting this error:

Set-RsDataSource : A parameter cannot be found that matches parameter name 'DataSourceUsername'.
At line:1 char:92
+ ... ReportServer' -DataSourcePath '/acc/acc/' -DataSourceUsername 'remote ...

[Anonymous]

Hi @pundip44 ,

Please refer below documents, hope it is helpful for you:

ReportingServicesTools/ReportingServicesTools/Functions/CatalogItems/Set-RsDataSource.ps1 at master ...,

ReportingServicesTools/ReportingServicesTools/Functions/CatalogItems/Set-RsDataSource.ps1 at master ...

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.