It’s been a little while since I had PowerBI working against a remote repository on my Raspberry Pi and I had some troubles recalling how to configure it. As a result, it was an excellent experience troubleshooting it. This also led to me realizing, there’s no guide to configuring PowerBI to MySQL(MariaDB) on a Raspberry Pi. There are a lot of similar posts, but the problem with most of scenarios I found online is that the MySQL DB was most frequently running locally with PowerBI, whereas in my circumstance it was remote.
The primary issue I ran into was seeing “Details: MySQL: Unable to connect to any of the specified MySQL hosts.” I was able to connect to my instance with phpmyadmin and also with MySQL workbench, so I felt I was able to connect with PowerBI here too. I installed MariaDB Workbench just to test and see if I could at least access my instance with that tool. It turned out I ran into a similar issue. The problem is twofold.
1. The bind-address for my MySQL instance was set, not allowing remote IP calls.
2. I needed a user with a correct domain to access the box with a SQL login.
To resolve the first issue, I had to find my config file. MariaDB is set up to import four different config files, the one I edited was /etc/mysql/mariadb.conf.d/50-server.cnf. A quick # in front of #bind-address=127.0.0.1 and a sudo /etc/init.d/mysql restart got the IP fixed up quick.
To resolve the second issue, I created a user with extra permissions. IMPORTANT NOTE: This is NOT best practice. My Pi is on a private network and does not host any secure data. So I’d recommend taking this as an example and working with it to fit your security needs.
I create a user with admin privileges that has access open to any IP. So I’ll be able to remote into the Pi from any device on that private network with that login now.
CREATE USER 'someuseraccount'@'%' IDENTIFIED BY 'some_pass'; GRANT ALL PRIVILEGES ON *.* TO 'someuseraccount'@'%' WITH GRANT OPTION;
In PowerBI, I went to Get Data -> MySQL DB -> put in my server as 192.168.1.155 -> the name of my Database -> and set the login for Database and used the user and password I had just created in the last step.
Now I’ve seen where other users had issues and it was due to a driver issue, so make sure you have your driver updated for MySQL.
Here is where I obtained the idea for the bind in step 1.
Here is where I obtained the idea for the user in step 2.