0

i am trying to setup a mysql server with some logins that can be accessed from anywhere, and another login ( effectively root access ) from a single machine with a static ip. both machines have a single WAN address and are located in the same hosted server facility. the only firewall present is the one built into the operating system.

Host ( 81.1.1.1 / SERVER-4XP5PS5 / mysql_win64 5.5 / svr2012r2 ):

firewall port translation :

netsh interface portproxy add v4tov4 listenport=5001 connectport=3306 connectaddress=81.1.1.1 protocol=tcp
Advanced Firewall Rules->Add inbound open port 'MySQL5001' / port 5001 / tcp / All connection types

mysql remote login :

create user 'testuser'@'82.2.2.2' identified by 'testpassword';
grant all on *.* to 'testuser'@'82.2.2.2';

Client ( 82.2.2.2 / mysql_win32 5.5 / svr2012r2 ):

mysql.exe -h81.1.1.1 -P5001 -utestuser -ptestpassword
ERROR 1045 (28000): Access denied for user 'testuser'@'SERVER-4XP5PS5' (using password: YES)

it seems the mysql server on the host thinks the connection is coming from the local machine, but it is not, i am running the mysql connect command from the client machine.

any ideas how to get mysql to get testuser to connect through port 5001 while restricting testuser connections to ip 82.2.2.2 ?

note : it works fine if i lose the ip address requirement and replace 82.2.2.2 with %, but that defeats the purpose of what i am trying to achieve.

  • 'testuser'@'82.2.2.2' and 'testuser'@'SERVER-4XP5PS5' - it is two DIFFERENT users. Create 'testuser'@'SERVER-4XP5PS5'. Read Specifying Account Names from the words "The server performs matching of host values ..." – Akina May 8 at 14:37
  • Additionally - try to insert the IP-Name pair into server's 'hosts' file to avoid illegal connections. – Akina May 8 at 14:41
  • What is the setting of skip-name-resolve? – Rick James May 8 at 17:03
  • skip-name-resolve is off, so i guess it's not a dns cache messing things up. – Barny May 8 at 17:24
0

Thanks for the contributions - although i have discovered a work around. Instead of using windows firewall to do port translation, i can just change my.ini and set my desired port. and instead of creating a user i just create the grant :

grant all on *.* to 'testuser'@'82.2.2.2' identified by 'testpassword';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.