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.