phpMyAdmin - #1130 - Host 'SERVER' is not allowed to connect to this MySQL server

Question

I run WAMPServer on a Windows server 2012 r2, I want to run a database on it using phpMyAdmin, everything is working but when I try to login from the Windows server I get this error:

is the error that I see.

I get the same error when I try to login from a other computer.

There are allot of people having this problem so I searched all over the internet but all I can find are query's that can fix this problem.. But I can't login so I can't do anything with those query's.

I hope someone can fix this problem for me, I would really appreciate it!

The two errors I get on the picture:

#1130 - Host 'SERVER' is not allowed to connect to this MySQL server
mysqli_real_connect(): (HY000/1130): Host 'SERVER' is not allowed to connect to this MySQL server

Answer 1

WAMPServer is configired to be a single user developer tool. Therefore the security is configured to protect the beginner.

Therefore:

1. The phpMyAdmin alias is configured to only accept connections from the PC running WAMPServer.
2. As in any sensibe default config of MYSQL the root and any other pre-configured MYSQL user accounts are also configured to only be accessible from the PC running MYSQL.

So to allow you to run phpMyAdmin from another host (PC) you need to amend the Alias configuration for phpMyAdmin.

So edit \wamp\alias\phpmyadmin.conf (NOTE your version may be different) and amend the Require setting to allow access from specific, or all the Ip Addresses in yor subnet.

Alias /phpmyadmin "D:/wamp/apps/phpmyadmin4.7.0/"

<Directory "D:/wamp/apps/phpmyadmin4.7.0/">
    Options Indexes FollowSymLinks MultiViews
  AllowOverride all
  <ifDefine APACHE24>
        Require local

        ## Add access allowed from your subnet
        Require ip 192.168.1

    </ifDefine>
    <ifDefine !APACHE24>
        Order Deny,Allow
    Deny from all
    Allow from localhost ::1 127.0.0.1
    </ifDefine>

# To import big file you can increase values
  php_admin_value upload_max_filesize 128M
  php_admin_value post_max_size 128M
  php_admin_value max_execution_time 360
  php_admin_value max_input_time 360
</Directory>

Now you should be able to run phpMyAdmin from another PC

You must now create a user within MYSQL that is allowed to access the database(s) that that user requires access to from whichever remote IP Addresses that user is allowed to access the database from. You should go to the Server and using phpMyAdmin, login as root from there, remember root can only login from the PC running MYSQL.

I suggest you do not amend the access rights of root but instead create a new user and give whatever access that user is allowed to which ever databases that user is allowed to access.

For example,

CREATE USER 'raul'@'192.168.1.%' IDENTIFIED BY 'mypass' PASSWORD EXPIRE NEVER;

Will allow you to login from any of the ip addresses in the 192.168.1 subnet. Amend this to fit your actual situation.

And then allow that user access to the databases he will require.

GRANT ALL ON test.* TO 'raul'@'localhost';

This is all possible using point and click via phpMyAdmin

Answer 2

I managed to fix this by going to wamp\alias\phpmyadmin.conf and made it like this:

Alias /phpmyadmin "F:/SERVER/apps/phpmyadmin4.9.2/"

<Directory "F:/SERVER/apps/phpmyadmin4.9.2/">
    Options Indexes FollowSymLinks MultiViews
  AllowOverride all
  <ifDefine APACHE24>
        Require local
        Require ip 192.168.1
        Require ip 192.168.2
        Require ip 127.0.0.1
    </ifDefine>
    <ifDefine !APACHE24>
        Order Deny,Allow
        Deny from all
        Allow from localhost ::1 127.0.0.1
    </ifDefine>

# To import big file you can increase values
  php_admin_value upload_max_filesize 128M
  php_admin_value post_max_size 128M
  php_admin_value max_execution_time 360
  php_admin_value max_input_time 360
</Directory>

AND hosts from C:\Windows\System32\drivers\etc\hosts and added

127.0.0.1 localhost
127.0.0.1 127.0.0.1
::1 localhost

Also needed to go to F:\SERVER\bin\mysql\mysql5.7.28\my.ini and add or uncommment

[wampmysqld64]
skip-grant-tables

In order to do the steps above with grant tables as is not possible to log in to mysql if you don't have the option skip-grant-tables and execute the sql from @RiggsFolly