OpenVPN System Based On User/Password Authentication with mysql &
Day Control (shell script) - Debian
System detail:
I. Install MySQL Server for User/Pass Authentication, IP = 2.2.2.2
1. Install MySQL Server
II. Install OpenVPN Server and generation of certificate, IP = 1.1.1.1
apt-get install mysql-server2. Log in MySQL as root
mysql -uroot -p3. Create the database 'openvpn'
CREATE DATABASE openvpn;4. Create a MySQL user with username 'USERNAME' and password 'PASSWORD'
GRANT ALL ON openvpn.* TO 'USERNAME'@"%" IDENTIFIED BY 'PASSWORD';5. Log out root user
exit;6. Log in MySQL as new user 'USERNAME'
mysql -uUSERNAME -pPASSWORD7. Switch database
USE openvpn;8. Create user, log table and insert user data - user table
CREATE TABLE IF NOT EXISTS `user` ( `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `user_pass` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '1234', `user_mail` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL, `user_phone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, `user_online` tinyint(1) NOT NULL DEFAULT '0', `user_enable` tinyint(1) NOT NULL DEFAULT '1', `user_start_date` date NOT NULL, `user_end_date` date NOT NULL, PRIMARY KEY (`user_id`), KEY `user_pass` (`user_pass`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;- log table
CREATE TABLE IF NOT EXISTS `log` ( `log_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `user_id` varchar(32) COLLATE utf8_unicode_ci NOT NULL, `log_trusted_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `log_trusted_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, `log_remote_ip` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL, `log_remote_port` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL, `log_start_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `log_end_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `log_received` float NOT NULL DEFAULT '0', `log_send` float NOT NULL DEFAULT '0', PRIMARY KEY (`log_id`), KEY `user_id` (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;- user data
INSERT INTO `user` ( `user_id`, `user_pass`, `user_mail`, `user_phone`, `user_online`, `user_enable`, `user_start_date`, `user_end_date` ) VALUES ( 'test', '1234', 'mr.tumcpe@gmail.com', '+66815447514', 0, 1, '2012-01-01', '0000-00-00' );9. Show tables
show tables; +-------------------+ | Tables_in_openvpn | +-------------------+ | log | | user | +-------------------+10. Show user data
select * from user; +---------+-----------+---------------------+--------------+-------------+-------------+-----------------+---------------+ | user_id | user_pass | user_mail | user_phone | user_online | user_enable | user_start_date | user_end_date | +---------+-----------+---------------------+--------------+-------------+-------------+-----------------+---------------+ | test | 1234 | mr.tumcpe@gmail.com | +66815447514 | 0 | 1 | 2012-01-01 | 0000-00-00 | +---------+-----------+---------------------+--------------+-------------+-------------+-----------------+---------------+11. Log out
exit;12. Edit file /etc/mysql/my.cnf insert # to line
bind-address = 127.0.0.1success
#bind-address = 127.0.0.113. Edit file /etc/rc.local Add before exit 0;
iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT14. Run Script Iptables
/etc/rc.local iptables-save
1. Install OpenVPN
III. Customize shell script, IP = 1.1.1.1
apt-get install openvpn2. Generate the certificate Copy the certificate generator scripts from OpenVPN docs
cp -R /usr/share/doc/openvpn/examples/easy-rsa /etc/openvpn/. cd /etc/openvpn/easy-rsa/2.0/3. Modify certificate variables
vi varsEdit this file and change the following lines into your case
export KEY_COUNTRY="TH" export KEY_PROVINCE="BKK" export KEY_CITY="Bangkok" export KEY_ORG="Chtunnel-VPN" export KEY_EMAIL="support@chtunnel.com"4. Save and exit. Run the variable script and clean
source ./vars ./clean-all5. Generate the public and private certificates. Just press ENTER or YES by default
./build-ca ./build-key-server server ./build-key client ./build-dh mv keys /etc/openvpn/.In fact, generation of client is not necessary for a User/Pass authentication approach.
1. Create directory for script '/etc/openvpn/script'
For each file, it forks a daemon. In this system,
we need both UDP and TCP support. I created two configuration files for two daemons in charge of UDP and TCP respectively. 7. Create file server-tcp-443.conf '/etc/openvpn/server-tcp-443.conf' for Server Port:443
IV. Share Internet to Client
mkdir /etc/openvpn/script cd /etc/openvpn/script2. Create file config.sh '/etc/openvpn/script/config.sh'
#!/bin/bash ##Dababase Server HOST='2.2.2.2' #Default port = 3306 PORT='3306' #Username USER='USERNAME' #Password PASS='PASSWORD' #database name DB='openvpn'3. Create file test_connect_db.sh'/etc/openvpn/script/test_connect_db.sh'
#!/bin/bash . /etc/openvpn/script/config.sh ##Test Authentication username=$1 password=$2 user_id=$(mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -sN -e "select user_id from user where user_id = '$username' AND user_pass = '$password' AND user_enable=1 AND user_start_date != user_end_date AND TO_DAYS(now()) >= TO_DAYS(user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user_end_date) OR user_end_date='0000-00-00')") ##Check user [ "$user_id" != '' ] && [ "$user_id" = "$username" ] && echo "user : $username" && echo 'authentication ok.' && exit 0 || echo 'authentication failed.'; exit 14. Create file login.sh '/etc/openvpn/script/login.sh'
#!/bin/bash . /etc/openvpn/script/config.sh ##Authentication user_id=$(mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -sN -e "select user_id from user where user_id = '$username' AND user_pass = '$password' AND user_enable=1 AND user_start_date != user_end_date AND TO_DAYS(now()) >= TO_DAYS(user_start_date) AND (TO_DAYS(now()) <= TO_DAYS(user_end_date) OR user_end_date='0000-00-00')") ##Check user [ "$user_id" != '' ] && [ "$user_id" = "$username" ] && echo "user : $username" && echo 'authentication ok.' && exit 0 || echo 'authentication failed.'; exit 15. Create file connect.sh '/etc/openvpn/script/connect.sh'
#!/bin/bash . /etc/openvpn/script/config.sh ##insert data connection to table log mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "INSERT INTO log (log_id,user_id,log_trusted_ip,log_trusted_port,log_remote_ip,log_remote_port,log_start_time,log_end_time,log_received,log_send) VALUES(NULL,'$common_name','$trusted_ip','$trusted_port','$ifconfig_pool_remote_ip','$remote_port_1',now(),'0000-00-00 00:00:00','$bytes_received','$bytes_sent')" ##set status online to user connected mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE user SET user_online=1 WHERE user_id='$common_name'"6. Create file disconnect.sh '/etc/openvpn/script/disconnect.sh'
#!/bin/bash . /etc/openvpn/script/config.sh ##set status offline to user disconnected mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE user SET user_online=0 WHERE user_id='$common_name'" ##insert data disconnected to table log mysql -h$HOST -P$PORT -u$USER -p$PASS $DB -e "UPDATE log SET log_end_time=now(),log_received='$bytes_received',log_send='$bytes_sent' WHERE log_trusted_ip='$trusted_ip' AND log_trusted_port='$trusted_port' AND user_id='$common_name' AND log_end_time='0000-00-00 00:00:00'"Compose OpenVPN configuration files, OpenVPN server will scan .conf files in /etc/openvpn when it starts.
For each file, it forks a daemon. In this system,
we need both UDP and TCP support. I created two configuration files for two daemons in charge of UDP and TCP respectively. 7. Create file server-tcp-443.conf '/etc/openvpn/server-tcp-443.conf' for Server Port:443
##protocol port port 443 proto tcp dev tun ##ip server client server 10.4.0.0 255.255.255.0 ##key ca /etc/openvpn/keys/ca.crt cert /etc/openvpn/keys/server.crt key /etc/openvpn/keys/server.key dh /etc/openvpn/keys/dh1024.pem ##option persist-key persist-tun keepalive 5 60 reneg-sec 432000 ##option authen. comp-lzo user nobody #group nogroup client-to-client username-as-common-name client-cert-not-required auth-user-pass-verify /etc/openvpn/script/login.sh via-env ##push to client max-clients 50 push "persist-key" push "persist-tun" push "redirect-gateway def1" #push "explicit-exit-notify 1" ##DNS-Server push "dhcp-option DNS 8.8.8.8" push "dhcp-option DNS 8.8.4.4" ##script connect-disconnect script-security 3 system client-connect /etc/openvpn/script/connect.sh client-disconnect /etc/openvpn/script/disconnect.sh ##log-status status /etc/openvpn/log/tcp_443.log log-append /etc/openvpn/log/openvpn.log verb 38. Create file server-udp-53.conf '/etc/openvpn/server-udp-53.conf' for Server Port:53
##protocol port port 53 proto udp dev tun ##ip server client server 10.5.0.0 255.255.255.0 ##key ca /etc/openvpn/keys/ca.crt cert /etc/openvpn/keys/server.crt key /etc/openvpn/keys/server.key dh /etc/openvpn/keys/dh1024.pem ##option persist-key persist-tun keepalive 5 60 reneg-sec 432000 ##option authen. comp-lzo user nobody #group nogroup client-to-client username-as-common-name client-cert-not-required auth-user-pass-verify /etc/openvpn/script/login.sh via-env ##push to client max-clients 50 push "persist-key" push "persist-tun" push "redirect-gateway def1" push "explicit-exit-notify 1" ##DNS-Server push "dhcp-option DNS 8.8.8.8" push "dhcp-option DNS 8.8.4.4" ##script connect-disconnect script-security 3 system client-connect /etc/openvpn/script/connect.sh client-disconnect /etc/openvpn/script/disconnect.sh ##log-status status /etc/openvpn/log/udp_53.log log-append /etc/openvpn/log/openvpn.log verb 39. Create directory for log '/etc/openvpn/log'
mkdir /etc/openvpn/log touch /etc/openvpn/log/openvpn.log touch /etc/openvpn/log/tcp_443.log touch /etc/openvpn/log/udp_53.log10. Changes the permission of files
chmod -R 755 /etc/openvpn11. Test authentication username 'test' and password '1234'
/etc/openvpn/script/test_connect_db.sh test 1234 # user : test # authentication ok. # if authentication failed. check user and password in database # or detail database server in /etc/openvpn/script/config.sh12. Start serviece OpenVPN
/etc/init.d/openvpn start
1. Edit file /etc/sysctl.conf Remove # In line : #net.ipv4.ip_forward=1
V. Config for Client
net.ipv4.ip_forward=13. Edit file /etc/rc.local Add before exit 0;
echo "1" > /proc/sys/net/ipv4/ip_forward echo "1" > /proc/sys/net/ipv4/ip_dynaddr iptables -A INPUT -i tun0 -j ACCEPT iptables -A FORWARD -i tun0 -j ACCEPT iptables -A INPUT -i tun1 -j ACCEPT iptables -A FORWARD -i tun1 -j ACCEPT iptables -A INPUT -p udp --dport 53 -j ACCEPT iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT iptables -t nat -A POSTROUTING -s 10.4.0.0/24 -o eth0 -j MASQUERADE iptables -t nat -A POSTROUTING -s 10.5.0.0/24 -o eth0 -j MASQUERADE4. Run Script Iptables Share Internet
/etc/rc.local iptables-save
1. Config for port TCP port 443
Day of user in database
client dev tun proto tcp remote 1.1.1.1 443 nobind auth-user-pass reneg-sec 432000 resolv-retry infinite ca ca.crt comp-lzo verb 12. Config for port UDP port 53
client dev tun proto udp remote 1.1.1.1 53 nobind auth-user-pass reneg-sec 432000 resolv-retry infinite ca ca.crt comp-lzo verb 13. Copy file ca.crt from /etc/openvpn/keys/ca.crt to same config path in client
# If today = '2012-01-01' # day = user_start_date | user_end_date # 0 = 0000-00-00 | 0000-00-00 # 0 = 2012-01-01 | 2012-01-01 # 0 = 2012-01-02 | 2012-01-01 # 1 = 2012-01-01 | 2012-01-02 # unlimited = 2012-01-01 | 0000-00-00Install finish.
39 comments:
Thank you very much for this. I used this to centralize the authentication of my VPN servers.
My next project is to create registration and monitoring pages in PHP even though I am not good with PHP.
Hi
Do you have knowledge on how to implement this on a remote mysql server?
lets say ServerA has a mysql installed
Server B and Server C will query on the Remote MySql of ServerA when clients will connect to either ServerB or ServerC.
Hope you get my question.
Thank you.
how can i connect or create web based using sql database or how to synchronize this database to other database?
hope you could create tutorial for web based using this sql
thank you
using ubuntu veraion what
Version
Success new free version. 13 june 2012
how if i using webhost??
port delete??
please repost your old tutorial.
i cannot use this
im using webhost for mysql auth
old tutorial
http://chagridsada.blogspot.com/2012/06/openvpn-system-based-on-user-pass-lib.html
why i cannot auth after a few hour,
i have to restart again and again few hour
echo "1" > /proc/sys/net/ipv4/ip_dynaddr
what for??
hello.can tell me how to get protocol type in mysql
ruzainiourtech.com/capture.png
example for geting trusted_ip is $trusted_ip
how about protocol
UDP,TCP
Try $proto_1
echo "1" > /proc/sys/net/ipv4/ip_dynaddr
http://www.mjmwired.net/kernel/Documentation/networking/ip_dynaddr.txt
EXCELLENT !!!
It would be much easier and better if all of this ported to PHP or another scripting language so we can manage everythings from web browser.
Input, update, username and password from an admin panel ... etc.
And end user just need to register through our web pages.
Just an idea.
admin
can u custom coding openvpn to make icmp tunneling using mysql authentication like this.
all my isp in my country block many udp n tcp port
credit 4 u
thank's this is very helpful... great!!
hello Mr.Tum
your script is helping me out very effectively
first of all thanks for this script and tutorial
but i have only 1 question if a user don't disconnect and use reconnect option than the main problem arises no disconnection take place, no user table updated and row in log table never shows data received and send and an end time of the client
can you do something in regard to this problem
very great!!!
Thank you much!!!
how to see the total usage , by a single user?
and also i want to know how to prevent multiple login from same user id. i want to make 1 user id per user...
Hello if a user tries to login to the same server from multiple devices at the same time, they are rejected. Is there anyway that two users can use the same account at the same time?
in bash script, how to make $password in md5 or $password in md5 with salt?
I'm getting this error when testing the connection: /etc/openvpn/script/test_connect_db.sh: line 6: mysql: command not found
authentication failed.
I'm not hosting the mySQL database on this server, it's on another but I made sure to enter the right login information in etc/openvpn/script/config.sh. And ideas why it would give me an error on line 6?
Justin Miller:
did you allow outside/remote access to your remote mysql server
how make 1 user single connection with this?
why UDP not working
Which is the best and cheap web host?
Unlimited Subdomains
Your Post is very useful, I am truly happy to post my note on this blog . It helped me with ocean of awareness so I really consider you will do much better in the future.
best domain name registration
Hi, I have a question: I modified your script to keep the IP assigned from ccd dir generated from mysql and keep password saved in md5. With this scenario I have 2 problems:
1) even if --duplicate-cn is not in my config, a user can login multiple times and have multiple client connected
2)WARNING: POTENTIALLY DANGEROUS OPTION --client-cert-not-required may accept clients which do not present a certificate
Do you have idea of how to solve this?
Cheapest RDP Windows vpn linux on earth !
Try this :
Cheap-RDP
i really enjoyed a lot from this blog ui course online
This post is really nice and informative. The explanation given is really comprehensive and informative on windows azure training
Passwords are stored UNENCRYPTED in the database.
Pity, this could be easily fixed using the mysql PASSWORD function or any SHA within the script
Dave
----
htttp://www.netexpertise.eu
It also raises some major security issues such as SQL injections making it INSECURE
You can easily connect to the VPN just knowing a user login WITHOUT any password, since the password in the database isn't checked against the one in the form
Replace the password with something like:
' or user_id='VALIDUSER'; --
And you're IN!
Dave
----
http://www.netexpertise.eu
instagram takipçi satın al
ucuz takipçi
takipçi satın al
https://takipcikenti.com
https://ucsatinal.org
instagram takipçi satın al
https://perdemodelleri.org
https://yazanadam.com
instagram takipçi satın al
balon perdeler
petek üstü perde
mutfak tül modelleri
kısa perde modelleri
fon perde modelleri
tül perde modelleri
https://atakanmedya.com
https://fatihmedya.com
https://smmpaketleri.com
https://takipcialdim.com
https://yazanadam.com
yasaklı sitelere giriş
aşk kitapları
yabancı şarkılar
sigorta sorgula
https://cozumlec.com
word indir ücretsiz
tiktok jeton hilesi
rastgele görüntülü sohbet
fitness moves
gym workouts
https://marsbahiscasino.org
http://4mcafee.com
http://paydayloansonlineare.com
perde modelleri
sms onay
vodafone mobil ödeme bozdurma
Nft nasıl alınır
ANKARA EVDEN EVE NAKLİYAT
Trafik sigortasi
dedektör
Web sitesi kurma
ASK KİTAPLARİ
smm panel
SMM PANEL
iş ilanları
instagram takipçi satın al
hirdavatciburada.com
beyazesyateknikservisi.com.tr
SERVİS
Tiktok Jeton Hile
Post a Comment