MySQL connection failure on deployment

Hi there

I've got an asp.net application which I've deployed to my shared hosting provider.  I've modified the connection string to point to the MySQL database stored on the shared server, and the page loads correctly.


However, when I run the page through the internet (as opposed to through VWD2008), I get an error: Unable to connect to any of the specified MySQL hosts.

I'm not sure where I've gone wrong.  The MySql.Data.dll; MySql.Data.Entity.dll and MySql.Web.dll are all in the /Bin folder.


I feel like I'm finally on the cusp of getting my app working!

Thanks in advance
k1ss0ffAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shinesh PremrajanEngineering ManagerCommented:
It could be possible that there is some user restrictions, check the user privileges.
flush the user privileges.

go to the command prompt and type mysql

mysql -h hostname -u username@ipaddress -p password

See if you are able to login ?
k1ss0ffAuthor Commented:
I can login from my local machine and use the application when running on localhost, it's the connection through the website that's failing?
JohnHECommented:
is the web server running on the same physical machine as the MySQL server?  If not, then you will have to add a user with the appropriate privileges to the database from the host that the web server is running on. For example:

CREATE USER 'webuser'@'webhost.example.com' IDENTIFIED BY 'some_pass';

Just replace the webhost.example.com in the above statement with the IP or name of the machine the webserver is running on.  The above doesn't assign any privileges to the account to access the actual database so you'd need to explicitly set that using something similar to:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    ->     ON mydb.*
    ->     TO 'webuser'@'webhost.example.com';

You'd have to replace mydb with the actual name of the database and also adjust the actual privileges you wish to give the user.  Hope this helps.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

k1ss0ffAuthor Commented:
Hmm...

My hosting company uses myphpadmin to manage the MySQL databases.  As part of the set up, I had to create a database account which logged me into myphpadmin, where I ran the script to create the database on the server.

I've now plugged in the username/password etc. that I used to create the database into my connection string, and have managed to connect to the database on the server, whilst running the asp app on my localhost. So i don't think it's a problem with the user account, but rather with the ASP application or something missing from it?

JohnHECommented:
K1ss0ff, you could very well be correct, but could you to log into phpmyadmin, click on the "databases" tab.  Click the link for your database, and then click on the privileges tab.  The page that loads should provide a list of users that have access to that database.  On that page, you should see your user listed one or more times depending upon the number of hosts that user has permission to connect to that database from.  What host(s) are shown on that page?
Shinesh PremrajanEngineering ManagerCommented:
Could you pls post the connection string here, so that we can help you out easily
k1ss0ffAuthor Commented:
The users of the database managed through my host's web interface rather than through myphpadmin, so this is what I see (attached)


<connectionStrings>
		<remove name="LocalMySqlServer"/>
		<add name="LocalMySqlServer" connectionString="Server=91.208.99.2;Port=3365;Database=passenge1_nreservices;Uid=passenge1_root;Pwd=123456;" providerName="MySql.Data.MySqlClient"/>
	</connectionStrings>

Open in new window

privileges.jpg
Shinesh PremrajanEngineering ManagerCommented:
Uid=myUsername@91.208.99.2;

Give a try on this
k1ss0ffAuthor Commented:
The hosting co has given me a different IP address to use - the 91... one is only for connecting from my development machine :/

Apologies for giving everyone the run around!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JohnHECommented:
Not completely sure, but it seems like my theory that the database connection was being refused because the host you were trying to connect to it from wasn't assigned to the database user was correct.  If nothing else, it should have brought to light the fact that the wrong host IP address was being used.
JohnHECommented:
Not completely sure, but it seems like my theory that the database connection was being refused because the host you were trying to connect to it from wasn't assigned to the database user was correct.  If nothing else, it should have brought to light the fact that the wrong host IP address was being used.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Software

From novice to tech pro — start learning today.