Tag: RaspberryPi

Connecting PowerBI to RaspberryPi (MariaDB Engine)

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.

MySQL (Really, MariaDB) on the Raspberry Pi!

I’ve had a few people ask me lately about how I got MySQL running on my Raspberry Pi, so here’s my guide on how to go from almost 0 to 100. This guide assumes you understand the basics of Linux and Raspberry Pi’s. You’ve probably done a few different tutorials and you have tinkered with it before.

1. Download Raspbian.

Raspbian

2. Wipe the sd card and format it. (SDFormatter is a free tool you can use.)

3. Unzip the file.

4. Write the image to disk. (Win32 Disk Imager is a free tool you can use.)

5. Copy a wpa_supplicant.conf file and ssh.txt over to the boot partition of the sd card. You’ll need to edit the conf to contain your current wifi information, the below is a good example of a file you can modify and use. The ssh.text file is just a blank file with the name ssh so the Pi knows to enable SSH on boot.

country=US
ctrl_interface=DIR=/var/run/wpa_supplicant GROUP=netdev
update_config=1

network={
    ssid="YOUR WIFI IS GONNA GO HERE"
    scan_ssid=1
    psk="YOUR PASSWORD IS GOING HERE AND IF YOU DON'T USE ONE THEN SHAME ON YOU"
    key_mgmt=WPA-PSK
}

6. SSH into the PI. (Putty is a free tool you can use.)

7. Run this on your Pi to get RDP working. It’s just a nice to have but you can skip this if you won’t use it, we won’t be using it in the tutorial.

#remove the following packages : xrdp, vnc4server, tightvncserver
sudo apt-get remove xrdp vnc4server tightvncserver

#install tightvncserver followed by xrdp
sudo apt-get install tightvncserver

sudo apt-get install xrdp

8. Now update the Pi so it’s current.

#update your system's package list by entering the following command:
sudo apt-get update

#Next, upgrade all your installed packages to their latest versions with the command:
sudo apt-get dist-upgrade

8. Get My-SQL.

sudo apt-get install mysql-server

9. Configure security for MySQL, this is going to ask you about creating a root user. Make sure you give it a password, MyPHPAdmin will NOT work if you don’t make your root user with a password. I made this mistake the first time and then installed phpmyadmin, this was where the root password got stored and I had to re-run the configuration for phpmyadmin by following the steps posted below: (Credit to Amigo Chan on askUbuntu) (For anyone googling this error, I saw errors such as “not enough privilege to view users. phpmyadmin” and “access denied for user ‘root’@’localhost’ TO DATABASE phpmyadmin”.)

sudo mysql_secure_installation

10. Install bindings for python for later use.

sudo apt-get install python-mysqldb

11. Create a mysql user.

sudo mysql -u root -p

GRANT ALL PRIVILEGES ON mydb.* TO 'USERNAME'@'localhost' IDENTIFIED BY 'PASSWORD GOES HERE';

quit

12. Get apache2 up and running.

sudo apt-get install apache2 php5 libapache2-mod-php5

13. Get phpmyadmin up and running.

sudo apt-get install phpmyadmin

14. Configure apache2.

sudo nano /etc/apache2/apache2.conf

15. Restart apache2.

sudo /etc/init.d/apache2 restart

16. Now you need to go to a webbrowser and put in the IP of the Pi and /phpmyadmin at the end. Example(192.168.1.155/phpmyadmin)

And Voila! You should be in!

Additional resources used:
https://pimylifeup.com/raspberry-pi-mysql-phpmyadmin/

Home VPN’s, are you updating your IP securely?

Normally I blog about SQL Server, but I have found this (and one more coming) answers to questions that I could not find on the internet. So I had to share!

As many folks who start dabbling in home VPN setups realize or had known prior (I’m a database guy, not a network admin!), you need a DDNS provider. Your home IP will probably change, so if you wanted to connect to your home IP, it may work for a little while, but it may change unexpectedly. Having it update every few minutes with a provider so they can give you a static name to use that maps automatically to your new IP is the solution to keep it working consistently.

There are many benefits to running a home VPN and using a DDNS service, but the benefit I was after is blocking all adds on my phone. Not just blocking them from being seen, but blocking them from even starting to be downloaded. I run a PiHole at home that blocks ads at the DNS level and is my DNS server. So putting my phone on my home network at all times lets me save data AND block ads EVERYWHERE. If that wasn’t great enough, you can access home files and will have a secure connection to use if you are on a WiFi connection you may not trust.

After dabbling and following some fantastic tutorials as seen by Lauren Orsini on ReadWrite.com and Sam Hobbs on Samhobbs.co.uk I made a lot of progress and got to where I needed an application (DDClient) to update my IP to my DDNS provider and I ran into a problem. Sure it was working, my IP was being updated. But how could I be sure that I was connecting over SSL? Was I transmitting my username and password over plain text across the internet? The answer is, yes.

Here’s the format for a typical DDClient config file:

daemon=60
syslog=yes
mail=root
mail-failure=root
pid=/var/run/ddclient.pid .
ssl=yes
use=web, web=myip.dnsdynamic.org
server=www.dnsdynamic.org
login=USERNAME
password=PASSWORD
server=www.dnsdynamic.org, \
protocol=dyndns2 \
YOUR DOMAIN GOES HERE

Looks fine enough right? Well, it’s not.

If you run this:

sudo ddclient -verbose -debug -noquiet -query

You will quickly see you are connecting over HTTP, not SSL. Even though we have the value ssl=yes, it SHOULD be forcing HTTPS, but we can clearly see our connections are in plain text floating around the internet.

use=web, web=loopia address is IPADDRESSISHERE
CONNECT: myip.dnsdynamic.org
CONNECTED: using HTTP
SENDING: GET / HTTP/1.0
SENDING: Host: myip.dnsdynamic.org
SENDING: User-Agent: ddclient/3.8.2
SENDING: Connection: close

A lot of times I solve problems when I come back to them with a fresh brain and a few cups of coffee. So after a reminder about my problem, I was able to figure out that if I add a simple eight more characters, we would no longer fly through internet tubes seen, but we would be secured.

I changed the line use=web, web=myip.dnsdynamic.org to use=web, web=https://myip.dnsdynamic.org and here’s the new output from the query:

use=web, web=loopia address is IPADDRESSISHERE
CONNECT: myip.dnsdynamic.org

The verification of cert '/C=US/O=GeoTrust Inc./CN=RapidSSL SHA256 CA/CN=www.dnsdynamic.org'
failed against the host 'myip.dnsdynamic.org' with the default verification scheme.

THIS MIGHT BE A MAN-IN-THE-MIDDLE ATTACK !!!!

To stop this warning you might need to set SSL_verifycn_name to
the name of the host you expect in the certificate.

CONNECTED: using SSL
SENDING: GET / HTTP/1.0
SENDING: Host: myip.dnsdynamic.org
SENDING: User-Agent: ddclient/3.8.2
SENDING: Connection: close

Success! I’m not too concerned about the cert issue, if you look, it’s the same subdomain listed. So the query above shows you how you can verify you are connecting over SSL and the config file adjustment with HTTPS resolves your SSL connection.