Mikrotik with Freeradius/mySQL

MYSQL  CONFIGURATION:

Create Freeradius Database in MYSQL

Now create Freeradius Database in mySQL.

Login to mysql (use mysql root password that you entered in above steps)

1 2 3

mysql -uroot -puser1234 create database radius; grant all on radius.* to radius@localhost identified by "user1234";

Import Freeradius Database Scheme in MYSQL ‘radius’ DB

Insert the freeradius database scheme using the following commands, Make sure to change the password ####

1 2 3 4 5

mysql -u root -puser1234 radius < /etc/freeradius/sql/mysql/schema.sql mysql -u root -puser1234 radius < /etc/freeradius/sql/mysql/nas.sql   # For Ubuntu 18, use below... # mysql -u root -puser1234 radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/schema.sql

Create new user in MYSQL radius database (For Testing Users)

User id = user
Password = user
Rate-Limit = 1024k/1024k

1 2 3 4 5

mysql -uroot -puser1234 use radius; INSERT INTO radcheck ( id , UserName , Attribute , op , Value ) VALUES ( NULL , 'zaib', 'Cleartext-Password', ':=', 'zaib'); INSERT INTO radreply (username, attribute, op, value) VALUES ('zaib', 'Mikrotik-Rate-Limit', '==', '1024k/1024k'); exit

Note:
You can skip the Framed-IP-Address part or modify it as per required.

---

FREERADIUS CONFIGURATION:

SQL.CONF

NAS SECTION:

We have to add a NAS entry either in radius NAS table, or in clients.conf so that this NAS will be allowed to send auth request to this freeradius

To enable NAS table via sql, we need to enable it in sql.conf file, follow below method …

Edit following file  /etc/freeradius/sql.conf

1	nano /etc/freeradius/sql.conf file

Change the password to zaib1234 (or whatever you set in mysql if required) and Uncomment the following

1	readclients = yes

So some portion of the file may look like following, after modifications

1 2 3 4 5 6

# Connection info: server = "localhost" #port = 3306 login = "radius" password = "user1234" readclients = yes

Save and Exit the file

---

/etc/freeradius/sites-enabled/default

Now edit the /etc/freeradius/sites-enabled/default

1	nano /etc/freeradius/sites-enabled/default

Uncomment the sql option in the following sections

accounting

# See “Authorization Queries” in sql.conf

sql

session

# See “Authorization Queries” in sql.conf

sql

Post-Auth-Type

# See “Authorization Queries” in sql.conf

sql

[/sourcecode]

Save and Exit the file

---

RADIUSD.CONF

Now edit /etc/freeradius/radiusd.conf file

1	nano /etc/freeradius/radiusd.conf

#Uncomment the following option

$INCLUDE sql.conf

Save and exit the file

---

/etc/freeradius/sites-available/default

Last but no least , edit /etc/freeradius/sites-available/default

1	nano /etc/freeradius/sites-available/default

Search for LINE

#  See “Authorization Queries” in sql.conf

and UN-COMMENT the SQL word below it.

Example After modification

#  See “Authorization Queries” in sql.conf

sql

Save and exit.

---

ADDING ‘NAS’ [Mikrotik] in CLIENTS.CONF

To accept connectivity of Mikrotik with the Freeradius, we need to add the mikrotik IP and shared secret in clients.conf

Edit  /etc/freeradius/clients.conf

1	nano /etc/freeradius/clients.conf

and add following lines at bottom

1 2 3 4

client 10.11.11.255 { secret          = 12345 shortname       = Mikrotik }

Note: Change the IP /Secret according to your Mikrotik Network Scheme.

after any changes either to clients.conf or NAS table, you must restart the freeradius service in order to take changes effect, its a security measure

---

Last but not least, download mikrotik dictionary from

https://wiki.mikrotik.com/wiki/Manual:RADIUS_Client/vendor_dictionary

and copy it in /usr/share/freeradius folder

If freeradius is already running, stop it and restart it.

---

TESTING USER AUTHENTICATION ON FREERADIUS:

Now stop the free radius server

1	/etc/init.d/freeradius stop

and start in DEBUG mode so that we can monitor for any errors etc

1	freeradius -X

Now OPEN another TERMINAL/CONSOLE window and issue following command to TEST USER AUTHENTICATION

1	radtest user userlocalhost 1812 testing123

and you should ACCESS-ACCEPT MESSAGE as below …

1 2 3 4 5 6 7 8 9

root@ubuntu:~#  radtest user user localhost 1812 testing123   Sending Access-Request of id 38 to 127.0.0.1 port 1812 User-Name = "user" User-Password = "user" NAS-IP-Address = 10.11.11.245 NAS-Port = 1812 rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=38, length=39 Mikrotik-Rate-Limit = "1024k/1024k"

Another method

1	echo "User-Name = user, Password = user, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123

1 2 3 4 5 6 7

root@apnaradius:~# echo "User-Name = user, Password = user, Calling-Station-Id =00:0C:29:35:F8:2F" | radclient -s localhost:1812 auth testing123 Received response ID 101, code 3, length = 56 Reply-Message = "user- Your account has expired. \r\n"   Total approved auths: 0 Total denied auths: 1 Total lost auths: 0