Forum Discussion
PowerBI Personal On-premise Gateway Connection Reverted to Cloud Connection and Unable to Restore
I've been using a personal on-premise gateway to enable scheduled refresh of PowerBI dashboards from a MySQL data source (which is behind a firewall) without issue for several years. However, yesterday when I made some changes to a report on desktop and uploaded it, powerbi.com was unable to refresh it. At first it seemed like a credential issue, but now I realized the data connection is no longer pointed to the personal gateway. I toggle to turn on the on-premise gateway is greyed out (snapshot below).
I've tried a bunch of different things--deleting data sources and data connections, trying to recreate them in PowerBI desktop, reuploading new files, and the only thing i've accomplished is breaking other dashboards. The other dashboards that are now broken (which connect to the same MySQL schema and probably had the same data connection) now show both a gateway and a cloud connection that didn't exist before and have authentication errors (probably because the cloud connection doesn't work). Meanwhile, other dashboards that connect to a differet MySQL schema and were untouched by the update continue to work fine and the gateway shows up as online and working.
I have been considering upgrading to a enterprise gateway and maybe this is my opportunity to just clean house and start from scratch, but I'm worried I'll run into issues there as well. Any ideas on how to fix this? Thank you so much!
I never solved the initial problem with the Personal gateway, but was able to make it work after switching to an Enterprise gateway. After I had the Enterprise gateway, I could create a connection to MySQL in the PowerBi.com settings (which was not an option for some reason with the Personal gateway), enable access for cloud connections to use the gateway, and then I could map the cloud connections in all dashboards to the new enterprise gateway. This only worked for dashboards that were connected to the same schema on the MySQL database, but i went ahead and cleaned everything up to point to a single schema and utilize that one connection.
9 Replies
I never solved the initial problem with the Personal gateway, but was able to make it work after switching to an Enterprise gateway. After I had the Enterprise gateway, I could create a connection to MySQL in the PowerBi.com settings (which was not an option for some reason with the Personal gateway), enable access for cloud connections to use the gateway, and then I could map the cloud connections in all dashboards to the new enterprise gateway. This only worked for dashboards that were connected to the same schema on the MySQL database, but i went ahead and cleaned everything up to point to a single schema and utilize that one connection.
I'm facing the same issue in a meshup with embedded Python. The semantic model - retrieving data from an REST-endpoint wrapped in a Numpy-DF worked for at least 1,5 year.
In spring 2024 Microsoft did some changes that even the Microsoft support was not aware of (https://learn.microsoft.com/en-us/power-query/connectors/mysql-database).
Thank you Microsoft it will cost me hours to find a "work-a-round" beside the caused impact!
- lbendlin
Super User
If you have a VM (or two) in the on-prem cloud close to your MySQL data source then I would certainly recommend you switch to that. (Quite impressive that you have survived on a Personal Gateway for so long). But I am not sure that it will make a big difference unless you were running on a rather old version ?
Hi rttracey ,
I would like to acknowledge the valuable input provided by lbendlin .
His initial ideas were instrumental in guiding my approach. However, I noticed that further details were needed to fully understand the issue.
In my investigation, I took the following steps:
Personal Gateway is updated monthly and using an outdated version may cause connectivity issues.
Uninstall the current gateway, download the latest version, and reinstall it.
During the installation process, select "On-premises data gateway (personal mode)".
After reinstalling the gateway, go to the Power BI service, navigate to the dataset settings, and make sure the gateway is correctly selected for the dataset.
Please try these operations to see if the problem is solved.
If you have any other questions, please feel free to contact me.
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!Hi. I have same problem. I had install and uninstall many version (2.0.X - 3.X) of MySQL Connector/NET. Also I had changed on-premise gateway (personnal mode) versions (Dec 23, Apr 24).
MySQL database is on-premise database, but I published to app.powerbi, it was not selected to on-premises gateway. It is selected cloud connections (Maps to: Personal Cloud Connect). How can I solve this problem?
- lbendlin
Use a known good version like 2.0.28
Hi Yang,
the issue is/was that Microsoft changed the behaviour of that particular MySQL-connector. The personal gateway, plus in conjunction with embedded R and Python code worked for years - I promise!!
But PowerQuery-Documentation of May stating:
MySQL connections cannot be used with personal gateways.
If the MySQL database is not accessible from the cloud, configure MySQL on-premises connections by upgrading to an Enterprise Gateway instead of using a personal On-premises Data Gateway. For cloud-based MySQL servers, a gateway is not required.
It is not possible to mashup MySQL on-premises data with R and Python.
For cases where Python or R is used with a MySQL database on-premises connection, use one of the following methods:
- Make the MySQL server database accessible from the cloud.
- Move the MySQL on-premises data to a different dataset and use the Enterprise Gateway exclusively for that purpose.
So please explain the reason to us why it's eliminated by spring/summer 2024 - to me it makes no sense (beside Fabric upselling strategies of certain capacities)? This is absolutly unsatisfying. You modified - if you so want - the runtime and even (after opening a ticket) the Microsoft support is not aware of and let us developers figure out how to solve the impact it causes to our business. I experienced similar for the 3rd time (like changes in Fabric/Warehouse in executing stored procedures).
Please be aware that the PBI as part of the PowerPlatform stack is "somehow" a developer environment. And tiny changes could impact business critical. THX
I have the same issue, I used to use Powerbi desktop and connect to mysql DB (import mode) then publish and use the personal mode gateway for a refresh,
Suddenly on 4th April 2024, I had to edit some things in the Powerbi desktop dashboard, then I published as usual and found that the Gateway connection reverted to the cloud connection which is giving me a credential error when I try to connect through the cloud connection,
I tried to publish another dashboard and I found the same issue happened,
I tried to reinstall DataGateway but still have the same issue
since i have the same case, Please let me know if you solved this issue, Thanks
