Syed Jahanzaib Personal Blog to Share Knowledge !

October 31, 2017

Mikrotik with Freeradius/mySQL – Auto MAC Binding on 1st Login – Part 4

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


~ Auto Mac Binding via EXEC / PHP in Freeradius 2.x ~
! From the CORE of FREERADIUS !
Syed jahanzaib



There are others parts too, look at part-1 for listing, i will update only part-1 listing

Personnel Note:

[At end of this guide I used TRIGGER method to auto insert the mac address of user if there is no mac entry in the radcheck table for his username, Trigger are more efficient method in my opinion.]

This post is just for demonstration purposes. in production environment you should make your own module and add it in proper relevant places. This post contains just minimalist working config to begin with. Make sure to refine it in prd environment.

This is another post about freeradius. My aim is to let people know that creating your own Radius Billing system is not ROCKET SCIENCE. The only thing required is the ultimate passion to achieve the goal & with the proper googling , reading a LOT, understand logic’s, then you can do all on your own. Just wanted to break the image that most of professionals don’t like to share there knowledge. I strongly encourage to read the FR mailing list and Google

OP Requirements:

[ Sort of Wired one 😉 ]

We have a working Freeradius installation. All users can login to mikrotik which verify user account authentication requests via this radius. All working fine. Now OP wants to add Auto MAC binding feature so that when user first time login to NAS, his MAC should auto binds with his account, so next time if he tries to login from another workstation, he must get access denied.

Components used in this guide:

  • Ubuntu 12.4 / x86
  • Freeradius 2.1.10 [Default apt-get installation]
  • MySQL 5.5.47 [Default apt-get installation]


To fulfill such weird requirements, we have to use external program example PHP program (via exec) which will be executed when user gets connect successfully. It will then look in RADCHECK table for this specific user MAC address value name “Calling-Station-Id”. If it’s unable to find it, then it will add the entry so that next time user will login his MAC will be verified by the CHECKVAL module in freeradius to match the mac address. If there is mac address entry, it will simply ignore and process further , will also print message that “MAC Entry already found – z@iB”

First enable the CHECKVAL module in following file > /etc/freeradius/sites-enabled/default

nano /etc/freeradius/sites-enabled/default

Search & uncomment the checkval module. Save & Exit.

Now edit EXEC module file by

nano /etc/freeradius/modules/exec

Remove all previous contents (if its lab testing otherwise be careful editing this file) & paste following

exec {
wait = yes
program = "/usr/bin/php /temp/checkmac.php %{User-Name} %{Calling-Station-Id}"
input_pairs = request

Save & Exit.

Now create the php program which will be executed by above module.

mkdir /temp
mkdir /temp/checkmac.php
touch /temp/checkmac.php
nano /temp/checkmac.php

and use following to paste make sure to modify relevant details …


checkmac.php contents

 0) {
printf ("MAC Entry already found by ZAiBBBBBBBBBBBBBBBB");
else {
printf ("Seems to be New User, adding its MAC address in table ...");
mysql_query("INSERT into radcheck (UserName, Attribute, op, Value) values ('$argv[1]', 'Calling-Station-Id', ':=', '$argv[2]')");


Start FR in debug mode by freeradius -X and try to login with the test ID from your workstation (or use the radtest or ntradping)

rad_recv: Access-Request packet from host port 42449, id=45, length=188
Service-Type = Framed-User
Framed-Protocol = PPP
NAS-Port = 15728851
NAS-Port-Type = Ethernet
User-Name = "zaib"
Calling-Station-Id = "0C:84:DC:1E:0B:8D"
Called-Station-Id = "service1"
NAS-Port-Id = "ether10"
MS-CHAP-Challenge = 0x49c4549501e07fad5e6dae708bc815ed
MS-CHAP2-Response = 0x0100acaa712e29adad9abb681c5ef666e69300000000000000003cd5a092d7c816de798b7f5d09acba6f04eeed208cd6c19b
NAS-Identifier = "MIKROTIK"
NAS-IP-Address =
# Executing section authorize from file /etc/freeradius/sites-enabled/default
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
[mschap] Found MS-CHAP attributes. Setting 'Auth-Type = mschap'
++[mschap] returns ok
++[digest] returns noop
[suffix] No '@' in User-Name = "zaib", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
[exec] expand: %{User-Name} -> zaib
[exec] expand: %{Calling-Station-Id} -> 0C:84:DC:1E:0B:8D
Exec-Program output: Seems to be New User, adding its MAC address in table ...
Exec-Program-Wait: plaintext: Seems to be New User, adding its MAC address in table ...
Exec-Program: returned: 0
++[exec] returns ok

As you CAN SEE

“Exec-Program output: Seems to be New User, adding its MAC address in table …”

Now see the difference …

RADCHECK TABLE, Before Login …

1- before login


2- after login ok

When user will login again, radcheck table will be searched, if the mac found it will simply skip the add part and print the statement

[exec] expand: %{User-Name} -> zaib
[exec] expand: %{Calling-Station-Id} -> 0C:84:DC:1E:0B:8D
Exec-Program output: MAC Entry already found
Exec-Program-Wait: plaintext: MAC Entry already found
Exec-Program: returned: 0
++[exec] returns ok

& If the user will login from any other mac/workstation, he will be denied access.

Method #2
Trigger approach to add MAC address automatically upon user connection

Use following TRIGGER on radacct table. It will add the MAC address for the user in RADCHECK table after user is successfully connected. (or you can modify it as well)

-- Triggers `radacct`
CREATE TRIGGER `chk_mac_after_insert` AFTER INSERT ON `radacct` FOR EACH ROW BEGIN
SET @mac = (SELECT count(*) from radcheck where username=New.username and attribute='Calling-Station-ID');
IF (@mac = 0) THEN
INSERT into radcheck (username,attribute,op,value) values (NEW.username,'Calling-Station-ID',':=',NEW.callingstationid);
UPDATE users SET mac = NEW.callingstationid where username = NEW.username;

trigger for mac add.JPG

3# Allow Multiple MAC Login along with CHECKVAL !

We know that CHECKVAL allows only single mac to connect. Recently there was a requirement that a user have 2 systems that he uses in day & night timings. Since he had no wifi router,  therefore he wanted to have 2 MAC allowed. (one Id will connect at one time). So we can Use the += operator instead to allow multiple mac if we are using CHECKVAL operative to limit mac address!

multiple mac

INSERT INTO `radcheck` (`id`, `username`, `attribute`, `op`, `value`) VALUES
(1, 'zaib', 'Cleartext-Password', ':=', 'zaib'),
(11, 'zaib', 'Expiration', ':=', '25 Feb 2019 17:00:00'),
(27, 'zaib', 'Calling-Station-ID', '+=', '24:26:42:D4:BC:43'),
(42, 'zaib', 'Calling-Station-ID', '+=', '00:0C:29:B9:D8:A0');[/soureceode]

<hr />

<h2>Additional Notes for DMASOFTLAB Radius Manager Auto Mac binding on user first login</h2>
in DMA radius Manager there is builtin option to auto bind the user mac upon first login. in DMA panel, Goto <em>Home &gt; Settings &gt; Lock first seen mac</em> , although this option is working fine, but still if you need alternate method using mysql trigger, then use this trigger

Login to mysql, use radius db, and issue

-- MySQL dump 10.13 Distrib 5.5.54, for debian-linux-gnu (i686)
-- Host: localhost Database: radius
-- ------------------------------------------------------
-- Server version 5.5.54-0ubuntu0.12.04.1
/*!40103 SET TIME_ZONE='+00:00' */;
/*!50003 SET @saved_cs_client = @@character_set_client */ ;
/*!50003 SET @saved_cs_results = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
/*!50003 SET sql_mode = '' */ ;
/*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER `chk_mac_after_insert` AFTER INSERT ON `radacct` FOR EACH ROW BEGIN
SET @mac = (SELECT count(*) from rm_users where username=New.username and mac='Calling-Station-ID');
IF (@mac = 0) THEN
UPDATE rm_users SET mac = 'Calling-Station-ID' , usemacauth = '1' where username = 'New.username';
END */;;
/*!50003 SET sql_mode = @saved_sql_mode */ ;
/*!50003 SET character_set_client = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection = @saved_col_connection */ ;

-- Dumping routines for database 'radius'


Syed Jahanzaib ~


  1. […] FREERADIUS WITH MIKROTIK – Part #4 […]


    Pingback by Mikrotik with Freeradius/mySQL # Part-1 | Syed Jahanzaib Personal Blog to Share Knowledge ! — November 2, 2017 @ 8:27 AM

  2. Sir i need ur help pls
    I want to add new ip pool in mikrotik hot spot server. I add pool but its not working. What can i do ?


    Comment by Hassan — November 8, 2017 @ 12:01 AM

  3. […] FREERADIUS WITH MIKROTIK – Part #4 – Auto Mac Binding […]


    Pingback by Mikrotik with Freeradius/mySQL – Quota Limit # Part-7 | Syed Jahanzaib Personal Blog to Share Knowledge ! — January 8, 2018 @ 11:37 AM

  4. […] FREERADIUS WITH MIKROTIK – Part #4 – Auto Mac Binding […]


    Pingback by Mikrotik with Freeradius/mySQL – Change IP Pool After Expiration # Part-3 | Syed Jahanzaib Personal Blog to Share Knowledge ! — January 10, 2018 @ 12:48 PM

  5. […] FREERADIUS WITH MIKROTIK – Part #4 – Auto Mac Binding […]


    Pingback by Mikrotik with Freeradius/mySQL – Trimming & Archiving RADACCT # Part-8 | Syed Jahanzaib Personal Blog to Share Knowledge ! — January 15, 2018 @ 2:40 PM

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: