Syed Jahanzaib Personal Blog to Share Knowledge !

August 7, 2019

Exploiting Mikrotik for Good ?

Filed under: Mikrotik Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 11:30 AM

mikrotik exploit logo.png

Last updated: 7-AUGUST-2019 / 1400 hours

Note: Lot have been written on this vulnerability & this is not something NEW, but this vulnerability helped us in accessing one of our remote site old router with forgotten credentials.


In our remote branch network , we had installed one Mikrotik small box RB750 for branch to HO connectivity. This small unit was installed few years back & we never looked into it again. Recently we needed to make some configuration changes but following some bad practices we didn’t added this particular mikrotik in our centralized automated backup system & we didn’t noted down the credentials & IP configurations of its VPN settings after its deployment considering it less important.

Luckily it was running old firmware which was exploitable dueto to its critical WinBox vulnerability (CVE-2018-14847) which allows for arbitrary file read of plain text passwords.


Index of this post

  1. Vulnerable Versions
  2. Requirements
  3. Executing scripts in linux
  4. Results
  5. Securing older version with firewalling
  6. Securing the Mikrotik Router at basics

Vulnerable Versions

Versions affected:

  • Affected all bugfix releases from 6.30.1 to 6.40.7, fixed in 6.40.8 on 2018-Apr-23
  • Affected all current releases from 6.29 to 6.42, fixed in 6.42.1 on 2018-Apr-23
  • Affected all RC releases from 6.29rc1 to 6.43rc3, fixed in 6.43rc4 on on 2018-Apr-23

For more information see: https://blog.mikrotik.com/security/winbox-vulnerability.html

Using this exploit we were able to recover the password and after changes we upgraded it immediately.

We can use Windows or Linux to remotely exploit the older mikrotik firmware to query for all user accounts.


Requirements:

The scripts can be run using PYTHON version 3+ & I have uploaded the scripts @ my Google Drive.


Driving in Linux !

  • I have tested it with Ubuntu ver 12 & 16
sudo apt-get update
sudo apt-get install python3

Now extract scripts in any temp folder.

Executing the scripts …

Extract users details using the Remote Mikrotik IP address [default 8291 port]

python3 WinboxExploit.py 10.0.0.1

Extract users details using the Remote Mikrotik IP address [custom port]

python3 WinboxExploit.py 10.0.0.1 1122

Discover Mikrotik on the network

(it will scan the network for Mikrotik, may take some time, or you can press CTRL+C to exit)

python3 MACServerDiscover.py

 

Extract users details using the Remote Mikrotik MAC Address

python3 MACServerExploit.py e4:8d:8c:9a:ed:11

Results:

mikrotik winbox exploit results.PNG

 

If the firmware is latest or not exploitable, it will give error “Exploit failed


# Securing older version with firewalling

If you dont want to upgrade, than at least use firewall filter to secure older versions……

/ip firewall filter
add action=reject chain=input comment="block CVE-2018-14847 exploit by z@ib" content=user.dat
add action=drop chain=input content="user.dat"

# Securing the Mikrotik Router at basics

  • TOP OF THE LINE THING TO DO : apply port scanning filtering !
  • Remotely Accessible Router Services should be limited to few addresses/interfaces
  • Never use default ports for Winbox / SSH & other services
  • Change there ports number to preferably higher unused ports like 50000 or above or likewise
  • If not in use, Disable all services like FTP / SSH & others
  • Never use default usernames like ADMIN , disable or delete them, and make alternate admin accounts with difficult passwords

Disable following

  • MAC-telnet services
    /tool mac-server set allowed-interface-list=none
  • MAC-Winbox
    /tool mac-server mac-winbox set allowed-interface-list=none
  • MAC-Ping service
    /tool mac-server ping set enabled=no
  • MikroTik Neighbor discovery protocol
    /ip neighbor discovery-settings set discover-interface-list=none
    /ipv6 nd set [find] disabled=yes
  • DNS cache
    /ip dns set allow-remote-requests=no
  • Socks proxy
    /ip proxy set enabled=no
    /ip socks set enabled=no
  • UPNP service
    /ip upnp set enabled=no
  • MikroTik dynamic name service or ip cloud
    /ip cloud set ddns-enabled=no update-time=no
  • Enable More Secure SSH access
    /ip ssh set strong-crypto=yes

Regard’s
Syed Jahanzaib

July 23, 2019

RM: Delete Expired Users Record

Filed under: Radius Manager — Tags: — Syed Jahanzaib / Pinochio~:) @ 12:57 PM

expired.jpg


Following script was made for DMA Radius Manager 4.1.x. It can delete X months old Expired users record from the mysql DB.

Sharing for reference purposes …

WordPress is not letting proper pasting of the code …

delete expired uesr.PNG

#!/bin/sh
#set -x
# This script delets users who have expired 2 months ago. and then delete there records from all tables.
# Syed Jahanzaib / June 2019
SQLPASS=”SQLPASS”
export MYSQL_PWD=$SQLPASS
> /tmp/expired.users.txt

#mysql -uroot -e “use radius; select username from rm_users where expiration BETWEEN ‘2010-01-01’ AND ‘2019-04-30’;” |sort > /tmp/expired.users.txt

# Fetch users who have expired 2 months ago & before, (using expired date), BE CAREFUL WHEN USING THIS
mysql -uroot -e “use radius; select username from rm_users where expiration <= DATE_SUB(CURDATE(), INTERVAL 2 MONTH)” |sort > /tmp/expired.users.txt
num=0
cat /tmp/expired.users.txt | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk ‘{print $1}’`
echo “$USERNAME —- user record from all relevant tables”
mysql -uroot -e “use radius; DELETE FROM rm_cards WHERE cardnum = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM rm_users WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM rm_changesrv WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM radcheck WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM radacct WHERE username = ‘$USERNAME’;”
mysql -uroot -e “use radius; DELETE FROM rm_radacct WHERE username = ‘$USERNAME’;”
done

 

Jz

July 17, 2019

BASH: Exporting MYSQL DB to Remote Server

Filed under: Linux Related — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 10:28 AM

mysql-export-import

Disclaimer: This post is shared just for reference & learning purposes. You must modify and add more failsafe check before using it in production.

Regards
Syed Jahanzaib


Scenario:

We are using Freeradius server which uses mySQL as its backend DB. Ideally the mysql server should have replica server so that if Primary goes down dueto any fault, the secondary replica should come in action.

For high availability purposes we we want to have a standby server. Mysql Master-Slave or Master-Master replication is ideal for real time replication. We successfully implemented this model at few sites, but yes replication requires constant monitoring, and at one place the secondary replica server backfired & caused data loss.

For one particular Remote Site we wanted to avoid the complications of REPLICATION. What we wanted is a standby server, and the DB from primary should be exported to secondary replica server daily in morning and emails for the actions taken by the script should be emailed to us.

We made custom script that is running successfully from quite some time.

The BASH script performs following function …

  • Checks secondary server PING response
  • Check secondary server SSH access
  • Checks primary server MYSQL DB access
  • Checks secondary server MYSQL DB access
  • Check if exported DB is of valid size, (I set it to min 10 KB, yes you may want to adjust it according to your setup)
  • If all OK, then export primary server DB, and import it to secondary server

Script Requirements:

https://aacable.wordpress.com/2011/11/25/howto-login-on-remote-mikrotik-linux-without-password-to-execute-commands/


BASH Script Code:

  • touch /temp/update_radius_from_10.0.0.1__TO__10.0.0.2.sh
  • chmod +x /temp/update_radius_from_10.0.0.1__TO__10.0.0.2.sh
  • nano /temp/update_radius_from_10.0.0.1__TO__110.0.0.2.sh
#!/bin/bash
clear
#set -x
# Version 1.0 / 10-July-2019
# Syed Jahanzaib / Web: https://aacable.wordpress.com / Email: aacable@hotmail.com
# This script exports mysqldb and restores it to second remote server
# Requires passwordless login on remote server using SSH keys
# Settings various VARIABLES for the script
# adding dns for resolving
echo "nameserver 8.8.8.8" > /etc/resolv.conf
#SET DATE TIME
set $(date)
time=`date |awk '{print $4}'`
YESTERDAY=`date --date='yesterday' +%Y-%m-%d`
TODAY=`date +"%d-%b-%Y__%T"`
SCRIPTST=`date +"%d-%b-%Y__%T"`
HOSTNAME=`hostname | sed 's/ //g'`
IP1=10.0.0.1
IP2=10.0.0.2
IP2ROLE="RADIUS"
IP2_SSH_PORT=22
SQL_DIR="sql_replica"
#MYSQL DETAILS
SQLUSER="root"
SQLPASS="TYPE.YOUR.SQL.ROOT.PASS"
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLUSER --skip-column-names -s -e"
DB="radius"
FILE="/$SQL_DIR/$HOSTNAME.$TODAY.IP.$IP1.sql"
GMAILID="YOUR_SENDER_GMAILID@gmail.com"
GMAILPASS="GMAIL_PASS"
ADMINMAIL1="aacableATATAThotmail.com"
COMPANY="ZAIB"
RESULT="/tmp/$IP2.$IP2ROLE.txt"
CLIENTS_FILE="/usr/local/etc/raddb/clients.conf"
PING_ATTEMPTS="2"
PING_RESULT="/tmp/$IP2.$IP2ROLE.ping.result.txt"
IP2_SSH_CHK="/tmp/$IP2.ssh.chk.txt"
touch $RESULT
touch $PING_RESULT
> $RESULT
> $PING_RESULT
rm -f /$SQL_DIR/*.sql
# Test PING to device
count=$(ping -c $PING_ATTEMPTS $IP2 | awk -F, '/received/{print $2*1}')
if [ $count -eq 0 ]; then
echo "- $COMPANY ALERT: $IP2 - $IP2ROLE is not responding to PING Attempts, cannot continue without it , Please check !"
echo "- $COMPANY ALERT: $IP2 - $IP2ROLE is not responding to PING Attempts, cannot continue without it , Please check !" > $PING_RESULT
sendemail -t $email -u "ALERT: $IP2 $IP2ROLE NOT RESPONDING!" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$PING_RESULT -o message-content-type=text
exit 1
fi
echo "- Script start time: $SCRIPTST

This report contains DB export results.

- Source Server : $HOSTNAME / $IP1
- Destination Server : $IP2

- PING Result to $IP2 : OK"

echo "- Script start time: $SCRIPTST

This report contains DB export results.

- Source Server : $HOSTNAME / $IP1
- Destination Server : $IP2
- PING Result to $IP2 : OK" >> $RESULT

#Cehck if SSH is accessible
scp -q -P $IP2_SSH_PORT root@$IP2:/etc/lsb-release $IP2_SSH_CHK
# Verify if file is downloaded from remote server via ssh
if [ ! -f $IP2_SSH_CHK ]; then
echo -e "- $COMPANY ALERT: $IP2 - $IP2ROLE is not responding to passwordless SSH ACCESS, cannot continue without it , Please check !"
exit 1
fi
echo -e "- SSH Access to $IP2 : OK"
echo -e "- SSH Access to $IP2 : OK" >> $RESULT

# Check if $DB (in this case radius )is accessible or not, if NOT, then exit the script
RESULT_DB_CHK=`$CMD "SHOW DATABASES LIKE '$DB'"`
if [ "$RESULT_DB_CHK" != "$DB" ]; then
echo "- ALERT: $IP1 - DB $DB not accessible !!!"
echo "- ALERT: $IP1 - DB $DB not accessible !!!" >> $RESULT
sendemail -t $email -u "- ALERT: $IP1 - DB $DB not accessible" -o tls=yes -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$RESULT -o message-content-type=text
exit 1
fi

echo "- $DB - Database accessed on $IP1 : OK" >> $RESULT

#############################################
######## START the BACKUP PROCESS ... #######
#############################################
# Checking if $SQL_DIR folder is previously present or not . . .
{
if [ ! -d "/$SQL_DIR" ]; then
echo -e "- ALERT: /$SQL_DIR folder not found, Creating it MYSQL EXPORT/DUMP backup should be placed there . . ."
mkdir /$SQL_DIR
else
echo -e "- INFO: $SQL_DIR folder is already present , so no need to create it, Proceeding further . . ."
fi
}

mysqldump -u$SQLUSER -p$SQLPASS --single-transaction=TRUE --ignore-table={radius.radacct} $DB > $FILE
# CHECK FILE SIZE AND COMPARE, IF ITS LESS , THEN ALERT
SIZE=`ls -lh $FILE | awk '{print $5}'`
SIZEB=`ls -l $FILE | awk '{print $5}'`
if [ $SIZEB -lt 1 ]
then
echo "- ALERT: DB export failed on $IP1 - Size = $SIZE OR $SIZEB Bytes"
echo "- ALERT: DB export failed on $IP1 - Size = $SIZE OR $SIZEB Bytes" >> $RESULT
sendemail -t $email -u "ALERT: DB export failed on $IP1 - Size = $SIZE OR $SIZEB Bytes" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$RESULT -o message-content-type=text
exit 1
fi
#ssh -p $IP2_SSH_PORT root@$IP2 mkdir /$SQL_DIR
#scp -P $IP2_SSH_PORT $FILE_FINAL root@$IP2:/$SQL_DIR
#ssh -p $IP2_SSH_PORT root@$IP2 ls -lh /$SQL_DIR
# Import file in secondary radius
#ssh -p $IP2_SSH_PORT root@$IP2 "mysql -u$SQLUSER -p$SQLPASS $DB < $FILE
#mysql -h $IP2 -u$SQLUSER -p$SQLPASS $DB < $FILE
ssh -p $IP2_SSH_PORT root@$IP2 mysql -u$SQLUSER -p$SQLPASS $DB  output
#scp -P $IP2_SSH_PORT $CLIENTS_FILE root@$IP2:/usr/local/etc/raddb/
ssh -p $IP2_SSH_PORT root@$IP2 'service freeradius restart'
SCRIPTET=`date +"%d-%b-%Y___%T"`

echo "- FILE NAME : $FILE
- FILE SIZE : $SIZE

- DONE : Backup from $IP1 to $IP2 have been Exported OK

- Script End Time: $SCRIPTET

Regard's
Syed Jahanzaib"

echo "- FILE NAME : $FILE
- FILE SIZE : $SIZE

- DONE : Backup from $IP1 to $IP2 have been Exported OK

- Script End Time: $SCRIPTET

Regard's
Syed Jahanzaib" >> $RESULT

sendemail -t $email -u "$TODAY $HOSTNAME DB Exported from $IP1 to $IP2 Report OK" -o tls=yes -s smtp.gmail.com:587 -t $ADMINMAIL1 -xu $GMAILID -xp $GMAILPASS -f $GMAILID -o message-file=$RESULT -o message-content-type=text

#cat $RESULT
rm $IP2_SSH_CHK
rm $RESULT
rm $PING_RESULT
rm $FILE

Email Report Sample:

replica export done.PNG


Cron schedule to run the script Daily at 7am

# To run the script daily at 7 AM in morning
00 07 * * * /temp/update_radius_from_10.0.0.1__TO__10.0.0.2.sh

# To run the script every 6th hours 30 mnts
30 */6 * * * /temp/update_radius_from_10.0.0.1__TO__10.0.0.2.sh

Regard's
Syed Jahanzaib

July 2, 2019

DENIED Notes users are still able to access mails through IBM Notes Traveler

Filed under: IBM Related — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 4:30 PM

This post is about a case study regarding “Denied access Notes users are still able to access mails through IBM Notes Traveler“.

We are using IBM lotus Domino server as per following

  • – Lotus Domino – Primary Mail Server [For Lotus Notes/Webmail]
  • – Lotus Domino – Traveler Role [For Mobile Devices like Android/iPhone]

 

Case Study:

Today, It was brought to our knowledge that one of company’s employee resigned on 28th June 2019) have sent emails to HR Dept on ndex day. while his account was under DENY group, but still he was able to sent emails. We tried settings from IBm document referenced “Denied access Notes users are still able to access mails through IBM Notes Traveler” from https://www-01.ibm.com/support/docview.wss?uid=swg21634205 but still no luck. Traveler users who were under NO ACCESS GROUP under Primary LOTUS server were still able to sync emails.

Our Blocking Practice:
As per our practice , when any user resigned from the company, we add him under DENY GROUP under Lotus Domino Server for few days, which blocks the Notes/Webmail Access access for that particular user. Later if user withdraw resignation we just remove his name from this list, Else we remove his profiles and save his email in Archive for ever.

Findings:
If the user have IBM Verse installed on there mobile device, he can still access the email because his access is blocked primarily on Lotus Email Server, but since mobile devices does not communicate with the Primary server directly instead they access it via separate TRAVELER server (by proxying through LOTUS TRAVELER server), and communication between Primary Server & Lotus traveler server is being done through server to server basis thus they could access the emails.

Solution:

no access group.jpg
Adding the NO ACCESS list in the traveler server document under security DID THE TRICK !

[13FC:000A-1574] 07/01/2019 12:45:02 PM XXXXX Web Server: Access Denied Exception [/traveler?action=sync&orig=sp&deviceId=Android_a41df4vf3fe46a8e3a] CN=MY USER/O=MYCOMP

This list will be updated via Primary Lotus server after every 10 minutes (using replication connection) & it will act as additional level of permissions filtering. Now if any user will be added under DENY GROUP under Lotus Mail Server, this list will be propagated to Lotus Traveler server as well which will deny the user request if his name is under DENY GROUP.

Thanks to FB group “IBM Lotus Domino Administrators” for pointing in the right direction.


Some addition Tip:

to flush DB cache

sh nlcache reset

https://www.novell.com/coolsolutions/tip/17050.html

Regard’s
Syed Jahanzaib

 

May 12, 2019

Facilitate CDN traffic with Mikrotik

Filed under: Mikrotik Related — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 12:49 PM

ncdn_-_cdn.png

Control / Facilitate CDN traffic with

~ Mikrotik Router ~

First some DRY theory !

CDNs replicate content in multiple places. There’s a better chance of content being closer to the user, with fewer hops, and content will run over a more friendly network. The general idea of a CDN is to deliver content as fast as possible to the user without compromising the user’s experience. Usually, a CDN have global location servers, called Point of Presence. These PoPs store data as cache. When a user requests for a website, the nearest PoP will handle the request using stored cache.

The BIG players such as Google in order to enhance user experience have tried to get as close to the user as possible by direct peering with the regional service providers and provide contents using CDN (Content delivery network) providers. Google is having its own CDN network branded as a service called Google Global Cache (GGC)

Nowadays all the major ISPs have CDN facility , which tremendously helps them to reduce burden on there internet feed. Without CDN, cost of real internet bandwidth will be a heavy burden for any OP. With CDN user will get better video streaming experience.

I know few ISP’s here in Karachi (& one particularly originated from Gulshan Area) which totally relies on CDN (more than 50-60% of there internet data is routing via CDN) , I have used one of them, there real internet speed is pathetic but if you browse YT/FB they works excellent.


Scenario:

Our upstream ISP have CDN server installed in there data center & traffic going to CDN have no limit. But we want to control the traffic as following

1 Mb package Users break up for bandwidth controlling …

  • 1mb internet bandwidth
  • 1mb CDN bandwidth

So if a user is surfing the internet he will get full 1mb internet speed, & if he uses the traffic going to YOUTUBE CDN ,  He will get another 1 mb (additional).
Virtually he will get 2mb in total.

Using Mikrotik, we can achieve this task by using Firewall Mangle & Queues Tree. Same can be done with Mangle & PCQ base simple queues too. It’s a debatable topic on what to use, & depends on the selection, mangle marking method would also be changed.

Every network is different so one configuration cannot fit all. Number of users & traffic volume plays vital role in selection of marking / queue type to use.

Choose the marking/queue type wisely to save your Mikrotik CPU from becoming Mr. SPIKY 🙂 YKWIM 😀

Disclaimer: This is just an example for sharing purposes ONLY & yes there are many other methods and tuning techniques you can adopt to make this process much more efficient.


Script !

#===================================================
# CDN PACKET MARKING SCRIPT using Mangle/Queue.Tree
# By Syed.Jahanzaib
# Email: aacableAThotmailDOTcom
# https://aacableDOTwordpressDOTcom
# March 2019
#===================================================
# Address list name which is created dynamically by radius or you can go with manual method too
# This is important ... it can be done by varieties of ways, select one that matches your network design
#1Mb
#2Mb

#Create Address List which will contain CDN server's IP addresses
/ip firewall address-list
add address=1.2.3.4/24 list=cdn_list
add address=5.6.7.8/32 list=cdn_list

# Copy paste following rules & make sure to move these MANGLE rules to TOP position,
# so that they can be applied before any other rule, (for cdn)

/ip firewall mangle
add action=mark-packet chain=postrouting dst-address-list=cdn_list new-packet-mark=cdn_1mb_up passthrough=no src-address-list=1Mb
add action=mark-packet chain=postrouting dst-address-list=1Mb new-packet-mark=cdn_1mb_down passthrough=no src-address-list=cdn_list

add action=mark-packet chain=postrouting dst-address-list=cdn_list new-packet-mark=cdn_2mb_up passthrough=no src-address-list=2Mb
add action=mark-packet chain=postrouting dst-address-list=2Mb new-packet-mark=cdn_2mb_down passthrough=no src-address-list=cdn_list

# Define Queue Type & limitation that we want to provide to each package
/queue type
add kind=pcq name=1mb-cdn-download pcq-classifier=dst-address pcq-dst-address6-mask=64 pcq-rate=1024k pcq-src-address6-mask=64 pcq-total-limit=1024KiB
add kind=pcq name=1mb-cdn-upload pcq-classifier=src-address pcq-dst-address6-mask=64 pcq-rate=1024k pcq-src-address6-mask=64 pcq-total-limit=1024KiB

add kind=pcq name=2mb-cdn-download pcq-classifier=dst-address pcq-dst-address6-mask=64 pcq-rate=2048k pcq-src-address6-mask=64 pcq-total-limit=2048KiB
add kind=pcq name=2mb-cdn-upload pcq-classifier=src-address pcq-dst-address6-mask=64 pcq-rate=2048k pcq-src-address6-mask=64 pcq-total-limit=2048KiB

# Add Queue/Speed Limitation using above Queue Types to firewall mangled/marked packets

/queue tree
add name="CDN - 1mb - upload" packet-mark=cdn_1mb_up parent=global priority=1 queue=1mb-cdn-upload
add name="CDN - 1mb - download" packet-mark=cdn_1mb_down parent=global priority=1 queue=1mb-cdn-download

add name="CDN - 2mb - upload" packet-mark=cdn_2mb_up parent=global priority=1 queue=2mb-cdn-upload
add name="CDN - 2mb - download" packet-mark=cdn_2mb_down parent=global priority=1 queue=2mb-cdn-download

# Script Ends Here.

1mb users CDN usage Graph.

cdn

As shown in above example image, 1mb users are using 227 mb of CDN (YT) bandwidth,  (it was off time with lesser number of users, in peak traffic reaches in Gb’s) & real internet bandwidth is free OR available for other tasks/users,  thus providing relief to the real internet bandwidth pipe.


Regard’s
Syed Jahanzaib

durood

April 22, 2019

MySql Database Recovery from Raw Files

Filed under: Linux Related, Radius Manager — Tags: , , , , , — Syed Jahanzaib / Pinochio~:) @ 2:31 PM

mysql recovery.PNG


Disclaimer: This worked under particular case. It may or may not work for everyone.

Scenario:

OS: Ubuntu 12.4 Servedit Edition / x86

MYSQL: Ver 14.14 Distrib 5.5.54, for debian-linux-gnu (i686) using readline 6.2

The OP was running radius for AAA. The disk got faulty for some unknown reasons and were unable to boot from it. There was no database backup [Real example of bad practices] So restoration from mysqldump to new system was not an option there !

Requirements:

We need to restore the Database using mysql raw files. Luckily the faulty disk was able to got attached to other system & we were able to copy the core /var/lib/mysql/ folders (along with all sub folders in it)


Quick & Dirty Restoration Step !

Requires some good level of Linux / DB knowledge]

  • Setup a test SANDBOX, Install same level of OS along with MYSQL on new system/disk. Create databases / tables as required. Verify all is working by logging to mysql
  • Stop the MYSQL service.
  • Copy the folder /var/lib/mysql [copied from faulty disk] to this new box under /var/lib/mysql/  
  • Set the permission on newly copied files/folders
    chown mysql -R /var/lib/mysql/

After this point Try to start the MYSQL service , IF it starts successfully & you can see your DATA , then skip below steps , ELSE continue through below steps …

  • Edit the /etc/mysql/my.cnf & add following line under [mysqld] section
    innodb_force_recovery = 6
  • Start MYSQL service & the service will start in Safe Mode with limited working support. Verify if you can able to login to MYSQL service by
    mysql -uroot -pPASS
  • If above step works, Export the Database backup using mysqldump cmd e.g:
    mysqldump -uroot -pSQLPASS   radius  >  radius_db_dump_.sql
  • Once done, Open the file in nano or any other text editor, & verify if it contains the required data.

Now copy the radius_db_dump_.sql to safe location & you know what to do next 🙂

  • Import this mysqldump file to your working radius system !

TIPS:

best-practice2

Make sure you have multistage backup strategies in place for any mission critical server.

Example for mysql Database, You can do following

  • If your server is VM, then VEEAM B&R will be your best friend & guardian, go for it
  • 1st Stage Backup: [Highly recommended for live replication]
    ideally, you should have at least 2 Replica servers & configure either Master-Master or Master-Slave Replication
  • 2nd Stage backup:
    Create bash scripts to export DB backup in local folder on a daily basis, (or hourly basis if required]
  • 3rd Stage backup:
    Attach external USB disk to the server, and in your backup script, add this usb as additional backup repository
  • 4th Stage backup:
    Configure DROPBOX and add it as additional backup repository
  • 5th Stage backup:
    The admin should manually copy the backup folders to his desktop so that if all other backups gets failed , this should come in handy.

Regard’s
Syed Jahanzaib

 

 

 

April 11, 2019

Vcenter 6.5: Cannot complete operation due to concurrent modification by another operation

Filed under: VMware Related — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 9:39 AM

Case#1

We have few ESXI machines managed by Vcenter (all have same 6.5 version). Today when we tried to upgrade compatibility on one vm guest using Vcenter, it gave following error.

Cannot complete operation due to concurrent modification by another operation

After some troubleshooting, it came to my knowledge that there was a pending snapshot made by Veeam B&R software, that was causing the issue. After removal of this snapshot, the compatibility upgraded worked fine, & later we moved this VM from one esxi to another dueto resource strains.

 

Vcenter error and snapshot removal solved it

Case#2

In one another encounter, whenever we tried to edit the guest VM setting, it gave error “Invalid configuration for device ‘1’.” , for this particular case we simply remove the affected guest VM from the inventory & re-added it and the problem got solve.

April 5, 2019

Mikrotik with Freeradius/mySQL # Part-22 – Create Dynamic Address List using Mikrotik-Address-List Attribute

Filed under: freeradius, Mikrotik Related — Tags: — Syed Jahanzaib / Pinochio~:) @ 12:28 PM

fre



Disclaimer! This is important!

Every Network is different , so one solution cannot be applied to all. Therefore try to understand logic & create your own solution as per your network scenario. Just dont follow copy paste.

If anybody here thinks I am an expert on this stuff, I am NOT certified in anything Mikrotik/Cisco/Linux or Windows. However I have worked with some core networks and I read , research & try stuff all of the time. So I am not speaking/posting about stuff I am formerly trained in, I pretty much go with experience and what I have learned on my own. And , If I don’t know something then I read & learn all about it.

So , please don’t hold me/my-postings to be always 100 percent correct. I make mistakes just like everybody else. However – I do my best, learn from my mistakes and always try to help others.

Regard’s
Syed Jahanzaib~


Scenario:

  • We have FREERADIUS installed as a AAA system in Ubuntu 16.04 server
  • Mikrotik version 6.44 is acting as PPPoE NAS connected with radius for AAA

Requirement:

When any user connects with our NAS, he should be added to mikrotik dynamic address list under IP > firewall > address list, so that we can manipulate this address list for different tasks, example mark connections/packets/routing and use them in Queues / Routes section or perform different sort of filtering as required.

In this particular task we are dynamically adding user in particular address list using radius attributes, then using this address list packet marking is being made, and then in Queues we are using these marked packets for different sort of bandwidth policies, example for normal internet we will limit 1mb per user , and for CDN traffic we will add addition 2mb for YT & FB. [and vice versa for different packages accordingly]

 


Solution:

We will use Mikrotik-Address-List attribute in radgroupreply section. as shown here.

1# Adding User entry in RADCHECK table so user can authenticate …

mysql> select * from radcheck;
+----+----------+--------------------+----+-------------------+
| id | username | attribute | op | value |
+----+----------+--------------------+----+-------------------+
| 1 | zaib | Cleartext-Password | := | zaib |
+----+----------+--------------------+----+-------------------+
1 rows in set (0.01 sec)

2# Adding Radius Group Reply for 1mb Group, Example 1mb group user will get 1mb dynamic queue plus they will be added dynamically in address list name 1mb

mysql> select * from radgroupreply;
+----+-----------+-----------------------+----+--------------+
| id | groupname | attribute | op | value |
+----+-----------+-----------------------+----+--------------+
| 21 | 1mb | Mikrotik-Rate-Limit | == | 1024k/1024k |
| 22 | 1mb | Mikrotik-Address-List | := | 1mb |
+----+-----------+-----------------------+----+--------------+
2 rows in set (0.00 sec)

2# Adding username ZAIB in the Radius user group & assign him 1mb Group.

 

mysql> select * from radusergroup;
+----+----------+-----------+----------+
| id | username | groupname | priority |
+----+----------+-----------+----------+
| 5 | zaib | 1mb | 1 |
+----+----------+-----------+----------+
1 row in set (0.00 sec)

RADTEST:

Now we will test user via RADTEST cmd …


radtest zaib zaib localhost 1812 testing123

Result:

Sending Access-Request of id 130 to 127.0.0.1 port 1812
User-Name = "zaib"
User-Password = "zaib"
NAS-IP-Address = 101.11.11.254
NAS-Port = 1812
Message-Authenticator = 0x00000000000000000000000000000000
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=130, length=50

Mikrotik-Rate-Limit = "1024k/1024k"
Mikrotik-Address-List = "1mb"

Freeradius Debug Result:

Sending Access-Accept of id 156 to 127.0.0.1 port 34563
Mikrotik-Rate-Limit == "1024k/1024k"
Mikrotik-Address-List := "1mb"
Finished request 32.

Now try to connect user from your user device, & upon connection you will see new address list entry for this user IP ..

& its 1mb queues have been created as well


# Mikrotik Mangling & Queueing Section !

Now we will move towards Mikrotik related configuration for mangling & queue. in above steps we added DYNAMIC queue for test purposes, & as we will be using simple queues therefore we need to remove the dynamic queue, Do so , then we will move further …

  • Marking upload & download separately for 1mb user address list …

/ip firewall mangle
add action=mark-packet chain=forward comment="1mb users UPLOAD" new-packet-mark=1mb_users_up src-address-list=1mb passthrough=no
add action=mark-packet chain=forward comment="1mb users DOWNLOAD" dst-address-list=1mb new-packet-mark=1mb_users_down passthrough=no
  • Creating PCQ base 1mb download/upload limit variable …
/queue type
add kind=pcq name=download-1mb pcq-classifier=dst-address pcq-dst-address6-mask=64 pcq-rate=1024k pcq-src-address6-mask=64
add kind=pcq name=upload-1mb pcq-classifier=src-address pcq-dst-address6-mask=64 pcq-rate=1024k pcq-src-address6-mask=64
  • Creating PCQ base simple Queues to actual limit each user with 1mb download/upload …
/queue simple
add name="1mb user DOWN - PCQ" packet-marks=1mb_users_down queue=upload-1mb/download-1mb target=""
add name="1mb user UP - PCQ" packet-marks=1mb_users_up queue=128k-per-user/128k-per-user target=""

 

PC#1

1st- user - 1mb user test

PC#2

2nd pc 128K 1mb


TIPS:

How to remove all dynamic queues [can be used in script login section]

dynamic queue removal.PNG

/queue simple remove [find where dynamic]

Conclusion:

As we can see that address list have been created successfully, now we can manipulate it for our different tasks using marked packets for customized PCQ base queues for policy base queueing.

I will write more on it later if manage to get some spare time.


 

Regard’s
Syed Jahanzaib

 

March 29, 2019

March 25, 2019

Mikrotik with Freeradius/mySQL # Part-21 – Weird Trigger for Duplicate Users

Filed under: Mikrotik Related, Radius Manager — Tags: , — Syed Jahanzaib / Pinochio~:) @ 11:13 AM

dup user.jpg

fre



Disclaimer! This is important!

Every Network is different , so one solution cannot be applied to all. Therefore try to understand logic & create your own solution as per your network scenario. Just dont follow copy paste.

If anybody here thinks I am an expert on this stuff, I am NOT certified in anything Mikrotik/Cisco/Linux or Windows. However I have worked with some core networks and I read , research & try stuff all of the time. So I am not speaking/posting about stuff I am formerly trained in, I pretty much go with experience and what I have learned on my own. And , If I don’t know something then I read & learn all about it.

So , please don’t hold me/my-postings to be always 100 percent correct. I make mistakes just like everybody else. However – I do my best, learn from my mistakes and always try to help others.

Regard’s
Syed Jahanzaib~


Scenario:

  • We have DMASOFTLAB radius manager installed as a billing system in Ubuntu 12.04 server
  • Mikrotik version 6.4x.x is acting as Hotspot NAS and connected with radius for AAA

Requirement: [A Weird one really]

As operator demanded

“We are running Hotspot on mikrotik, & client login to hotspot using his mobile/laptop. If logged-in client leaves his primary location without logout, & move to another location, & if he try to login from another device, his request will gets DENY because of Single user limit. We increased it to 2 by using SIM-USE=2 directive in user properties,It allows second session to login, but both sessions can use the bandwidth, therefore we want that once second session is established its old first live session should get kicked. If it was single Hotspot we could have used the script on LOGIN, but there are several NAS spreaded across various location using single radius.”

if the user uses same device then we could have used

if (User-Name){
if("%{sql:UPDATE radacct set AcctStopTime=ADDDATE(AcctStartTime,INTERVAL AcctSessionTime SECOND), AcctTerminateCause='Clear-Stale Session' WHERE UserName='%{User-Name}' and CallingStationId='%{Calling-Station-Id}' and AcctStopTime is NULL}"){
}
}

but things are different in hotspot as I have observed, if devices are different then it will give us already logged-in error, if we use sim-use=2 then second device can be logged-in but old session will also be alive and both ids will suck the bandwidth at a time.

Also using idle-timeout or keep-alive timeout is the simplest way to achieve this , but for some weird reasons and to avoid long arguments dueto accent issues, I made one customized solution for the operator.


Solution:

Login to mysql with root

mysql -uroot -pXXXX

and switch to radius database

use radius;

Now create new table that will hold duplicate users record

MYSQL Table to hold duplicate users list


--
-- Table structure for table `rm_dupusers`
--

DROP TABLE IF EXISTS `rm_dupusers`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `rm_dupusers` (
`dupid` int(9) NOT NULL AUTO_INCREMENT,
`datetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`username` varchar(64) NOT NULL,
`ip` varchar(16) NOT NULL,
`nas` varchar(16) NOT NULL,
`comments` varchar(64) DEFAULT NULL,
KEY `dupid` (`dupid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `rm_dupusers`
--

MYSQL TRIGGER to check duplicate users sessions

Now we will create a new Trigger that will be executed when any record is inserted in radacct, it will check for existing duplicate session of user and if it found , it will add its entry in the mysql table of rm_dupusers

drop trigger chk_dup_user;
DELIMITER ;;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `chk_dup_user` BEFORE INSERT ON `radacct` FOR EACH ROW BEGIN
SET @dupuserchk = (SELECT count(*) from radacct where username=New.username and acctstoptime is NULL);
IF (@dupuserchk = 1) THEN
SET @dupusername = (SELECT username from radacct where username=New.username and acctstoptime is NULL);
SET @dupuserip = (SELECT framedipaddress from radacct where username=New.username and acctstoptime is NULL);
SET @dupusernas = (SELECT nasipaddress from radacct where username=New.username and acctstoptime is NULL);
INSERT into rm_dupusers (dupid,username,ip,nas,comments) values ('',@dupusername,@dupuserip,@dupusernas,'Duplicate User');
END IF;
END */;;
DELIMITER ;

Mysql Part is Done.

Now we will create a BASH script that will scheduled to run every minute.

BASH script !

Create bash script in desired folder, in this example I am using /temp folder as default

mkdir /temp
touch /temp/kickdupuser.sh
chmod +x /temp/kickdupuser.sh
nano /temp/kickdupuser.sh

& paste following, make sure to modify credentials

#!/bin/bash
#set -x
# Following script is made specifically for Dmasoftlab radius manager 4.1.x
# When any new user will login, it will simply check if exists session of same user found, it will kick previous session
# it requires custom trigger on radacct table, this script will be schedule to run every minute
# Created: 25-MARCH-2019
# Tested on Ubuntu OS Only
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
#################
# CHANGE these
HOSTNAME=`hostname`
SQLID="root"
SQLPASS="XXXXXX"
NAS_COA_PORT="1700"
DB="radius"
SRV="mysql"
DUP_TABLE="rm_dupusers"
INT="1"
RADCLIENT="/usr/local/bin/radclient"
#################
#DATE TIME FUNCTIONS
currenttime=$(date +%H:%M:%S)
# Add Script start execution entry in the /var/log/syslog to see if the script got executed or not
logger "Duplicate User poller script Started @ $currenttime by the CRON scheduler ... Powered by SYED.JAHANZAIB"
echo "- Script Start Time - $currenttime"
echo "- Checking Duplicate Users in $DUP_TABLE table ..."
export MYSQL_PWD=$SQLPASS
CMD="mysql -u$SQLID --skip-column-names -s -e"
#Table which contain main users information
TMPUSRINFO=/tmp/userpass.txt
TEMP="/temp"

# Checking if /temp folder is previously present or not . . .
{
if [ ! -d "$TEMP" ]; then
echo
echo "- INFO: $TEMP folder not found, Creating it now to store logs ..."
mkdir $TEMP
else
echo -e "- INFO: $TEMP folder is already present to store logs."
echo
fi
}

DUP_LIST_FILE=$TEMP/duplicate_users_list.txt
SYSLOG="/var/log/syslog"
> $TMPUSRINFO

# KANNEL DETAILS
KHOST="127.0.0.1:13013"
KID="kannel"
KPASS="KANNEL_PASSWORD"

IPADD=`ip route get 1 | awk '{print $NF;exit}'`
SRVSTATUS=`service $SRV status |grep running |wc -l`
if [ "$SRVSTATUS" -ne 1 ];
#if [ -z "$SRVSTATUS" ];
then
echo "- ALERT: $HOSTNAME - $IPADD - $SRV NOT RESPONDING CHECK - $DATE $DT .Exiting ..."
echo "- ALERT: $HOSTNAME - $IPADD - $SRV NOT RESPONDING CHECK - $DATE $DT .Exiting ..." >> $SYSLOG
echo "- ALERT:

- $HOSTNAME
- $IPADD
- $SRV not responding ***
- $currenttime

Exiting ..."
exit 1
else
echo "- INFO: $SRV service is accessible. Proceeding further ... OK"
fi

# Check if table exists
if [ $($CMD \
"select count(*) from information_schema.tables where \
table_schema='$DB' and table_name='$DUP_TABLE';") -eq 1 ]; then
echo "- INFO: $DUP_TABLE Table exists ..."
else
echo "- WARNING: $DUP_TABLE Table does not exists ..."
fi
########
########
# Enable following line so that it will update all users simultanous-use to '2' so that two sessions can be established
# $CMD "use $DB; UPDATE  radius.radcheck SET value = '2' where Attribute = 'Simultaneous-Use';
#######
#######
# pull user record
$CMD "use $DB; select username,ip,nas from $DUP_TABLE WHERE datetime >= NOW() - INTERVAL $INT MINUTE;" >> $TMPUSRINFO
if [ ! -s $TMPUSRINFO ]
then
endtime=$(date +%H:%M:%S)

echo "
- INFO: No Duplicate User found in DMA RADIUS MANAGER TABLE '$DUP_TABLE' , Sending EXIT signals ...

- Script Ends Here...
- EXITING peacefully...
- Script End Time - $endtime
"
exit 1
fi

# Apply Count Loop Formula while deleting first line which have junk text
num=0
cat $TMPUSRINFO | while read users
do
num=$[$num+1]
username=`echo $users | awk '{print $1}'`
USER_IP=`echo $users | awk '{print $2}'`
ACCTSESID=`$CMD "use $DB; select acctsessionid from radacct where framedipaddress ='$USER_IP' AND acctstoptime is NULL;"`
NAS_IP=`echo $users | awk '{print $3}'`
NAS_SECRET=`$CMD "use $DB; select secret from nas where nasname = '$NAS_IP' ;"`

# Print Info on screen
echo "Duplicate User Found: USER: $username , IP: $USER_IP, ID: $ACCTSESID, $NAS: $NAS+IP @ $currenttime ... KICKING him now ..."
echo "Duplicate User Found: USER: $username , IP: $USER_IP, ID: $ACCTSESID, $NAS: $NAS+IP @ $currenttime ... KICKING him now ..." >> $DUP_LIST_FILE
#echo User-Name=$USERNAME,Acct-Session-Id=$ACCTSESID,Framed-IP-Address=$USER_IP,Mikrotik-Rate-Limit=\"$DN_BWPKG\" | $RADCLIENT -q -c 1 $NAS_IP:$NAS_COA_PORT coa $NAS_SECRET
#for hotspot, enable following line
echo Framed-IP-Address=$USER_IP | radclient -x -c 1 $NAS_IP:$NAS_COA_PORT disconnect $NAS_SECRET
done
# once done, we should delete the tmp files to clear the garbage
rm $TMPUSRINFO

CRON scheduler to run the above script every minute. Edit crontab by

crontab -e

& add following entry

* * * * * /temp/kickdupuser.sh >/dev/null 2>&1

Testing …

Using same credentials, Login to first device, and then on second ,

& run this script,

root@radius:/temp# /temp/kickdupuser.sh
- Script Start Time - 10:52:03
- Checking Duplicate Users in rm_dupusers table ...
- INFO: /temp folder is already present to store logs.
- INFO: mysql service is accessible. Proceeding further ... OK
- INFO: rm_dupusers Table exists ...
Duplicate User Found: USER: test , IP: 172.16.0.253, ID: 81d00057, : +IP @ 10:52:03 ... KICKING him now ...
Sending Disconnect-Request of id 58 to 10.0.0.1 port 1700
Framed-IP-Address = 172.16.0.253
rad_recv: Disconnect-ACK packet from host 10.0.0.1 port 1700, id=58, length=32
NAS-Identifier = "ZAIB_CCR_GW"
root@radius:/temp#

older session will be removed

radclient dc the first user.PNG


Weirdo …. but its fun to learn !

TIPS:

Command to view duplicate users session in freeradius using CLI


mysql -uroot -pMYPASS --skip-column-names -e 'use radius; SELECT username FROM radacct WHERE acctstoptime IS NULL;' > 1.txt && sort 1.txt | uniq -cd

Regard’s
Syed Jahanzaib

Older Posts »

%d bloggers like this: