MySQL connection fails with one database and not with other

0
votes

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 my local 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'

Some Extra Info

Since asp.net mvc checks for a database connection before launching the application I have a default database on the server which is defined as GEContext. As you can see the database name there is also ge. I have not changed this connection string at all so it still points to localhost.

1
mysqlasp.net-mvc-4

1 Answers

1
votes

You are missing the GRANT from your external IP for ge_sc001

Connect from external IP

The specific syntax (to run on the remote server) should be 

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

Where <external_ip> is your remote client EXTERNAL IP

A more general (I don't like it, too broad) would be:

GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '<password>' WITH GRANT OPTION;

--- SIDE NOTE ---

There was a straggling entry in mysql.user table that was interfering. Removing that manually and using FLUSH PRIVILEGES cleared things up