MySQL – Connection Error – [MySQL][ODBC 5.3(w) Driver]Host ‘IP’ is Not Allowed to Connect to this MySQL Server

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

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

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

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

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)

, , ,
Previous Post
SQL SERVER – AlwaysOn Automatic Seeding – Database Stuck in Restoring State
Next Post
Avoiding Database Downtime via Replication – SharePlex

Related Posts

4 Comments. Leave new

  • ThePolyglotDBA
    March 29, 2018 1:46 am

    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.

    Reply
  • virgiljosephschmidt@gmail.com
    February 4, 2020 10:41 pm

    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.

    Reply
  • jeffsoossJeff Soo
    July 6, 2020 11:13 am

    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

    Reply

Leave a Reply

Menu