Koozali.org: home of the SME Server

[SOLVED] Can not access mysql database thru vpn

Offline jysse

  • *
  • 16
  • +0/-0
[SOLVED] Can not access mysql database thru vpn
« on: November 01, 2007, 09:26:04 AM »
Hi,

I'm trying to use a piece of software that requires connection to mysql databases. It uses Mysql ODBC connector 3.51.12 to this.
Connection is established thru vpn which works fine. I'm able to use ssh and connection to database in ssh session works fine with the same username + password.
I'm getting an error message like this when trying a remote connection:
[MySQL][ODBC 3.51 Driver]Access denied for user 'dbuser'@'pcname.domain.com' (using password: YES)

This is what I have done before:
config setprop mysqld LocalNetworkingOnly no
 expand-template /etc/my.cnf
 /etc/rc.d/init.d/mysqld restart


config set mysqld service access public status enabled TCPPort 3306
 signal-event remoteaccess-update
 signal-event reboot


config show mysqld

-> mysqld=service
    TCPPort=3306
    access=public
    status=enabled

I'm not an expert with mysql but gotta feeling that I have not established enough privileges to this user. By the way I gave rights like this:

 grant all privileges on dbname.* to dbuser@'192.168.1.0/255.255.255.0' identified by 'password';


Any ideas ?

Thanks, Jysse

06112007

I did as I was told to in this thread. First, same error messages as before until I noticed that in ODBC- config database name was wrong...
First letter of database name was in capital letter. Hmm, a little bit shamed.

« Last Edit: November 06, 2007, 08:45:35 PM by jysse »

Offline dmajwool

  • *****
  • 180
  • +0/-0
Re: Can not access mysql database thru vpn
« Reply #1 on: November 01, 2007, 11:57:06 AM »
can  dbuser connect to the database from a workstation actually on the 192.168.1.xx subnet?

I'm thinking that the vpn station may be interpreted as being on a different subnet and may need mysql privileges for that vpn subnet.

HTH, David

Offline jysse

  • *
  • 16
  • +0/-0
Re: Can not access mysql database thru vpn
« Reply #2 on: November 01, 2007, 12:54:37 PM »
ok, I can't test that right now but I will.
My workstation gets an ip from the server when I'm using VPN.
Do you know what kind of privileges can I test with this ?

Jysse

Offline raem

  • *
  • 3,972
  • +4/-0
Re: Can not access mysql database thru vpn
« Reply #3 on: November 01, 2007, 03:10:44 PM »
jysse

Quote
I'm not an expert with mysql but gotta feeling that I have not established enough privileges to this user. By the way I gave rights like this:
grant all privileges on dbname.* to dbuser@'192.168.1.0/255.255.255.0' identified by 'password';

That should be
grant all privileges on dbname.* to dbuser@'localhost' identified by 'password';
grant all privileges on dbname.* to dbuser@'%' identified by 'password';

Refer to the mysql online manual, Users section (I think).
...

Offline raem

  • *
  • 3,972
  • +4/-0
Re: Can not access mysql database thru vpn
« Reply #4 on: November 01, 2007, 03:17:04 PM »
jysse

Quote
Connection is established thru vpn which works fine.

config set mysqld service access public status enabled TCPPort 3306
 signal-event remoteaccess-update
 signal-event reboot

You have opened access to ALL your mysql databases to ANYONE.
They are now only protected by the strength of your usernames & passwords.
If you intend to connect via VPN, then you do not need to enable Public access, as VPN is considered as local (Private) access.

It is advisable to disable Public access immediately.

...

Offline cactus

  • *
  • 4,880
  • +3/-0
    • http://www.snetram.nl
Re: Can not access mysql database thru vpn
« Reply #5 on: November 01, 2007, 04:15:20 PM »
jysse

That should be
grant all privileges on dbname.* to dbuser@'localhost' identified by 'password';
grant all privileges on dbname.* to dbuser@'%' identified by 'password';

Refer to the mysql online manual, Users section (I think).

Using the ip/netmask option should work as well, see http://dev.mysql.com/doc/refman/4.1/en/connection-access.html . Did you also issue the flush privileges command? I seem to remember that this is still required in MySQL version 4 to refresh user privileges, I made a habit of it and often issue the command on MySQL version 5 as well.
Be careful whose advice you buy, but be patient with those who supply it. Advice is a form of nostalgia, dispensing it is a way of fishing the past from the disposal, wiping it off, painting over the ugly parts and recycling it for more than its worth ~ Baz Luhrmann - Everybody's Free (To Wear Sunscreen)