CODEWITHSUNDEEP
mysqlauthentication
SomewhatInnocuous
SomewhatInnocuous

Reputation: 89

How to connect Power BI Desktop to remote MySql server that requires authentication

I have a MySql database running on a remote server which requires ssh authentication that I need to connect to with Microsoft Power BI. I can easily connect to MySQL on my localhost machine, but cannot find a way to manage both the server ssh authentication and the database user log on information.

The server requires authentication on port 22 with a username and password and the MySQL database requires a different username and password.

Can anyone offer assistance?

Upvotes: 6

Views: 24164

Answers (6)

Chris Mullins
Chris Mullins

Reputation: 53

This is not currently possible with PowerBI. The feature is currently under review, and you can help get it prioritized by upvoting here:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/7020353-mysql-over-ssh-connection

Upvotes: 1

Luis Hernando Aguilar Ramirez
Luis Hernando Aguilar Ramirez

Reputation: 9

Please remember that for remote connections you need to authorise the mysql users to connect from specific hosts (adding the IP) or % to allow to get connected from any host remotely

the SQL is something similar to: 

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

How to allow remote connection to mysql

Upvotes: 0

SomewhatInnocuous
SomewhatInnocuous

Reputation: 89

The problem turned out to be an error in the remote servers firewall configuration.

After further research I have confirmed that Power BI would not support dual authentication steps like logging onto a remote server with ssh and then connecting to the database. Thanks Robin for your suggestion. That was a key point.

Upvotes: 0

Rangerk
Rangerk

Reputation: 81

It seems possible to connect remotely, though I'm not sure you can do it through an 'SSH tunnel' on port 22.

There's a discussion here about connecting remotely which sounds to me like a connection to the usual port 3306, though I'm not certain:

https://community.powerbi.com/t5/Integrations-with-Files-and/Cannot-Connect-to-MySQL-on-Linux-VM/td-p/94914

Some common pitfalls:

1) Make sure you download the correct version of MySQL/Net connector. As of now, version 6.6.5 seems to be working. I wasted a lot of time trying to figure out what was wrong with earlier versions.

2) Server & database settings: closed ports / user permissions / bind-address

Before connecting PowerBI, try to connect another utility like MySQL Workbench. This will force you to troubleshoot the above settings.

Upvotes: 2

mahendra maid
mahendra maid

Reputation: 467

1) create gateway to connect server then

2) then it will ask connection string their you can give server port and database username and password

Upvotes: 0

Robin
Robin

Reputation: 146

I don't think it is supported (yet) but it looks like a lot of people want a solution including me. See this Power BI Ideas Request

Upvotes: 0

Related Questions