One of my clients contacted me for a linked server issue between SQL Server and MySQL Server. Since I mostly work with SQL Server, it was a fun installing and connecting MySQL Server. While I was trying to simulate the issue and I wanted to create a linked server. In this blog, we would learn how to fix MySQL connection error [MySQL][ODBC 5.3(w) Driver]Host ‘IP’ is not allowed to connect to this MySQL server.
I installed MySQL Server on a server and provided the root user’s password. When I was trying to connect to MySQL Server using ODBC.
![MySQL - Connection Error - [MySQL][ODBC 5.3(w) Driver]Host 'IP' is Not Allowed to Connect to this MySQL Server mysql-login-err-01](https://blog.sqlauthority.com/i/d/mysql-login-err-01.jpg "MySQL - Connection Error - [MySQL][ODBC 5.3(w) Driver]Host 'IP' is Not Allowed to Connect to this MySQL Server mysql-login-err-01")
As soon as I clicked on the test, I see below error.
![MySQL - Connection Error - [MySQL][ODBC 5.3(w) Driver]Host 'IP' is Not Allowed to Connect to this MySQL Server mysql-login-err-02](https://blog.sqlauthority.com/i/d/mysql-login-err-02.jpg "MySQL - Connection Error - [MySQL][ODBC 5.3(w) Driver]Host 'IP' is Not Allowed to Connect to this MySQL Server mysql-login-err-02")
The IP mentioned in error message is IP of the client which is trying to connect. The text message is as follows:
Connection Failed
[MySQL][ODBC 5.3(w) Driver]Host ‘IP’ is not allowed to connect to this MySQL server:
I have never seen this error while working with SQL Server, so I had no idea about this error. When I was researching I learned below.
By default, MySQL does not allow remote clients to connect to the MySQL database.
The fastest way to verify that is as below. If we check mysql.user table, there is an entry for user ‘root’ with host ‘localhost’.
![MySQL - Connection Error - [MySQL][ODBC 5.3(w) Driver]Host 'IP' is Not Allowed to Connect to this MySQL Server mysql-login-err-03](https://blog.sqlauthority.com/i/d/mysql-login-err-03.jpg "MySQL - Connection Error - [MySQL][ODBC 5.3(w) Driver]Host 'IP' is Not Allowed to Connect to this MySQL Server mysql-login-err-03")
So, we need to provide permission to connect to MySQL Server to a client.
WORKAROUND/SOLUTION
First, make sure it is not a firewall issue.
As we discussed earlier, it’s an issue with permissions. We can then give permission using the command.
Use mysql; GRANT ALL ON *.* to root@'x.x.x.x' IDENTIFIED BY 'your-root-password';
You can also use MySQL Workbench to do that. Below screenshot tells the steps to be followed. Same as the command, we need to give username, password, and IP in the graphical screen.
![MySQL - Connection Error - [MySQL][ODBC 5.3(w) Driver]Host 'IP' is Not Allowed to Connect to this MySQL Server mysql-login-err-04](https://blog.sqlauthority.com/i/d/mysql-login-err-04.jpg "MySQL - Connection Error - [MySQL][ODBC 5.3(w) Driver]Host 'IP' is Not Allowed to Connect to this MySQL Server mysql-login-err-04")
Hope this blog would help. After the above changes, when you try to connect to the mysql database from this remote client (we have given the IP/hostname), you should not get the “Host is not allowed to connect to this MySQL server” error message anymore. We can also use % to allow all hosts but I don’t prefer that option.
I am not a MySQL expert so feel free to comment and let me know if there are better ways.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
Think of the MySQL root user as SA in SQL Server, you don’t give out access to your general every day user. Similar to SQL Server, you would create a user with just the permissions to the individual databases you would need, for the IP address of the server(s) you’re requesting from. You’re creating a new user anyway in your root example.
@ThePolyglotDBA,
Wow! thanks for sharing this important information
Thank you Pinal and PolyglotDBA. I have been working with old code someone was using in which they supposedly were connecting with root. When I worked with it, it wasn’t connection. I wasn’t totally at ease using the Grant permission, and perhaps that would have been okay. I just don’t like messing with root, just like not messing with SA.
In any case, the program is inserting and updating users for a WordPress site. A new and dedicated user (limited access) really makes more sense to begin with.
Thank you very much, our company upgrading the version from 5.5 to 8.0 and i have been struggling connecting it from client workstation, your solutions had save my life! Thanks
Muchas gracias señor, su empatia para con el otro es conmovedora y muy util, usted seguramente no sabe lo que puede ayudar a los colegas con este tipo de post, salud y larga vida para usted y los suyos, estará presente en mis oraciones nocturnas, gracias por tanto.
[Translation]
Thank you very much sir, your empathy for the other is moving and very useful, you surely do not know what can help colleagues with this type of post, health and long life for you and yours, you will be present in my night prayers, thanks a lot.