Topic: MySQL access denied from local network

[kevincallan]

I am trying to connect to my SME 7.4 box with either MySQL Administor or NavCat.  I followed the instructions (reluctantly) at http://wiki.contribs.org/SME_Server:Documentation:FAQ#Access_MySQL_from_the_local_network to allow access.  (Both tools require port connection it seems so I did the second step as well.)  Did that open my database to the WAN side also?

When I try to connect from the LAN side, I get an error message that states, "Could not connect to the specified instance.  MySQL Error Number 1130.  Host 'pc-00242.mydomain.net' is not allowed to connect to this MySQL server.  If you want to check the network connection, please click the Ping button."

I've browsed the forums and the docu for several hours but have not found anything.  If I have done badly with the mods to the server by opening the port to the WAN, would one of you experts mind to tell me how to undo it?  I figured out how to reverse the first step but not the second.

Thanks.

[chris burnat]

Moving to General Discussion where it is more appropriate.

[cactus]

When I try to connect from the LAN side, I get an error message that states, "Could not connect to the specified instance.  MySQL Error Number 1130.  Host 'pc-00242.mydomain.net' is not allowed to connect to this MySQL server.  If you want to check the network connection, please click the Ping button."

This is most likely a internal MySQL permission error caused by the permissions (ACL) definded in MySQL. You will have to create a user that has privileges to access the database from another host than localhost (perhaps using the wildcard %, %.domain.tld or when really desiring to be strict pc-00242.mydomain.net).
Pointers for this can be found in the MySQL documentation when looking for the GRANT statement as well as in the wiki.

[janet]

kevincallan

You should always check existing settings before making changes so you know what to revert to.

config show mysqld

To undo remote mysql access
config set mysqld service access private status enabled TCPPort 3306
signal-event remoteaccess-update
signal-event reboot

Also see
http://forums.contribs.org/index.php/topic,38998.msg177735.html#msg177735
and
http://forums.contribs.org/index.php/topic,37973.msg171053.html#msg171053

[mmccarn]

I've added a wiki section about creating database users with appropriate access rights: http://wiki.contribs.org/MySQL#Create_MySQL_user.28s.29_with_access_from_other_computers

Please let us know if these instructions work, or if they need fine tuning.

[cactus]

I've added a wiki section about creating database users with appropriate access rights: http://wiki.contribs.org/MySQL#Create_MySQL_user.28s.29_with_access_from_other_computers

Please let us know if these instructions work, or if they need fine tuning.

I have added a tip to use hostnames in certain cases (like dynamically assigned IP addresses). I personally prefer to assign access restrictions using hostname over IP as IMHO IP addresses change more often than hostnames.

[CharlieBrady]

I have added a tip to use hostnames in certain cases (like dynamically assigned IP addresses). I personally prefer to assign access restrictions using hostname over IP as IMHO IP addresses change more often than hostnames.

That's only a minor reason. The major reason for using IP addresses rather than hostnames is that hostnames can be spoofed by attackers, if they control reverse DNS.

[kevincallan]

It apparently has been a while--my post alerts went to my old email address.  When I requested an email change in my profile, my browser crashed and I couldn't log it.  Just today could I post.  Thank you all for the help.  I will be trying the suggestions and will report back here soon.

[kevincallan]

I now have access to the database from the LAN.  Thanks to all of you for your help.  The wiki is a great resource on this topic.

Thanks.