We set up a MySQL 8.0 server, uploaded some data, and connected a PBIX file to begin pulling data. The appropriate MySQL connectors have been installed in the MySQL Server and the PowerBI On Premise Gateway has also been installed on the MySQL Server. This server is a windows machine spun up on AWS.
When we upload the PBIX file to app.powerbi.com and setup the gateway to hit the AWS server, we keep receiving the following errors:
Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."
Code:DMTS_PublishDatasourceToClusterErrorCode
MYSQL:Unable to connect to the data source undefined.Underlying error code:-2147467259Underlying error message:An error happened while reading data from the provider: 'Could not load file or assembly 'Renci.SshNet, Version=2016.1.0.0, Culture=neutral, The system cannot find the file specified.'DM_ErrorDetailNameCode_UnderlyingHResult:-2147467259Microsoft.Data.Mashup.ValueError.DataSourceKind:MySqlMicrosoft.Data.Mashup.ValueError.DataSourcePath:ec2-50-19-199-157.compute-1.amazonaws.com;amsportfolio_newMicrosoft.Data.Mashup.ValueError.Reason:DataSource.Error
2nd Error:
| Activity ID: | f03ddd4b-abe7-4205-af71-e410dadeff36 |
| Request ID: | 77932eae-67f6-83a8-ad7c-7b8135e60ca4 |
| Cluster URI: | https://wabi-us-east2-redirect.analysis.windows.net |
| Status code: | 400 |
| Error Code: | DMTS_UpdateClusterDatasourceCredentialsErrorCode |
| Time: | Tue Jul 23 2019 00:57:00 GMT-0400 (Eastern Daylight Time) |
| Version: | 13.0.10175.153 |
| PIN MYSQL: | Unable to connect to the data source undefined. |
| Underlying error code: | -2147467259 |
| Underlying error message: | An error happened while reading data from the provider: 'Could not load file or assembly 'Renci.SshNet, Version=2016.1.0.0, Culture=neutral, PublicKeyToken=1cee9f8bde3db106' or one of its dependencies. The system cannot find the file specified.' |
| DM_ErrorDetailNameCode_UnderlyingHResult: | -2147467259 |
| Microsoft.Data.Mashup.ValueError.DataSourceKind: | MySql |
| Microsoft.Data.Mashup.ValueError.DataSourcePath: | ec2-50-19-199-157.compute-1.amazonaws.com;amsportfolio_new |
| Microsoft.Data.Mashup.ValueError.Reason: | DataSource.Error |
To troubleshoot the connection, I tested the following:
From a separate machine, I can connect to the MySQL database using PowerBI Desktop.
From the AWS Machine, I can connect to the local MySQL database using PowerBI Desktop.
So I can connect to the MySQL server, and I can see the new gateway connection on app.powerbi.com.
However, when I attempt to create the data source connection, I keep running into the same 2 errors above.
I scoured the webs in search of an answer for my issue, and no luck.
I referenced this site: https://community.powerbi.com/t5/Service/On-Premesis-Gateway-cannot-connect-to-MySQl-source/td-p/717..., but I keep running into the same errors.
Any idea how this Renci.SshNet file needs can be located and addressed?
