MySQL connection fails with one database and not the other

MySQLmysql-workbench

I have 2 databases on mysql. ge and ge_sc001.

I can access both of these through my asp.net mvc application locally.

Web.config

 <connectionStrings>
<add name="GEContext_sc001" connectionString="server=localhost;port=3306;uid=root;pwd=***;database=ge_sc001" providerName="MySql.Data.MySqlClient" />
<add name="GEContext_sc002" connectionString="server=localhost;port=3306;uid=root;pwd=***;database=ge" providerName="MySql.Data.MySqlClient" />
<add name="GEContext" connectionString="server=localhost;port=3306;uid=root;pwd=***;database=ge" providerName="MySql.Data.MySqlClient" />

Later on when i deployed my application on the web server, and tried to access them from a remote machine. I changed server=localhost to my machine's external-facing IP address. Now I can access only one ge. when i try to access the second it gives me error.

Access denied for user 'root'@'' to database 'ge_sc001'

Notice there is no localhost above i.e. 'root'@'localhost'

SHOW GRANTS

'GRANT ALL PRIVILEGES ON . TO \'root\'@\'localhost\' IDENTIFIED BY PASSWORD \'*xxx\' WITH GRANT OPTION' 'GRANT PROXY ON \'\'@\'\' TO \'root\'@\'localhost\' WITH GRANT OPTION'

UPDATE

I ran

GRANT ALL ON ge_sc001.* TO 'root'@'<external_ip>';

Now the error has change to

The provider did not return a ProviderManifestToken string.

This is now happening on both of my schemas

Best Answer

There was an extra entry in mysql.user table with host as '' that was interfering. Deleted it manually and running FLUSH PRIVILEGES worked for me.