Koozali.org: home of the SME Server

Contribs.org Forums => General Discussion => Topic started by: kevincallan on December 04, 2009, 05:42:49 PM

Title: MySQL access denied from local network
Post by: kevincallan on December 04, 2009, 05:42:49 PM: 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.
Title: Re: MySQL access denied from local network
Post by: chris burnat on December 04, 2009, 09:30:06 PM: Moving to General Discussion where it is more appropriate.
Title: Re: MySQL access denied from local network
Post by: cactus on December 05, 2009, 01:51:33 AM: Quote from: kevincallan on December 04, 2009, 05:42:49 PM
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 (http://dev.mysql.com/doc/refman/4.1/en/grant.html) as well as in the wiki (http://wiki.contribs.org/MySQL#Creating_MySQL_user.28s.29).
Title: Re: MySQL access denied from local network
Post by: janet on December 05, 2009, 02:59:16 PM: 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
Title: Re: MySQL access denied from local network
Post by: mmccarn on December 05, 2009, 05:02:13 PM: 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.
Title: Re: MySQL access denied from local network
Post by: cactus on December 05, 2009, 05:41:13 PM: Quote from: mmccarn on December 05, 2009, 05:02:13 PM
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.
Title: Re: MySQL access denied from local network
Post by: CharlieBrady on December 05, 2009, 06:39:22 PM: Quote from: cactus on December 05, 2009, 05:41:13 PM
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.
Title: Re: MySQL access denied from local network
Post by: kevincallan on December 10, 2009, 08:56:32 PM: 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.
Title: Re: MySQL access denied from local network
Post by: kevincallan on December 12, 2009, 07:01:08 PM: 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. :-)