I need to access to Moodle database, which is running in Ubuntu Server located in AWS, via ODBC on Windows. The database is MariaDB and I installed the appropriate driver in Windows, along with the ip, port, user and password but it fails with the message: 'Connection failed [HY000] [ma-3.1.17] Can't connect to server' I don't know if I to configure anything in AWS, besides opening 3306 port, on Ubuntu, on MariaDB...?
I got it to work with several steps, but I think that the decisive was the last one:
* Change localhost to 0.0.0.0 in bind-address
sudo vi /etc/mysql/mariadb.conf.d/50-server.cnf
sudo systemctl restart mariadb.service
sudo systemctl status mariadb.service
* Install netstat
sudo apt update
sudo apt install net-tools
sudo netstat -tulnp | grep mariadb
* Allow firewall
sudo iptables -A INPUT -p tcp --destination-port 3306 -j ACCEPT
mysql -u root -h YOUR_IP -p
* Open UFW
sudo ufw allow 3306/tcp
* Create new user for odbc
CREATE USER 'YOUR-USER'@'%' IDENTIFIED BY 'YOUR-PASSWORD';
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,CREATE TEMPORARY TABLES,DROP,INDEX,ALTER ON moodle.* TO 'YOUR-USER'@'%';
FLUSH PRIVILEGES;
Thanks you all for your help