[Solved]Need Authentication Method Using Remote Database

databits

Hello, everyone. I'm in need of a authentication method for my vpn. I have 6 servers and it's really a headache adding accounts in each server and deleting each one when they expire. I'll appreciate any recommendation. Thank you. 😉

All VPS are CentOS 5.x x64.

Bebop

There must be *so* many ways to do this, so if someone sees a better method than mine, please do share.

Anyways, here goes my favorite tho....

Requirements

6 linux servers, each running a server instance of OpenVPN (why 6? Well, OP said 6 so I'll go with that)
1 server has mysql and apache on it. This server may, or may not, be 1 of the said 6. Up to you
Access to crontab and know-how to use crontab
ccd-exclusive directive in server.conf

Method

All 6 Linux servers call back to the apache server each 1 minute via cron
They each use a "wget" command to retrieve a plain text file (or encrypted if you must, but the data is not sensitive)
the text file is a list of username:status pairs
eg: John:enabled Kate:disabled Ryan:Enabled etc etc
The text file is a just a dump of username:status pairs from your mysql database

Its quite easy to setup. If you are interested I can elaborate further. If not, I'll stop here.

I'm interested to see ideas of others too.

[edit] I re-read your title "Remote MySQL Database". If the apache/text_file method is not appetizing to you, you can alternatively allow remote connections directly to the mysql database. I do find this to be less straight forward though (just me, if you're comfortable with it, then this does fit the framework I describe).

databits

That must work but I have to use database from Wordpress or WHMCS to organize accounts. Any idea?
Someone posted a set-up but I can't make it work. Like Here: https://forums.openvpn.net/topic3158.html

Mimiko

Hello.

I see that for authentication, only mysql is needed. Wordpress is pure for administering acounts.

My thinkings is to combine all servers in a private VPN from those used to clients. And you can deploy an instance of mysql rom wich you will get the users. Your primary questions does not clarify how these servers are bound toghether so I can only guess.

Another method is to use a main instance of mysql, but on other servers to install backup (read-only) instances of mysql. Each server with that secondary instance will make a sequre connection with main mysql and sincronyze. But this will be harder to configure.

And ofcourse Bebop's method.

On the link you found, every thing is describe fine and clear. Staing you can't make it working, you say nothing. Describe what you did, post the config files, rules, applications installed and logs.

Bebop

The how-to you link from is actually for a single linux machine with mysql (and by the way, its an excellent how-to. Much credit to the author). If you can get that to work for you, then I, or others here can show you how to adapt the model to go across 6 vpns. This is starting to convey as a mysql/wordpress/whmcs problem you might need to solve before you delve into the OpenVPN specifics.

databits

My plan is to make an external database for accounts(WP). To authenticate a user, the vpn server will connect to the external/remote database to check if the account exists. To do that, I will place a script for every vpn server. Script like in the link I provided. However, he used Ubuntu while I used CentOS 5.5. I followed the setup but all i got is "AUTH_FAILED" message.

Bebop

auth_fail is the openvpn error right... (not mysql)

Show client.conf + server.conf, sanitized with no #comments etc.

My suggestion is probably to focus on getting the VPNs working as a first setep. Mysql later..

databits

Yes, it's OpenVPN error. Please note, all my servers is CentOS 5.6 x64 and working fine but not when I change the authentication method to use remote MySQL.

Server Error:

WARNING: Failed running command (--auth-user-pass-verify): could not execute external program
TLS Auth Error: Auth Username/Password verification failed for peer

Edit.

Bebop

The script will have to be executable.

The best error test you can do first I think is to just run the script from the cmd line (not from openvpn), and see if it actually, connects the database and returns a user:pass combination. Keeping in mind that the how-to was written quite some time ago, for an older version of wordpress, its possible that the WP DB format has changed (but not necessarily true, just saying 'possible').

Also, MySql does not accept remote connections by default; only from localhost.

databits

When I changed it to executable, I got this from the server:

/etc/openvpn/vpn_auth.sh: line 11: mysql: command not found
WARNING: Failed running command (--auth-user-pass-verify): external program exited with error status: 1

That line is this:

sqldtb=`mysql -u $DBUSER -p$DBPASS -D $DBNAME -h $DBHOST --skip-column-name -e "SELECT * FROM wp_users WHERE ( (user_login = '$vpndtb') AND (user_pass = PASSWORD('$dtb01')) );"`

I think there is something wrong with that line.

Edit:
I installed MySQL but did not run it. Now the error is only this:

WARNING: Failed running command (--auth-user-pass-verify): external program exited with error status: 1

Bebop

I installed MySQL but did not run it. Now the error is only this:

WARNING: Failed running command (--auth-user-pass-verify): external program exited with error status: 1

If the script returns a non-zero value, it means there was no match in the username/password.

Also, if you just installed MySQL, did you also install WordPress and signup a new user in the database?

databits

Intalled MySQL in VPN server but Wordpress is on my webhost.
I think there is a possible effect in the encryption type.

Bebop

databits wroteIntalled MySQL in VPN server but Wordpress is on my webhost

Ok I got you now. Before you worry about encryption, have you confirmed that the script is actually making the connection to the remote DB? A few kinds of problems other than encryption can get in your way such as remote firewall (mysql listening port open?), or the remote version of mysql is not enabled for remote access (only localhost). Just some ideas anyways. Maybe you took care of this already, but I cannot see because no mention yet.

databits

Yessss! :lol: I got it working but using only password in plain text. 😉

Edit:

Damn. I can't make it work in WordPress database 'coz of pass encryption.

esson

I got it with this thing http://chagridsada.blogspot.com/2011/01/openvpn-system-based-on-userpass.html