Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
jellek
New Member

Cannot Connect to MySQL on Linux VM

Hi there,

I have a Virtual Machine running a LAMP server with a bridged connection to the host computer (Win10).

 

After installing Mmysql-connector-net-6.6.5.msi I still cannot connect to my database; utilities. 
I can access the database through phpMyAdmin, 192.168.0.13 on host computer.
If I check in phpMyAdmin, the port is 3306, I also tried Ubuntu which is listed as the Hostname in phpMyAdmin.

 

In Power BI i get the following error:
"Details: "MySQL: Unable to connect to any of the specified MySQL hosts.""

 

Why can I not connect to my Database? what am I doing wrong? Is this a firewall issue?


I'm still new to virtual machines and LAMP, so if you could point me in the right direction then that would be great.

Thanks in advance.
Jelle

 

 

 

 

1 ACCEPTED SOLUTION
v-qiuyu-msft
Community Support
Community Support

Hi @jellek,

 

In your scenario, I would suggest you check if you can use MySQL workbench to the database successfully. Make sure that MySQL workbench is installed  on the same machine with Power BI Desktop. If the connection fails, please follow the instructions in this blog to troubleshoot this issue.

 

If you are able to connect to database using MySQL workbench, please use same connection parameters in Power BI Desktop, and also add port number with server name to connect to MySQL.

 

Also ensure that you install MySQL Connector/Net 6.6.5 for Microsoft Windows on your computer. Select the driver that matches your Power BI Desktop installation (32-bit or 64-bit).

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
dlash
New Member

Does this work? The mysql credentals page does not even support a port number? 

Are you sure you can connect to a remote mysql DB running off a linux server? It doesn't look supported. 

5 years later and I can connect with any tool to MySQL from inside WSL2 and from the Windows host. But PowerBI Desktop has no proper documentation, no settings, not anything that can help you to connect to DB or check what is not working. It will just not connect to MySQL DB.
You can paste whatever connection string there, it will not tell you anything useful just:
Details: "MySQL: Unable to connect to any of the specified MySQL hosts."

Edit: After finally discovering how to save and read the debug logs, issue was unability to read/reach DSN. I have tried altering host file to no avail. I have tried to put there "https://www.google.com" and even that was not recognized as domain. Then I tried to remove https:// from "https://localhost" to just "localhost" and it finally found the domain.

v-qiuyu-msft
Community Support
Community Support

Hi @jellek,

 

In your scenario, I would suggest you check if you can use MySQL workbench to the database successfully. Make sure that MySQL workbench is installed  on the same machine with Power BI Desktop. If the connection fails, please follow the instructions in this blog to troubleshoot this issue.

 

If you are able to connect to database using MySQL workbench, please use same connection parameters in Power BI Desktop, and also add port number with server name to connect to MySQL.

 

Also ensure that you install MySQL Connector/Net 6.6.5 for Microsoft Windows on your computer. Select the driver that matches your Power BI Desktop installation (32-bit or 64-bit).

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi @v-qiuyu-msft v-qiuyu-msft,

I have MySQL DB installed in a Linux Server. I am successfully able to connect to the linux server MySQL DB from MySQL Workbench (connection method = standard tcp/ip over ssh) in my local machine. Gave Server host name, user, pwd, mysql db host, port, user and pwd.

However I am not able to do the same from Power BI in my local machine. I see only the following (as in screen shot) taken as inputs.

I referred this link - https://towardsdatascience.com/connecting-to-aws-remote-mysql-server-via-an-ssh-tunnel-6669a84cee91 and it seems to say Power BI SSH tunnel is not possible directly and they suggest to use putty. However I also read that with Putty there is a danger frequent connection drops impacting Power BI reports.

Can you please help resolve this and let me know if there are any other settings I should consider/change in both my linux server and local windows machine to establish a successful connection?

 

powerbi_mysql.JPG

 

Hey @Anonymous I am also in the same boat today where you were a couple of months back. I am working on connecting MySQL database over ssh. I have got bastion SSH IP address, user name, password besides MySQL hostname, user name, password & pem/ppk file. 
I have been able to connect through MySQL workbench, but when I try using powerbi, I am also getting same error, ""Details: "MySQL: Unable to connect to any of the specified MySQL hosts.""

BAsed on your experience, can you help me with some approaches which u used to solve the problem. Thanks in advance

Thanks a lot for your help Qiuyun Yu, you pointed me in the right direction.

There were a few things I had to do on both my host and in guest MySQL database:

 

1) I opened the correct port number on my windows host.

2) GRANT ALL ON *.* to homedbuser@localhost IDENTIFIED BY 'password'; for my user in MySQL

3) in mysqld.cnf file i changed the bind-address = 0.0.0.0

 

After all these changes I tested the connection and it worked.

 

Time to visulize my data.

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors