Recently, one of my clients encountered an issue with a linked server between SQL Server and MySQL Server. As a SQL Server specialist, I found it interesting to install and connect MySQL Server to troubleshoot the problem. During my attempt to recreate the issue, I needed to create a linked server. In this blog post, I will guide you on how to fix the MySQL connection error: [MySQL][ODBC 5.3(w) Driver]Host ‘IP’ is not allowed to connect to this MySQL server.
After installing MySQL Server on a server and providing the root user’s password, I attempted to connect to MySQL Server using ODBC. However, upon clicking the test button, I encountered the following error message:
Connection Failed [MySQL][ODBC 5.3(w) Driver]Host ‘IP’ is not allowed to connect to this MySQL server.
This error was unfamiliar to me as I primarily work with SQL Server. Upon further research, I discovered that by default, MySQL does not allow remote clients to connect to the MySQL database. This can be verified by checking the mysql.user table, where an entry for the user ‘root’ with host ‘localhost’ exists. To resolve this issue, we need to grant permission for the client to connect to the MySQL Server.
Workaround/Solution
Before proceeding, it is important to ensure that the issue is not related to a firewall. Once confirmed, we can grant permission using the following command:
USE mysql;
GRANT ALL ON *.* TO root@'x.x.x.x' IDENTIFIED BY 'your-root-password';Alternatively, MySQL Workbench can also be used to grant permission. The steps are as follows:
- Launch MySQL Workbench.
- Connect to the MySQL Server.
- Select the ‘Users and Privileges’ tab.
- Click on the ‘Add Account’ button.
- Provide the username, password, and IP in the graphical screen.
- Click ‘Apply’ to save the changes.
After making the above changes, you should no longer encounter the “Host is not allowed to connect to this MySQL server” error message when attempting to connect to the MySQL database from the remote client (using the specified IP/hostname). It is worth noting that using ‘%’ to allow all hosts is an option, but it is not recommended.
I hope this blog post has been helpful in resolving the MySQL connection error in SQL Server. If you have any suggestions or alternative solutions, please feel free to leave a comment.