On-Premesis Gateway cannot connect to MySQl source

[maracles]

I've spent a bit of time looking on the forums and can find similar threads but none that solve my issue.

We've installed On-Premises Gateway on our Windows 2008 R2 server in an attempt to switch from personal. The gateway is installed, active and recognised in Power BI Service.

The problem comes when I try to add a data source to our MySQL databse, I get the following error:

Althought the error appears obvious I don't really understand what is not installed or being recognised, because:

• On the server if I connect to my source via MySQL Workbench I have no issues
• On the server if I connect to my source via Power BI Desktop I have no issues.

The fact that these both work on the server which the gateway is held suggests to me that is should work via the service, correct?

Can anyone identify what the cause may be or how I can further troubleshoot it. I have Gateway logs available if necessary but thought I'd keep them out for now to simplify things.

We have this version of the connector installed (http://dev.mysql.com/downloads/mirror.php?id=412152) but have also tried the latest version, the version above was recommended as a way of fixing the error but did not help.

[ibarrau]

I am having the same problem. I have an on premise gateway working with SQL Server. Then a new requirement made us connect to mysql with desktop without a problem.

When configuring gateway on Service the error for connector x64 shows up. It seems that the 64bits connector doesn't exists. So, how can Desktop recognize it and Service not.

Did anyone succed on this issue????? Please need an answer because personal gateway is not an option for me!!!   😞

[maracles · Accepted solution]

@ibarrau and others I have finally managed to resolve this issue with the help of Microsoft.

First, there is no specific x64 version of the MySQL connector - there is a single .net connector available that works with both x86 and x64. Some people have reported having issues with some of the newer versions however, so potentially try different version numbers.

Second, I've had conflicting stories on whether Personal Gateway needs to be removed however in my case it did. However, even after we had thoroughly removed personal gateway from our machine is still shows up in Power BI Service. This threw both us and the Microsoft support guys off for a little bit but to put it simply this can be ignored. The Personal Gateway will still show in Power BI Service but it isn't doing aynthing.

Regarding my specific problem, the issue was two-fold:

1. The connector was installed and being recognised by Power BI Desktop fine, however it wasn't being recognised by Power BI Service - the reason for this was that we had to restart the server with On-Premises on it.  After restarting we starting making progress, so if you have not done that please do this first.


2. When you add a MySQL data source to your On-Premises connector in Power BI you must give it EXACTLY the same name as in your PBIX file.

When you connect to MySQL in Power BI Desktop you can't actually give the source a name (as far as I know) so you end up with a name similar to 43.21.34.10;database_name this is the exact name that you have to give your source in Power BI service. So both the Name and Server field will end up being identical.

Once you have done this you should be able to connect.

A couple of things to note:

• Sometimes even when you think everything is configured correctly you will only have the option for a database to connect using On-Premises Personal (if you once had personal installed). This for me indicated that one or more of the data sources added to On-Premises gateway had been incorrectly named. Once all of the names matche up only then did On-Premises Gateway show up as an option.
• If you are transferring a PBIX file that was using Personal Gateway to use On-Premises gateway it will not work until ALL of the data sources are connected to On-Premises. You cannot connect if for example your MySQL connection is 100% accurate but a link to a OneDrive file is wrong.

Took a while but thankfully this is now working for us, hopefully the above helps someone.

[pmartin]

I had the exact same issue and tried all the following:

• Installing MySql Server (and rebooting).
• Installing newer MySql drivers than 6.6.5 (and rebooting).
• Installing older MySql drivers than 6.5.5. (and rebooting).

I could connect to the data source via a Personal Gateway and could schedule refreshes but still had an issue with the on-premises gateway.

The resolution was as per the comments.

• Login to PowerBI.
• Goto your Gateway.
• Add a new datasource.
• Ensure that the datasource name is "server;database", i.e. "10.0.100.1;reports"
• Fill in all the other fields.

Thanks

[anilreddy128]

I used above suggestions and Now I am getting into another issue related to SSL.

"An error happened while reading data from the provider: 'A call to SSPI failed, see inner exception.'". 

I was able to solve this in Power BI Desktop by unchecking a check box in datasource settings. But I could not find a way to do this on gatway.

[ibarrau]

Thank you!!! restarting the server and changing the name of the source to the exact name of the .pbix file worked!!!

Now the project is ready, thanks again.

Regards,

[ankitpatira]

@maracles I think it is complaining about missing MySQL connector which you can download it from here.

[maracles]

Sorry @ankitpatira, maybe I wasn't clear enough. I have already installed the connector. I have also tried the latest version of the connector and older versions in case there was compatibiity issues, but none have worked so far.

Any other suggestions.

[v-micsh-msft]

Hi maracles,

What is your current situation by now?

Please upload the event logs into OneDrive, then post the shared link here. We will help to check.

Just from the error message you posted, this issue seems to be related with the database connector, I am currently setting up the test environment on that part, there might be some time delay before I could share any results out.

For general troubleshooting, have you checked the following two articles:

Troubleshooting the On-Premises Data Gateway

Troubleshooting Power BI Gateway - Personal

I saw that you have installed personal gateway, and would like to switch to On-Premises Data Gateway, before install the On-Premises Data Gateway, have you uninstalled the personal gateway? If not, take a try to uninstall the personal gateway, and then add the data source again.

Regards

[maracles]

Thanks @v-micsh-msft, I've taken a look at the troubleshooting but without much success - I've also exited and stopped running the personal gatyway (but not uninstalled) and it isn't fixing the issue eithe.

I do have a ticket up with Microsoft support but was hoping I could solve it quicker here.

[maracles]

Can I just confirm, the error suggests installing a 64-bit verison of the connector, from what I can see there is only a 32-bit version which works on both - is this correct?

UPDATE:

I have tried testing my set-up a bit more and think there may be a deeper problem. I uninstalled Personal Gateway and then proceeded to create a completey new PBXI file.

From within that file I imported some data from a local Excel file stored in a OneDrive folder. I then published this new BI file to a brand new workspace that I created in Power BI service.

The initial update worked fine and data / reports were accessible, however when I try to refresh it fails. The message I get indicates that it is trying to refresh through the Personal Gateway despite this no longer being installed on any machines. Furthermore when I go to settings I cannot switch from the Personal to On-Premises Gateway. 

Any ideas on how to completely clear out the Personal Gateway from Power BI Service? 

[Jasper]

I have the same problem. I removed the Personal Gateway, but Power BI service is still referring to the personal gateway:

 while this gateway is not visible @ "Manage Gateways" (only the installed on-premises Gateway is).

I wanted to reinstall the personal gateway, but I get an error message that I needed to remove the on-premises data gateway first. I deleted everything, including via the regedit (https://powerbi.microsoft.com/en-us/blog/failure-installing-the-power-bi-personal-gateway/) Maybe this may help you.

For me it still won't install the personal gateway or refresh via the on-prem gateway.. Please let me know when you have an update!

[maracles]

@Jasper I'm having exactly the same issue as you now after removing the Personal Gateway. I have Microsoft support looking into this but it's a slow process and their inital advice hasn't been encouraging with them suggesting that I totally skip Power BI Desktop and only use Service for creating reports which for us is not feasible.

If I get this working I will let you know.

One thing they categorically stated was that you cannot have Personal and On-Premises running simultanesouly which is slightly at odds with the poster above who seemingly has managed to get both working.

[Jasper]

Hi maracles,

When the error message occurs you can save a the log file. There I found a string that belongs to an already installed gateway and I copied it. I uninstalled all programs, deleted all files (programs, download, appdata folders). I opened start -> run -> regedit and made an export in case things went wrong. Next search I searched for the string and found an uninstall file "MsiExec.exe /X/ 2950A5B0-C59C-4738-833B-3D8E0...." I executed this file and uninstalled another instance of a gateway. I downloaded the gateway, installed the on-prem gateway (run as administrator by right clicking the file) and made a new gateway. Next I installed the personal gateway. Now it works again and hopefully it will last!

Good Luck, Jasper

Link for the regedit actions: https://powerbi.microsoft.com/en-us/blog/failure-installing-the-power-bi-personal-gateway/

[Jasper]

With great support I got the Personal Gateway running again. Marcus told me that in some cases (data mashup?) the on premises data gateway is not working. You can first install the on premises data gateway and then the personal gateway. Good luck!

[gooranga1]

I found that the On-premises Gateway will not be enabled if any of the datasets cannot be connected to correctly by it. We did have a mySQL database to connect to but it was such a pain to try to set up that I created a SQL Server data warehouse with the relevant data in and queried that instead. Even then the SSIS connector is pretty flakey but at least I could get it to work okay.

Not that helpful I'm afraid but at least you know you are not alone.