MySQL EncryptedConnectionFailed error when dataset size increases

[SA2012]

See post from 2017 with same issue.

Data Gateway - MySql dataset refresh error - The M... - Microsoft Fabric Community

Using MySQL Connector Net 8.0.28 on Gateway and Desktop (latest version of connector does not connect to Localhost Mysql). 

MySQL sits in: Azure Database for MySQL servers

I have a dashboard which uses MySQL as the datasource. With a small dataset of 1000 rows, the dashboard refreshes successfully on Powerbi.com and from Desktop.

In the same dashboard, with the same dataset a test of 100k rows fails to refresh from Powerbi.com and from Desktop.

When the same MySQL source in the dashboard increases to 100k rows then receive the error:

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 abc.azure.com;my_db.)"}},{"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":"abc.azure.com;my_db"}},{"code":"Microsoft.Data.Mashup.CredentialError.Reason","detail":{"type":1,"value":"EncryptedConnectionFailed"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.DataSources","detail":{"type":1,"value":"[{\"kind\":\"MySql\",\"path\":\"abc.azure.com;my_db\"}]"}},{"code":"Microsoft.Data.Mashup.MashupSecurityException.Reason","detail":{"type":1,"value":"EncryptedConnectionFailed"}}],"exceptionCulprit":1}

Note occossionaly the dashboard will refresh with the larger dataset with no connection error.

[freginier]

Two things are worth separating here, because the error text and the behaviour point in different directions.

The explicit reason ("EncryptedConnectionFailed" / "The MySQL source doesn't support encrypted connections") is an SSL negotiation problem. Azure Database for MySQL enforces SSL, and MySQL Connector/NET 8.0.28 has known SSL-handshake issues with the gateway. So upgrade the Connector/NET on the gateway machine to a current 8.0.x build, and make sure the connector's SSL mode and the server's SSL requirement agree (Azure DB for MySQL expects SSL, so keep it on rather than disabling server-side).

But the fact that it works at 1k rows, fails at 100k, and "occasionally refreshes with the larger dataset with no error" is the bigger tell: that intermittent, size-dependent pattern is almost always a timeout (or a transient connection drop), not a real encryption misconfiguration. A misconfigured SSL setup would fail every time, not just on the big pull. A larger result set runs longer, trips the command/connection timeout, and the gateway surfaces it as the generic credentials/connection error you're seeing.

So I'd:

1) Upgrade MySQL Connector/NET on the gateway and confirm SSL mode matches the server (fixes the literal error).

2) Raise the command timeout (advanced options when you set up the connection) and check the gateway machine isn't hitting memory/CPU limits on the 100k refresh.

3) If it still only fails above a certain row count after the connector upgrade, it's the timeout - push more work to the source (a view) or filter/incremental-refresh so each pull is smaller.