Syed Jahanzaib Personal Blog to Share Knowledge !

July 12, 2018

FREERADIUS WITH MIKROTIK – Part #16 – Loosy workaround to disconnect missing users from the NAS

Filed under: freeradius — Tags: , , , — Syed Jahanzaib / Pinochio~:) @ 9:02 AM

search in croud

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s Click here to read more on FR tutorials …


Disclaimer! This is important!

Every Network is different , so one solution cannot be applied to all. Therefore try to understand the 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 single NAS (Mikrotik) as pppoe server along with Freeradius as AAA server.
In NAS we have configured INTERIM UIPDATES set to 5 minutes therefore it sends accounting packets to the freeradius server after every 5 minutes. In Freeradius server web have a BASH script that closes the online sessions if the FR doesnt receive  accounting packets from the NAS for more then 10 minutes (to clear false sessions by considering that NAS is powered off).

We also have some users created locally in the NAS.


Problem:

Sometimes dueto communication disruption , Freeradius close the user session in radacct table considering the NAS is not reachable, BUT the user is still active on the NAS. When the communication restores & NAS sends the existing users accounting packets to the freeradius, they get discarded because the NAS connected user Account-Session-ID does not matches with the radacct table session id.

Freeradius will create new session (with acctstoptime is NULL value) only when

  • If new connection is made by user end , OR
  • If NAS connected user session ID is matched with the radacct Account Session ID

Workaround:

This is not a proper solution because it is limited to single NAS only, for multi NAS you should search freeradius mailing list for better approach.

Following is bash script which performs following action …

  1. Fetch NAS online active users in PPP/Active Connections, using password less ssh login from the Linux to Mikrotik,
  2. Fetch Freeradius online active users from RADACCT table (where acctstoptime value is NULL),
  3. Display difference between NAS and Freeradius Online users,
  4. If differentiated user is NAS local user, then donot take any action just move on,
  5. ELSE consider this user as RADIUS user which is online in NAS but offline in Radius RADACCT table, therefore KICK it , so that it can re-establish the new session and get re-inserted in radacct table properly.

You can modify it as per you local requirements.


Requirements:

  • Mikrotik as NAS with SSH enabled & RSA key imported so that ssh from Linux to mikrotik must work without password, explained  here , Make sure its working
  • Freeradius Server
  • Arithmetic function is performed by BC command, make sure you have it installed

the Script!

Schedule the script to run every 5 or above minutes, or as per local requirement,

#!/bin/bash
# BASH script to fetch online users list from Mikrotik NAS & compare it with local FR online users
# IF difference found, then kick the missed users from NAS, but if user is NAS local , then skip it
# This way the missing users will re-establishes there session and will be inserted properly in radacct table AGAIN ,
# This is just a workaround only, ITs not a proper solution, It also assumes you have single NAS only
# Created on : 11-JUL-2018
# Syed Jahanzaib / aacable at hotmnail dot com / https://aacable dot wordpress dot com

#set -x
# Setting various variables ...

# MYSQL related data
SQLID="root"
SQLPASS="SQLROOTPASSWORD"
DB="radius"
TBL_LOG="log"
export MYSQL_PWD=$SQLPASS
FOOTER="Script Ends Here. Thank you
Syed Jahanzaib / aacable at hotmail dot com"
# MIKROTIK related Data
MT_USER="admin"
MT_IP="10.10.0.1"
MT_PORT="10022"
MT_SECRET="RADIUS_INCOMING_SECRET"
MT_COA_PORT="3799"

# SSH commands for Mikrotik & Freeradius
MT_SSH_CMD="ssh $MT_USER@$MT_IP -p $MT_PORT"
FR_SSH_CMD="mysql -uroot --skip-column-names -s -e"

# Temporary holder for various data
MT_ACTIVE_USERS_LIST="/tmp/mt_active_users.txt"
MT_ACTIVE_USERS_NAME_ONLY_LIST="/tmp/mt_active_users_name_only_list.txt"
FR_ACTIVE_USERS_LIST="/tmp/fr_active_users_list.txt"
FR_MISSING_USERS_NAME_LIST="/tmp/fr_active_users_missing_list.txt"
> $MT_ACTIVE_USERS_LIST
> $MT_ACTIVE_USERS_NAME_ONLY_LIST
> $FR_ACTIVE_USERS_LIST
> $FR_MISSING_USERS_NAME_LIST

# Execute SSH commands & store data in temporary holders
echo "1- Getting list of Mikrotik Online users via ssh ..."
$MT_SSH_CMD "/ppp active print terse " | sed '/^\s*$/d' > $MT_ACTIVE_USERS_LIST
echo "2- Getting list of Freeradius Online users from 'RADACCT' table ..."
$FR_SSH_CMD "use radius; select username from radacct WHERE acctstoptime IS NULL;" > $FR_ACTIVE_USERS_LIST

# Run loop forumla to run CMD for single or multi usernames
echo "3- Running loop formula to fetch usernames only from the mikrotik PPP online users list ..."
num=0
cat $MT_ACTIVE_USERS_LIST | while read users
do
num=$[$num+1]
USERNAME=`echo $users |sed -n -e 's/^.*name=//p' | awk '{print $1}'`
echo "$USERNAME" >> $MT_ACTIVE_USERS_NAME_ONLY_LIST
done

# calculate and display Users from Mikrotik Active PPP list vs Freeradius Local Actvive Users List
MT_USERS_COUNT=`cat $MT_ACTIVE_USERS_LIST | wc -l`
FR_USERS_COUNT=`cat $FR_ACTIVE_USERS_LIST | wc -l`
USER_DIFFERENCE=`echo "($MT_USERS_COUNT)-($FR_USERS_COUNT)" |bc`
echo "
Result:
echo MIKROTIK ACTIVE USERS = $MT_USERS_COUNT
echo RADIUS ACTIVE USERS = $FR_USERS_COUNT
echo Freeradius Missing Users = $USER_DIFFERENCE
"
#comm -23 <(sort < $MT_ACTIVE_USERS_NAME_ONLY_LIST) <(sort < $FR_ACTIVE_USERS_LIST)

# Make separate list for users that are foung missing in Freeradius online users list
comm -23 <(sort < $MT_ACTIVE_USERS_NAME_ONLY_LIST) <(sort  $FR_MISSING_USERS_NAME_LIST

# Check if missing user is NAS local or FR user
echo "4- Running loop formula to check if missing users from FR are NAS local or radius users ...
"
cat $FR_MISSING_USERS_NAME_LIST | while read users
do
num=$[$num+1]
USERNAME=`echo $users | awk '{print $1}'`
REMOTE_OR_LOCAL=`cat $MT_ACTIVE_USERS_LIST |grep $USERNAME |awk '{print $2}'`
if [ "$REMOTE_OR_LOCAL" != "R" ]; then
echo "$USERNAME = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ..."
else
# IF user is a FR user, then consider his MISSED user & kick him so that he will reconnect & proper entries will be made in radius radacct table
echo "$USERNAME = This user is ON-LINE in NAS, but OFF-LINE in FR radacct table, kicking it so it will reconnect & session will be recreated properly **********"
#$MT_SSH_CMD "/ppp active remove [find name=$USERNAME]"
# KICK user via RADCLIENT / zaib
echo user-name=$USERNAME | radclient -x $MT_IP:$MT_COA_PORT disconnect $MT_SECRET
$FR_SSH_CMD "use $DB; INSERT into $TBL_LOG (data, msg) VALUES ('$USERNAME', '$USERNAME - Kicked from NAS dueto missing session in FR');"
fi
done

echo "$FOOTER"

OUTPUT:

root@zaibradius:/temp# ./test.sh
1- Getting list of Mikrotik Online users via ssh ...
2- Getting list of Freeradius Online users from 'RADACCT' table ...
3- Running loop formula to fetch usernames only from the mikrotik PPP online users list ...

Result:
echo MIKROTIK ACTIVE USERS = 959
echo RADIUS ACTIVE USERS = 945
echo Freeradius Missing Users = 14

4- Running loop formula to check if missing users from FR are NAS local or radius users ...

USER1 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER2 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER3 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER4 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER5 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER6 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER7 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER8 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER9 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER10 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER11 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER12 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER13 = This user is ON-LINE in NAS but OFF-LINE in FR radacct table, BUT its NAS local user, so skipping it ...
USER14 = This user is ON-LINE in NAS, but OFF-LINE in FR radacct table, kicking it so it will reconnect & session will be recreated properly **********

Sending Disconnect-Request of id 95 to 10.10.0.1 port 3799
User-Name = "USER14"
rad_recv: Disconnect-ACK packet from host 10.10.0.1 port 3799, id=95, length=36
NAS-Identifier = "MikroTik"
NAS-IP-Address = 10.10.0.1

Script Ends Here. Thank you
Syed Jahanzaib / aacable at hotmail dot com

FR stale session script result.JPG

.

Kick action entry will be logged in LOG table,

LOG ENTRY.JPG


Salam Alykum,

November 1, 2017

Mikrotik with Freeradius/mySQL – Dealing with STALE sessions in FR – Part 5

Filed under: freeradius — Tags: , , — Syed Jahanzaib / Pinochio~:) @ 1:26 PM

~ Dealing with STALE session in Freeradius 2.x ~
! From the CORE of FREERADIUS !
By
Syed jahanzaib

FREERADIUS WITH MIKROTIK – Part #1 – General Tip’s Click here to read more on FR tutorials …


Scenario:

Mikrotik is working as as NAS (pppoe server) along with Freeradius for authorization/accounting. When electric power or hardware failure occurs or in a situation where NAS cannot update the FR about user is being disconnected & not active any more, the FR will consider user ACTIVE in radacct table, therefore on next dialup attempt by the user (once everything is restored), he will get access denied because

  1. There is Simultaneous-Use attribute to prevent multiple login from same user
  2. user accctsoptime is NULL because FR have not receive any update from the NAS about user is not online any more.

To remove such stale sessions, there are various methods, you can make your own bash script & schedule it to run every x minutes (example every 5 minutes). Or you can use IF query in authorize session so that when user tries to reconnect & his sessions have NULL  then in this case the query should put stop entry in acctstoptime and allow user new login. or make a PHP program that can be scheuled to run every 5 minutes and then query the radacct session for users whose account update have not received from the NAS.


STEP – 1

First you need to add lastupdate column in your RADACCT table (in radius db) . Infact all solutions posted in this guide relies on it. so add it

ALTER TABLE 'radacct' ADD 'lastupdate' TIMESTAMP NOT NULL AFTER 'xascendsessionsvrkey';

Now you can use following methods, which ever is ok with you, or you can combine both as well, I tested it a local network & worked well for me …. z@iB



Solution # 1
Using BASH in CRON

You can use following bash script to run every 5 minutes which will check for any stale session by matching last update time with current. If the radius have not received any updates for that account from the NAS for more then 5 minutes , it will consider this session as STALE and will close its session its record.

mysql -uroot -pSQLPASS -s --skip-column-names -e "use radius; UPDATE radacct SET acctstoptime = NOW(), acctterminatecause = 'SCRIPT ACTION' WHERE acctstoptime IS NULL AND lastupdate < DATE_SUB(NOW(), INTERVAL 10 MINUTE)";

Solution#2
This will update the radacct acctstoptime only if user will try to re-connect, ]

Edit /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

for compiled RADIUS , use this path

nano /usr/local/etc/raddb/sites-enabled/default

in AUTHORIZE { section add following query

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}"){
}
}

Save & restart freeradius server. This way if NAS goes out, the session will still show online in radacct table, but when user will relogin next time, his session on radacct table will update and new entry will be created.

Some DRY run theory !

To ALL: read this slowly & carefully …

First, There are some fundamentals you need to understand in order to troubleshoot the STALE session issue or session not appearing in the DMA or your panel.

Example what is interim update & why it is important.

Once user dials-in & get authenticated with the NAS , its sessions is recorded/opened in the Radius database called ‘radacct’ with acctstoptime set to NULL means the session is active (this is how radius tells ACTIVE number of users by checking acctstoptime field)
when you set interim update setting in the NAS , it means NAS will send all active users accounting to the Radius and it will be updates in the radacct table.
Ok, now consider this scenario.
the NAS got rebooted by power failure ,obviously all users got disconnected but the NAS didn’t got the time to inform the RADIUS about this event. Now the NAS is online and receiving the users authentication request, when the NAS ask radius on user account status to decide whether to let user connect or not, Radius will inform NAS that user is already ACTIVE (why? because radius didn’t received any user disconnection request from the NAS earlier so radius still thinks the user is active whereas the user is not). NAS will reject all these users requests and log USER IS ALREADY ACTIVE !!!

Hmmm, so what’s the manual solution? Clear all the session in radacct table whose acctstoptime is NULL but there are caveats in it, playing with such sensitive table is really not understable.

& what about any automated solution to this problem?

in dMA there is a setting called INACTIVITY TIMER, which is set to 10 minute default. What does this means? it means that if the RADIUS does not receives any account update for connected session for 10 mnts, then Radius will consider that these sessions are dead and it should close them in radacct (acctstoptime set to currenttime), this will let user login again.

Now consider this scenario.

All users are logged in, NAS & Radius have same number of online users. Now suddenly dueto to faulty network cable , communication between the NAS and the Radius got disconnected. What will happen now?

All users will remain ONLINE in NAS, but on radius, after 10 mnts radius will clear all online session and declare them dead and consider them offline. Now after 15 minutes the connectivity between NAS and DMA got restored. now the NAS will again send the interim updates to the RADIUS, but since the radius have already closed this session due to INACTIVITY SETTINGS, these interim updates will be DISCARDED, oh Yes !

Now once you understand the logics, & if you have common sense as well, you will understand the possible causes of why there are less number of users bein showed in the ONLINE session of RADIUS.

setting INTERIM update timings to too low will overflood your low specs radius. setting it to too high will create problem of FALSE Session & already logged in issues.

This is why experienced personnels will always invest good in hardware selection like good CPU with multiple cores and CPU cache level, sufficient memory (its really cheap now a days), STORAGE< this is most important section of overall readius performance. INvest in good brand SSD drives , preferably with Radius 10 model as this will give you same level of read+write speed.

Tuning up MYSQL is also equivalent important to digest interim updates at earliest.

Now if some is unable to digest all of these, he is free to vomit out all 🙂

Try setting your interim update to 3 minutes ! rest DMA will settle all as per the 10 mnts intervale ( its user configurable option)


Regard’s
SYED JAHANZAIB

%d bloggers like this: