Unable to add external database server in Plesk: 1044 Access denied for user

Symptoms

Unable to add an external MySQL database server in Plesk:

Error: SQLSTATE[42000]: Syntax error or access violation: 1044 Access denied for user 'pleskadmin'@'192.0.2.2' to database 'example_db'

Cause

Not enough permissions for root user on remote database server.

Resolution

1. Connect to MySQL as root/super user on the remote database server

2. Grant permissions to the user used in Plesk:

   For MySQL:

   update user set Grant_priv = 'Y' where Host = '192.0.2.2' and User = 'pleskadmin';

   For MariaDB:

   GRANT ALL PRIVILEGES ON *.* TO `pleskadmin`@`192.0.2.2` WITH GRANT OPTION;

3. Flush the privileges:

   flush privileges;

4. Add external database server to Plesk