Settings to connect to MariaDB from MS Excel

v3
#1

Hi

I have a client who wants to connect their Windows-based MS Excel spreadsheets to their database on my server, which is configured using EE.

I have installed the MariaDB OBDC connector on my PC, but cannot work out the settings. I have tried using the IP address with port 22222, but it doesn’t connect.

When I use phpMyAdmin, there is an extra login to port 22222.

How do I do this?

TIA ROB

#2

Hello @Rob_Bernstein, You’d need to connect to the MySQL port, not the admin 22222 port.

You may have to connect via SSH first, depending on how you’ve done your firewalls.

We use HeidiSQL to query / admin the MySQL side of things. With HeidiSQL it first SSH’s using plink (puttyagent should work too) via SSH to server port 22 / local port 3307 (*) and a user that can SSH.

Then HeidiSQL does a MySQL Connection to 127.0.0.1 on port 3306 using MySQL credentials over the SSH tunnel. This works because after the SSH you are really already connected to the server and 127.0.0.1 / local host IS the MySQL IP via the tunnel.

Not sure if your ODBC provider supports via SSH encapsulation, if not you’ll need to expose 3306 via your firewalls on the server and then connect to that port. If you’ve changed any of your ports in EE config you’ll need to reflect that.

(*) Local port matters here because if you are running a local MySQL on your client computer (the one connecting to the server) you can’t use 3306 or you’ll connect your local MySQL and not the servers. This is kinda a HeidiSQL specific thing, probably wouldn’t be exposed in your ODBC but I add the info for completeness - if you are running MySQL local on any of the machines wishing to connect you’ll need to keep this in mind.

Cheers

#3

Thank you davidedwards for great suggestions!

#4