Mysql "ERROR 1045 (28000): Access denied" only when host is specified
0
votes
1
answer
58
views
I'm scratching my head with this issue.
I have an Azure flexible for MySQL server with MySQL 5.7.
I've created and give grants for a new user with the "%" wildcard, following the syntax (don't worry, I'm using fake names for everything):
MySQL [(none)]> CREATE USER 'mynewuser'@'%' IDENTIFIED BY 'password';
MySQL [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'mynewuser'@'%';
MySQL [(none)]> FLUSH PRIVILEGES;
MySQL [(none)]> select user, host, account_locked from mysql.user where user = 'mynewuser'
-> ;
+--------------+------+----------------+
| user | host | account_locked |
+--------------+------+----------------+
| mynewuser | % | N |
+--------------+------+----------------+
1 row in set (0.01 sec)
MySQL [(none)]> show grants for mynewuser
-> ;
+------------------------------------------------------------+
| Grants for mynewuser@% |
+------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'mynewuser'@'%' |
| GRANT ALL PRIVILEGES ON *.* TO 'mynewuser'@'%' |
+------------------------------------------------------------+
But when I try a connection from a remote machine, if I try to specify the host (even localhost), I've got an Access Denied error from 172.26.3.34 (The remote machine, JUMPSRV01 ip):
JUMPSRV01> mysql -hSRVDB01.mysql.database.azure.com --ssl=true -umynewuser@SRVDB01 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'mynewuser@localhost'@'172.26.3.34' (using password: YES)
JUMPSRV01> mysql -hSRVDB01.mysql.database.azure.com --ssl=true -umynewuser@localhost -p
Enter password:
ERROR 1045 (28000): Access denied for user 'mynewuser@localhost'@'172.26.3.34' (using password: YES)
JUMPSRV01> mysql -hSRVDB01.mysql.database.azure.com --ssl=true -umynewuser@JUMPSRV01 -p
Enter password:
ERROR 1045 (28000): Access denied for user 'mynewuser@localhost'@'172.26.3.34' (using password: YES)
**However**, if I don't specify the host in the username parameter, it works
JUMPSRV01> mysql -hSRVDB01.mysql.database.azure.com --ssl=true -umynewuser -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 117
Server version: 5.7.44-azure-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]>
What am I missing?
The problem i am facing is that I'm replicating a database, and all the services and clients connects using the "username@dbhost" syntax and I don't know how many host or services are trying to connect to the database like that, so modify the clients and services connection string is no possible.
Asked by vegatripy
(668 rep)
Mar 25, 2025, 12:11 PM
Last activity: Mar 26, 2025, 08:47 AM
Last activity: Mar 26, 2025, 08:47 AM