MySQL user has Host=%, still can't connect remotely

Question

Even with a Host value of %, I cannot connect remotely to the MySQL container.

Error: Access denied for user 'root'@'172.28.0.1' (using passowrd: YES). 

• I'm using HeidiSQL and the MySQL library to connect
• Noting the IP specified in error message keeps changing which makes it hard to add to Host value
• On Windows 10 using Docker Desktop (4.24.2)
• my.cnf does bind to 0.0.0.0.
• Tried different port mappings (ex. 3366:3306)
• Windows Firewall shows Docker Desktop being open to public and private networks

I can connect through Docker Desktop using the Exec tab. I can see that root has appropriate host configured for remote access:

mysql> select Host,User from user where User='root';
+-----------+------+
| Host      | User |
+-----------+------+
| %         | root |
| localhost | root |
+-----------+------+
2 rows in set (0.01 sec)

File: docker-compose.yml

version: "3.2"
services:
  mysql:
    container_name: mysql
    build: './mysql/'
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_ROOT_HOST: "%"
      TZ: "-03:00"
    networks:
      - frontend
      - backend
    ports:
      - "3306:3306"
    volumes:
      - "$MYSQL_DATA:/var/lib/mysql"
networks:
  frontend:
  backend:

File: .env

MYSQL_ROOT_PASSWORD="root"
MYSQL_DATA=C:/Users/me/Downloads/docker_files/mysql/data

File: mysql/Dockerfile

FROM mysql:8.0
COPY conf/my.cnf /etc/my.cnf

File conf/my.cnf

[mysqld]
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
secure-file-priv=/var/lib/mysql-files
user=mysql
bind-address=0.0.0.0
default-time-zone='-03:00'
general_log = 1
general_log_file = /var/log/mysql/mysql.log

#setting to 1 per recommendations, need to do this in Azure too.
lower_case_table_names=1

pid-file=/var/run/mysqld/mysqld.pid
[client]
socket=/var/run/mysqld/mysqld.sock

!includedir /etc/mysql/conf.d/

HeidiSQL:

Update #1

For good measure, I set password for both users using Docker Desktop's terminal:

mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.02 sec)

mysql> alter user 'root'@'localhost' identified by 'root';
Query OK, 0 rows affected (0.02 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

Still getting access denied.

Answer 1

I learned that in HeidiSQL, I should always use libmariadb.dll even when connecting to MySQL:

https://github.com/HeidiSQL/HeidiSQL/issues/1865