Powerbi Gateway MYSQL Datasource Error

[Wayde]

Hi All,

I am hoping someone can provide some assistance or clues as to what may be wrong:

The error message is as below:

Data source error{"error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","pbi.error":{"code":"DM_GWPipeline_Gateway_InvalidConnectionCredentials","parameters":{},"details":[{"code":"DM_ErrorDetailNameCode_UnderlyingErrorCode","detail":{"type":1,"value":"-2147467259"}},{"code":"DM_ErrorDetailNameCode_UnderlyingErrorMessage","detail":{"type":1,"value":"The MySql source doesn't support encrypted connections. (Source at **.**.***.**</ip>:3306;S****r_******.)"}},{"code":"DM_ErrorDetailNameCode_UnderlyingHResult","detail":{"type":1,"value":"-2147467259"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourceKind","detail":{"type":1,"value":"MySql"}},{"code":"Microsoft.Data.Mashup.CredentialError.DataSourcePath","detail":{"type":1,"value":"<ip>**.**.***.**</ip>:3306;s******r_******"}},{"code":"Microsoft.Data.Mashup.CredentialError.Reason","detail":{"type":1,"value":"EncryptedConnectionFailed"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"MySql\",\"path\":\"<ip>**.**.***.**</ip>:3306;s******r_******"\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"EncryptedConnectionFailed"}}],"exceptionCulprit":1}}} Table: Fact Sales Order Details.

I have highlighted the issues I think are related to the error above, however, I will detail below what I have tried already.

Firstly, this report was published to a workspace in our tenant. It was working up fine with daily refreshes up until early february - around 6 February 2024. We then started to get the above error when trying to do refreshes.

1. I tried updating the credentials

2. I tried downloading a copy and refreshing it in the PowerBi application. (worth noting we only have Power Bi Pro)

3. I tried updating drivers on the Powerbi data gateway as the Gateway was not updated since 2022.

4. I updated the MYSQL ODBC and Connector Drivers as well. 

None of these resolved the issue in app.powerbi.com or on the Powerbi Application.

I then took a copy of the report and put in on a laptop in my test domain, provided the credentials and the report then updated on test domain and all data was included. 

So currently the report is refreshing (ad hoc) and i am able to provide it to the users in the organisation that require it via the .pbix file.

So since I was able to update the report through the test tennant I thought the issue may be on the PowerBi Gateway server. So I spun up another server, and tried to get it set up "as new" with all the latest drivers, credentials set up, ODBC drivers installed and connections tested.

I removed the old gateway and connections from the connections and gateway tab in app.powerbi.com and then went through the process of trying to refresh the same report that was recently updated from the test environment on the production environment. The report was not able to refresh with similar errors. I have tried clearing the Permissions from the workbook and global settings - this then requires me to re enter the credentials, which I do, and then after a moment, it comes up saying cannot connect through encrypted connection, do I want to try connect through unencrypted connection to which I click okay. I then get the following error on PBI - MySQL: Authentication to host '**.**.***.**' for user 'U******' using method 'mysql_native_password' failed with message: Reading from the stream has failed.

So then I tried creating the new gateway in the production environment, setting up the connections and publishing the report. The publishing was done successfully, however the "Data Sources included in this semantic model" pulled through the connection from the report, which I needed to add to the gateway - so I added it. At this point all test connections work - and I updated the credentials in the connection that was pulled through from the .pbix report. But as soon as I refresh the report, I get the same error as above (although it is different tables)

The current setup is as follows:

PBI Gateway : 3000.214.8 (March 2024)

ODBC System DSN: MySql ODBC 8.0 ANSI Driver - Credentials entered, tested and working

Running on Win11 Pro

I would appreciate any insight or tips on where I may be going wrong? 

Thank you in advance

[lbendlin]

The MySql source doesn't support encrypted connections.

[Wayde]

Hi @lbendlin 

Thank you for your response and consideration - As stated in the original post:

I have tried clearing the Permissions from the workbook and global settings - this then requires me to re enter the credentials, which I do, and then after a moment, it comes up saying cannot connect through encrypted connection, do I want to try connect through unencrypted connection to which I click okay. I then get the following error on PBI - MySQL: Authentication to host '**.**.***.**' for user 'U******' using method 'mysql_native_password' failed with message: Reading from the stream has failed.

Also, to add, the exact same report will run (refresh) on a different non-domain joined device with a PowerBI Pro licence.

Similarly, nothing has changed on the settings side for us - and this report previously refreshed up to early February without fail.

So I am still at a loss as to what could be going wrong.

[lbendlin]

Have you considered using the native MySQL or MariaDB driver rather than ODBC ?

[Wayde]

Thanks for your input.

Please could you elaborate further on what you're suggesting? 

The server we are connecting to is a vendor provided server. Previously the ODBC connection worked, however, Im not sure what your suggestion entails.

Should I install the MariaDB ODBC driver on the PBI Gateway? or Test it on my PC?

Thank you.

[lbendlin]

It's your choice - personally I prefer whatever driver gives best performance.  ODBC is not known for speed, but it has other advantages.

[Wayde]

That has not made a difference unforuntately.

[lbendlin]

8.0.28 ?

[Wayde]

Hi @Idrissshatila 

This is the error message:

DataSource.Error: MySQL: Authentication to host '**.**.***.**' for user 'U******2' using method 'mysql_native_password' failed with message: Reading from the stream has failed.
Details:
DataSourceKind=MySql
DataSourcePath=**.**.***.**:3306;s******2
Message=Authentication to host '**.**.***.**' for user 'U*****2' using method 'mysql_native_password' failed with message: Reading from the stream has failed.
ErrorCode=-2147467259

[Idrissshatila]

Hello @Wayde ,

try refreshing this table  Fact Sales Order Details , inside power query and see if it pops up an error.

if yes, it will show you where is the error is coming from.