I have 2 servers. Server A (10.0.0.1) and Server B (10.0.0.2) [not real ips]. Server A houses the script to create databases on remote Server B. The databases are created on the fly. The script on Server A is as follows:
$this->dbh->exec("CREATE DATABASE `" . $name . "`;
CREATE USER '" . $username . "'@'localhost' IDENTIFIED BY '" . $password . "';
GRANT ALL PRIVILEGES ON `" . $name . "`.* TO '" . $username . "'@'%' IDENTIFIED BY PASSWORD '" . $password . "' WITH GRANT OPTION;
FLUSH PRIVILEGES;")
This script connects to Server B and uses a mysql admin account.
This creates the user and db on Server B just fine. Right after executing,
I close the current admin connection and open a new connection as the newly created user to create a couple tables.
This is when I receive the following error Fatal error: Uncaught exception 'Exception' with message 'SQLSTATE[28000] [1045] Access denied for user.......;
Now when I log into my hosting account and choose "Allow remote connections" I can then run the query fine from Server A as the new user. There seems to be something wrong with my grant permission from the get go. As you can see I use the wildcard % to allow a connection from any IP so I am not sure where the statement goes wrong. I can supply more information if needed. Any help is greatly, greatly appreciated.
Turns out all I had to do was replace "'@'%' IDENTIFIED BY PASSWORD '" with
"'@'%' IDENTIFIED BY '" "PASSWORD" was not needed. After I removed it, I was able to connect remotely.