[Solved] MySQL Error: Host is not allowed to connect to this MySQL Server

kevinf
 
on Nov 20, 2022 10:06 PM
620 Views

Missing Server and User in Credentials

Host '192.168.0.4' is not allowed to connect to this MySQL server

To make my day I now got new error in the Server explorer all but 1 of my data connections vanished and if I try and refresh I get error shown in INSERT No 4 NOTE - I was able to recreate all connections) but still get Mouse drag operation failed

INSERT 3 is the Select * from mysql.user in Workbench

Do I add my password to password('######') and the '%' do I change it to 'localhost'

Tried your example (Insert NO 1) and added my password got error 1064 check for the right sintax to use near where I added my password ('######')  

INSERT No 1

INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', password(''######'));
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

Removed password and tried below got error Unknown Column password; in field list 

INSERT No 2

INSERT INTO mysql.user (Host, User, Password) VALUES ('%', 'root', '######');
GRANT ALL ON *.* TO 'root'@'%' WITH GRANT OPTION;

 INSERT No 3

# Host, User, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, Event_priv, Trigger_priv, Create_tablespace_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections, plugin, authentication_string, password_expired, password_last_changed, password_lifetime, account_locked, Create_role_priv, Drop_role_priv, Password_reuse_history, Password_reuse_time, Password_require_current, User_attributes
'localhost', 'mysql.infoschema', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', ?, ?, ?, '0', '0', '0', '0', 'caching_sha2_password', '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED', 'N', '2022-11-12 09:32:13', NULL, 'Y', 'N', 'N', NULL, NULL, NULL, NULL
'localhost', 'mysql.session', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', ?, ?, ?, '0', '0', '0', '0', 'caching_sha2_password', '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED', 'N', '2022-11-12 09:32:13', NULL, 'Y', 'N', 'N', NULL, NULL, NULL, NULL
'localhost', 'mysql.sys', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', ?, ?, ?, '0', '0', '0', '0', 'caching_sha2_password', '$A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED', 'N', '2022-11-12 09:32:13', NULL, 'Y', 'N', 'N', NULL, NULL, NULL, NULL
'localhost', 'root', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', ?, ?, ?, '0', '0', '0', '0', 'caching_sha2_password', '$A$005$+DF/^tQLP[~MC(s3G3GCim6C7bv41m4D2INKWugMyYlSbShNQYXrzvruAL1', 'N', '2022-11-12 09:32:17', NULL, 'N', 'Y', 'Y', NULL, NULL, NULL, NULL
Download FREE API for Word, Excel and PDF in ASP.Net: Download
kevinf
 
on Nov 21, 2022 11:29 PM

Thanks for you patience 

tried https://social.msdn.microsoft.com/Forums/en-US/d7194277-70bb-4123-9c8c-317a8c18a226/unable-to-add-data-connection-the-given-key-was-not-present-in-the-dictionary?forum=aspgettingstarted

got  my old server explorer data connections back and can create new data sourse using new username and password but still getting old error from origional question cashing_sha2_password failed

https://www.c-sharpcorner.com/article/to-overcome-the-given-key-was-not-present-in-the-dictionary-exception-in-mysql/

it said just select and delete “CHARACTER SET ‘utf8mb4’ ” and apply changes. Now all columns with “Collation” value become “Table default” after migration.