Ubuntu Server is one of most popular open-source Linux Server distribution. It is stable and reliable than other Linux distributions. So, enterprise level application can be hosted on Ubuntu Server. Ubuntu RADIUS Server is popularly used for remote authentication and mostly used with the freeRADIUS open source RAIDUS application.
freeRADIUS is one of the most popular and powerful AAA (Authorization, Authentication and Accounting) application. Any enterprise (specially ISP company) can use freeRADIUS for AAA solution and can develop billing system. freeRADIUS can be easily installed and configured in Ubuntu Linux Server and can be used as Ubuntu RADIUS Server.
In my previous article, I discussed how to installed Ubuntu Linux Server with LVM and in this article I am going to discuss how to install and configure freeRADIUS daemon in Ubuntu Linux Server and how to use Ubuntu RADIUS Server with freeRADIUS.
freeRADIUS installation in Ubuntu Server
freeRADIUS package is available in Ubuntu Server package repository. At the time of writing this article, the available version of freeRADIUS is freeRADIUS 3. You can find the current available version with the following command.
# sudo apt search freeradius
With the above command, you will find a lot of freeRADIUS packages available in Ubuntu Repository. Among these packages, we will only install the basic freeradius and freeradius-utils package initially.
Issue the following command to install freeRADIUS basic packages in Ubuntu Server.
# sudo apt install freeradius freeradius-utils -y
Within few times, the freeRADIUS daemon will be installed in your Ubuntu Server. You can verify freeRADIUS installation in Ubuntu Server with the following command.
# sudo apt list –installed | grep freeradius
Managing freeRADIUS Service in Ubuntu Server
The freeradius service will be started by default after installing the freeradius package. We can see the status of the freeradius service with the following command.
# sudo systemctl status freeradius
We will now find that the freeradius service is active and running. To make it start automatically when Ubuntu Server rebooted, issue the following command.
# sudo systemctl enable freeradius
We can also start or restart the freeradius service with the following two commands respectively.
If we need to stop the freeradius service for any reason, we can issue the following command.
# sudo systemctl stop freeradius
Testing freeRADIUS Installation and Default Configuration
To test and debug freeRADIUS installation and configuration as well as freeRADIUS data send and receive, we can run freeRADIUS as debug mode. To run freeradius as debug mode in Ubuntu Server, issue the following command but make sure the freeradius service is stopped.
# sudo freeradius -X
With the above command, you will find the following output at the bottom that means your server is ready to accept NAS authentication, authorization and accounting request.
Listening on auth address 127.0.0.1 port 18120 bound to server inner-tunnel Listening on auth address * port 1812 bound to server default Listening on acct address * port 1813 bound to server default Listening on auth address :: port 1812 bound to server default Listening on acct address :: port 1813 bound to server default Listening on proxy address * port 54279 Listening on proxy address :: port 52868 Ready to process requests
Allowing RADIUS Ports in Firewall
From the above command, you can see that freeradius is listening the auth (Authorization and Authentication) request on UDP port 1812 and the acct (Accounting) request on UDP port 1813. So, we have to keep allowed these UDP ports in our installed firewall in Ubuntu Server. As I am fond of Firewalld daemon for firewalling in Ubuntu Server, I am showing how to enable these UDP ports in firewalld service. If you use any other firewall daemon, use that service to allow these two UDP ports.
Issue the following command to allow these ports in firewalld service (on public zone, if you have any other zone, enable these ports on that zone also if required).
freeRADIUS Server Basic Configuration in Ubuntu Server
We will now do FreeRADIUS basic configuration which includes RADIUS client (NAS) and RADIUS User configuration. In Ubuntu FreeRADIUS Server, all the configuration files are placed in /etc/freeradius/version_number directory. So, go to this directory and open clients.conf file.
# cd /etc/freeradius/3.0/ # vim clients.conf
In this file, a default RADIUS client named localhost is configured by default for testing purpose. So, we will use this client for testing FreeRADIUS configuration. The default configuration of the localhost client looks like the following lines.
client localhost { ipaddr = 127.0.0.1 secret = testing123 require_message_authenticator = no nas_type = other }
Similarly, we can add other NAS devices such MikroTik Router, Cisco Router etc. We will discuss how to add NAS devices in clients configuration file in the upcoming tutorials. Now we will add our test users in FreeRADIUS Server.
By default, user will be authorized and authenticated from users file in FreeRADIUS Server. So, open the users file located in this directory and add the following bob user at the top of this file. Also make sure that the second and third lines are indented by a single tab character.
In the above user information, the first line contains authorization and authentication information which is user name and password, and the rest of the line contains AVPs (Attribute Value Pair) those will be returned when the user will be authenticated.
FreeRADIUS basic configuration has been completed. Now it is time to test the configuration. FreeRADIUS provides radtest and radclient tools to test user and its configuration. We will use radclient tool test our bob user.
So, reload the freeradius daemon and issue the following radclient command and observe the output of this command.
Sent Access-Request Id 10 from 0.0.0.0:60243 to 127.0.0.1:1812 length 43
User-Name = “bob”
User-Password = “password”
Cleartext-Password = “password”
Received Access-Accept Id 10 from 127.0.0.1:1812 to 127.0.0.1:60243 length 38
Framed-IP-Address = 192.168.10.10
Reply-Message = “Hello, bob”
The above output of the radclient command is showing how auth request is being sent to the radius server and how the response is being sent to the Radius client.
So, Ubuntu RADIUS Server with freeRADIUS is now ready to accept Radius client (NAS) request and sent response to the NAS. In the next article, we will learn how to add MikroTik Router as NAS device of the freeRADIUS Server and authenticated RouterOS system user from Ubuntu RADIUS Server.
How to install and configure freeRADIUS Server in Ubuntu Server has been discussed in this article. I hope, you will now be able to install and configure freeRADIUS Server in Ubuntu Server. However, if you face any issue to install and to do basic configuration of freeRADIUS Server, feel free to discuss in comment or contact me from Contact page. I will try my best to stay with you.
FreeRADIUS is a high performance RADIUS Server that accepts a large number of networking devices as RADIUS Client including MikroTik Router. MySQL is one of the best user sources for freeRADIUS server. In my previous article, I discussed how to install freeRADIUS server on CentOS 7 and how to integrate MySQL module with freeRADIUS server. I also discussed how to create user group and profile in MySQL database server and authenticate MikroTik PPPoE client with MySQL database user. I used MySQL Command Line Interface (CLI) to insert user information in database server. But most of the people like Graphical User Interface (GUI) rather than Command Line Interface (CLI). A lot of Graphical Software (such as MySQL Workbench, DBTools Manager, phpMyAdmin and so on) can be found to manage MySQL (MariaDB) database graphically. Among these, in this article I will show how to install and configure phpMyAdmin to manage freeRADIUS MySQL database because phpMyAdmin is a free MySQL (MariaDB) management tool over Web Interface.
phpMyAdmin Installation and Configuration on CentOS 7
phpMyAdmin is a free and open source MySQL and MariaDB database administration tool that is written in PHP and can manage over Web Interface. So, to get phpMyAdmin we should have PHP installed and Web Server (http service) running. As our freeRADIUS Server and MariaDB Database Server are running on CentOS 7, we will only install and configure Web Server, PHP and phpMyAdmin and then login to our MariaDB Database Server with phpMyAdmin Web Interface and manage our freeRADIUS users. The following steps will show how to install and configure Web Server, PHP and phpMyAdmin.
Step 1: Apache Web Server Installation and Configuration
Apache is an open-source and multi-platform web server application. Apache has a full range of web server features including CGI, SSL and virtual domains. In CentOS Linux, the Apache Server package is httpd (HTTP Daemon). So, we will now install httpd package that will turn on Apache HTTP Server in CentOS Linux.To install Apache HTTP Server, issue the following command from your CentOS 7 terminal.
[root@freeradius ~]# yum install httpd -y
The httpd package will be installed within a few second. After installing apache httpd package, we have to start the Apache service with the following command.
[root@freeradius ~]# systemctl start httpd
Apache service is now active and running and waiting for the incoming web (http) requests. The daemon will now answer any incoming http request.
But if your server gets rebooted in any case, the httpd daemon will not be stated automatically. So, run the following commend to start apache service automatically if any system restart is occurred.
[root@freeradius ~]# systemctl enable httpd
You can check your web server status at any time with the following command.
[root@ freeradius ~]# systemctl status httpd
HTTP Service runs on TCP 80 port. So, we have to allow port 80 or http service in CentOS firewall.
So, issue the following firewall commands to allow http service through your firewall.
Now open your web browser and navigate to http://localhost/ from your server or http://server-ip-address/ from any network workstation. If everything is OK, you will get the apache test page like the below image.
Step 2: PHP Installation
After installing http service, we have to install PHP because phpMyAdmin is written in PHP (hypertext preprocessor) server side scripting language. To install PHP in CentOS Linux, issue the following command in your terminal.
[root@freeradius ~]# yum install php php-mysql -y
Now install some common PHP modules that are sometimes required with the following command.
To test PHP installation, create a simple php file (testphp.php) in Apache document root folder (by default /var/www/html) with the following command.
[root@freeradius ~]# vim /var/www/html/testphp.php
And add the following php code in this file.
<?php phpinfo(); ?>
Now restart httpd service with the following command.
[root@freeradius ~]# systemctl restart httpd
Type http://ip-address/testphp.php in your browser to open phptest.php file. If everything is OK, you will fing all the details about PHP such as PHP version, build date and commands etc like the below image.
Step 3: phpMyAdmin Installation
We will now install phpMyAdmin in our CentOS Linux. By default phpMyAdmin is not found in CentOS official repositories. So, we have to install it using EPEL repository. To install EPEL repository, first download EPEL package with the wget command and then install it in your CentOS system according to the following command.
EPEL repository is now ready. So, issue the following command to install phpMyAdmin.
[root@freeradius ~]# yum install phpmyadmin –y
phpMyAdmin is now installed in our CentOS Linux. But before getting phpMyAdmin web interface, we have to do a simple configuration.
phpMyAdmin Configuration
To get phpMyAdmin web interface, we have to edit the phpmyadmin.conf file. So, open phpmyadmin.conf file with a text editor.
[root@freeradius ~]# vim /etc/httpd/conf.d/phpMyAdmin.conf
Now find and comment the whole <Directory> section as shown below.
[…] Alias /phpMyAdmin /usr/share/phpMyAdmin
Alias /phpmyadmin /usr/share/phpMyAdmin
#<Directory /usr/share/phpMyAdmin/>
# <IfModule mod_authz_core.c>
# # Apache 2.4
# Require local
# </IfModule>
# <IfModule !mod_authz_core.c>
# # Apache 2.2
# Order Deny,Allow
# Deny from All
# Allow from 127.0.0.1
# Allow from ::1
# </IfModule>
#</Directory>
[…]
And add the following lines just below the commented section.
<Directory /usr/share/phpMyAdmin/> Options none AllowOverride Limit Require all granted </Directory>
Restart the httpd service with the following command.
[root@freeradius ~]# systemctl restart httpd
Now phpMyAdmin can be found by navigating http://server-ip-address/phpmyadmin/ from your web browser.
Now login to phpMyAdmin with your root user credential and you will find all the databases in your MySQL or MariaDB database server including our desired radius database. Click on the radius database and you will find the tables created in radius database in the right panel. Click on any table and you will find inserted data in your table.
You can export your database or import a backup database with the Export and Import tab respectively as well as you can do any database operation from here graphically.
We will now insert a new user (jack) who will get 2M_Profile. So, his two entries in radcheck table will be…
username
attribute
op
value
jack
Cleartext-Password
:=
passme
jack
User-Profile
:=
2M_Profile
By default phpMyAdmin provides two rows at a time to insert. But we want to insert one row at a time. So, we need to change the default settings. The following steps will show how to change default edit mode in phpMyAdmin.
Click on Settings tab from phpMyAdmin home page.
Under Settings tab, click on Main panel button and then click on Edit mode tab.
From Edit mode tab, change Number of inserted rows from 2 to 1.
Now click Apply button.
Default insert row has been changed. Now click on radius database and click on radcheck table and then click on Insert tab. Put username (jack), attribute (Cleartext-Password), op (:=) and value (passme) for jack user and click Go button.
You will find 1 row inserted message. Click on Browse button and you will find jack is inserted here. Now click Insert tab again to insert jack’s profile. Put username (jack), attribute (User-Profile), op (:=) and value (2M_Profile) for jack user and click Go button.
Clicking browse button, you will also find that the profile of jack user has been inserted there.
New user creation with user profile has been completed with the phpMyAdmin database management tool. You can create as many users as you need following this step. Now we will check this user with the radtest program. So, login to the radius server with root user credential and issue the following command.
[root@freeradius ~]# radtest jack passme 127.0.0.1 100 testing123 1
Sent Access-Request Id 73 from 0.0.0.0:36674 to 127.0.0.1:1812 length 80
User-Name = “jack”
User-Password = “passme”
NAS-IP-Address = 192.168.40.10
NAS-Port = 100
Message-Authenticator = 0x00
Framed-Protocol = PPP
Cleartext-Password = “passme”
Received Access-Accept Id 73 from 127.0.0.1:1812 to 0.0.0.0:0 length 136
Framed-Protocol = PPP
Framed-Compression = Van-Jacobson-TCP-IP
Framed-Pool = “2M_pool”
Mikrotik-Rate-Limit = “2M/2M 4M/4M 2M/2M 40/40”
Reply-Message = “Hello jack! The database time is now 01:56:07. Your usage is 0”
[root@freeradius ~]#
If everything is OK, you will find the above output that means jack is a freeRADIUS user who is able to use PPP service and his bandwidth limit is 2mbps. So, you can use this user to authenticate as a MikroTik PPPoE client that I described in the previous article.
How to install and configure phpMyAdmin database management tool to manage freeRADIUS MySQL database has been discussed in this article. I hope you will be able to install phpMyAdmin to manage your freeRADIUS MySQL. However, if you face any confusion, discuss in comment or contact with me from Contact page. I will try my best to stay with you. In near future, we will learn freeRADIUS unlang to control freeRADIUS process as well as how to control freeRADIUS graphically with PHP language. So, keep tune with SystemZone.
PPPoE Service is one of the most popular services in MikroTik Router. It is specially used by the local ISP provider for the simplicity of client connection. MikroTik PPPoE Service accepts user or client authentication and authorization both MikroTik local user database and RADIUS user database. RADIUS Server provides an efficient way to manage user authentication, authorization and accounting. FreeRADIUS is a high performance RADIUS suite that provides user authentication, authorization and accounting facility for a number of network devices including MikroTik Router. So, connecting MikroTik Router with freeRADIUS Server, PPPoE user can be managed efficiently. How to configure freeRADIUS server with MySQL database and how to configure user profile with MySQL were discussed in my previous article. In this article, I will discuss how to configure MikroTik PPPoE Service and how to authenticate and authorize PPPoE user from freeRADIUS Server.
Network Diagram
To configure MikroTik PPPoE Service with freeRADIUS Server, I am following a network diagram like the below image.
In this network, MikroTik Router’s (RouterBOARD 1100 AHX2) WAN interface (ether1) is connected to internet through WAN distribution switch having IP address 192.168.40.8/25. MikroTik Router also has a LAN network having IP network 10.10.60.0/24. The freeRADIUS Server, installed on CentOS 7 Linux Server with MySQL Server, is also connected to internet through WAN switch having IP address 192.168.40.10. So, MikroTik Router can see freeRADIUS Server through WAN interface and WAN switch.
In this article, we will configure MikroTik PPPoE Server and connect with freeRADIUS Server to authenticate and authorize PPPoE users from freeRADIUS Server. So, design your MikroTik and freeRADIUS network according to the above network diagram and follow below sections to configure your MikroTik PPPoE network with freeRADIUS Server.
MikroTik PPPoE Server with freeRADIUS
We are now going to configure MikroTik PPPoE network with freeRADIUS Server. Complete configuration can be divided into the following 7 parts.
MikroTik Router basic configuration
MikroTik PPPoE Server configuration
MikroTik IP Pool Configuration
MikroTik Radius client configuration
FreeRADIUS user profile configuration
Adding MikroTik Router as a FreeRADIUS NAS device.
Testing PPPoE user authentication and authorization
The following section will describe the above seven parts step by step.
Part 1: MikroTik Router Basic Configuration
MikroTik Router basic configuration includes assigning WAN IP, LAN IP, DNS IP and NAT and Routing Configuration. The following steps will show how to perform these topics in your MikroTik RouterOS.
Login to your MikroTik Router using Winbox with full permission user such as admin user.
Go to IP > Addressesmenu item. Address List window will appear.
Click on PLUS SIGN (+). New Addresswindow will appear.
Put RouterOS WAN IP (RouterOS WAN IP: 192.168.40.8/28) in Addressinput field and choose WAN interface (ether1) from Interface dropdown menu and then click Apply and OK
Again click on PLUS SIGN (+) and put LAN Gateway IP (LAN Gateway IP: 10.10.60.1/24) in Address input field and choose LAN interface (ether2) from Interface dropdown menu and click Apply and OK button.
Now go to IP > DNS. DNS Settingswindow will appear. Put your DNS server IP (Public DNS IP: 8.8.8.8 or 8.8.4.4) in Servers input field and then click Apply and OK button.
Go to IP > Routes. Route Listwindow will appear. Click on PLUS SIGN (+). New Route window will appear. Click on Gateway input box and put your internet gateway IP (192.168.40.1) in this input field. Click on Apply and OK
Go to IP > Firewall menu item and click on NAT tab and then click on PLUS SIGN (+). New NAT Rule window will appear.
In General tab, choose srcnat from Chain dropdown menu and click Action tab and then choose masquerade from Action dropdown menu.
Click on Apply and OK button.
MikroTik Router basic configuration has been completed. Now we will configure PPPoE Server in our MikroTik RouterOS.
Part 2: MikroTik PPPoE Server configuration
After completing basic RouterOS configuration, we will now configure PPPoE server in our MikroTik RouterOS. The following steps will show how to configure PPPoE server in MikroTik Router.
Click on PPPmenu item from Winbox left menu bar. PPP window will appear now.
Click on PPPoE Serverstab and then click on PLUS SIGN (+). New Interface window will appear.
Under Generaltab, put PPPoE Server Interface name what you want or you can keep automatic name. Click Apply and OK button.
Click on PPPoE Serverstab and then click on PLUS SIGN (+). New PPPoE Service window will appear.
Put your PPPoE Server name in Service Nameinput box and then choose interface on which you want to setup PPPoE Server (ether2) from Interface dropdown menu. Click Apply and OK button.
Click on Profilestab and then double click on Click on Local Address input field and put PPPoE Server’s IP Address (10.10.60.1) in this text field. Optionally, you can put DNS server for your PPPoE Client in DNS Server input box. If you want that only one connection is allowed with a username and password, click on Limits tab and click on yes radio button from Only one panel. Click Apply and OK button.
Now click on Secrets tab and then click on PPP Authentication and Accounting PPP Authentication and Accountingwindow will appear. Click on Use Radius check box and then click on Apply and OK button.
PPPoE Server that will use freeRADIUS Server for user authentication has been configured. Now we will configure IP Pool from where PPPoE client will get IP address.
Part3: MikroTik IP Pool Configuration
Now we will create three IP Pools(512k_pool, 1M_pool and 2M_pool) for three user profiles (512k_Profile, 1M_Profile and 2M_Profile). These user profiles will be created in freeRADIUS Server. The following steps will show how to create IP Pool in MikroTik Router.
Go to IP > Pool menu and click on PLUS SIGN (+). New IP Pool window will appear.
Put pool name 512k_pool in Name input filed and put IP address range (10.10.60.2-10.10.60.254) in Addresses input field. Click Apply and OK button.
Similarly create 1M_pool and 2M_pool and put address range 10.10.70.2-10.10.70.254 and 10.10.70.2-10.10.70.254 respectively.
MikroTik IP Pool configuration has been completed. Now we will configure MikroTik RADIUS client so that it can connect with the freeRADIUS Server.
Part 4: MikroTik Radius client configuration
Configuring Radius client, we tell MikroTik to connect with RADIUS Server and to query user authentication from RADIUS Server. The following steps will show how to configure Radius client in MikroTik RouterOS.
Click on Radiusmenu item from left menu bar. Radius window will appear now.
Click on PLUS SIGN (+). New Radius Serverwindow will appear now.
Click on pppcheckbox from Service panel.
Put Radius Server IP address (192.168.40.10) in Addressinput field.
Put Shared secret (Passkey@85) in Secretinput field. This secret has to put in freeRADIUS Server’s client configuration.
Click Applyand OK button.
Radius client configuration as well as the entire MikroTik PPPoE Server configuration to connect with freeRADIUS Server has been completed. The next step is to configure freeRADIUS user profile for MikroTik PPPoE Server.
Part 5: FreeRADIUS User Profile Configuration
In the previous article, I have discussed how to configure freeRADIUS user profile for MikroTik Router. In that article, I have created three user profiles (512k_Profile, 1M_Profile and 2M_Profile). I have also shown how to create user and assign their profile. So, no need to repeat here. If you are not familiar with freeRADIUS user profile, visit the article and complete user profile configuration. In the next part, I will show how to add MikroTik Router as a NAS device in freeRADIUS MySQL database.
Part 6: Adding MikroTik Router as a FreeRADIUS NAS device
In the previous article, I have shown how to create user profile and how to test user with radtest program. But I have not shown how to add a NAS device in MySQL database. So, the following step will show how to add a NAS device in freeRADIUS MySQL database.
Login to MySQL database with the following command.
[root@freeradius ~]# mysql -uroot -pPasskey85 radius
Don’t forget to replace your database username and password in the above command.
Issue the following command to add MikroTik Router (192.168.40.8) as a NAS device.
MariaDB [radius]> insert into nas (nasname,shortname,type,ports,secret,server,community,description) values(‘192.168.40.8’, ‘mikrotik-client’, ‘other’, NULL,’Passkey@85′,NULL,NULL,’MikroTik Client Router’);
Logout from your database server with the quit command.
MariaDB [(none)]> quit
Restart your freeRADIUS Server. Otherwise your freeRADIUS NAS information will not be updated.
[root@freeradius ~]# systemctl restart radiusd
Our MikroTik Router is now added as a NAS device and freeRADIUS is now ready to reply user request. Now we will test freeRADIUS user with MikroTik PPPoE Server.
Part 7: Testing PPPoE User Authentication and Authorization
We are now ready to test freeRADIUS user profile with MikroTik PPPoE Server. Connect any PC to your MikroTik PPPoE network and configure PPPoE Client. In this article, I am showing the steps to configure PPPoE client on Windows 7. If you use different Operating System, search in Google to find the steps to configure PPPoE Client on that specific Operating System.
Microsoft PC dialer is used to connect with remote PPPoE Server. So, you have to configure Microsoft PC dialer in windows 7 PC to get access to the internet through your MikroTik router. Follow my bellow steps to create PPPoE connection in windows 7 with built in PPPoE wizard.
Connect an Ethernet cable to windows 7 PC from your network switch.
Open Network and Sharing Centerfrom Control Panel.
Now click on Setup a new connection or networklink under Change your networking settings area. Set Up a Connection or Network window will appear.
In this window, click on Connect to the internetoption and click the Next Connect to the Internet window will appear.
Click on Broadband (PPPoE)option from this window and put username (such as: bob) and password that you have created in freeRADIUS user profile configuration. Optionally, you can change connection name in Connection name input field and you can also click on Remember this password option otherwise you have to provide password every time you start your PC. Now click on If you provide correct username and password, The connection to the Internet is ready to use message will be shown. Now click on Close button. A dialer will be created in your windows 7 PC and you can enter your credential anytime to connect Internet with this dialer.
You are now connected to the internet with PPPoE dialer. Browse any site. I hope you will be successful to browse any site now.
Also check your MikroTik Router. You will find your logged in user information in Queues, Interfaces and PPP active connection area.
How to configure MikroTik PPPoE Server with freeRADIUS user profile has been created in this article. I hope you will be able to configure your MikroTik PPPoE Server with freeRADIUS Server. However, if you face any confusion, feel free to discuss in comment or contact with me from Contact page. I will try my best to stay with you. In the next article, I will show how to manage MySQL Database graphically with phpmyadmin web interface tool. So, keep tune with SystemZone.
FreeRADIUS is a high performance RADIUS application that accepts a large number of network devices as RADIUS Client including MikroTik Router. FreeRADIUS MySQL module helps to get user authentication and authorization information from database server and to store user accounting information in database server. Configuring MySQL user group and profile properly, freeRADIUS user restriction can easily be maintained. How to configure user group to apply user limitation was discussed in my previous article. In this article, I will discuss how to configure freeRADIUS user profile to apply user limitation with more efficiently.
FreeRADIUS User Profile Configuration
A user profile is nothing but a user who has no entry in radcheck and radreply table but is a member of one or more groups to hold reply attributes. Assigning group to a profile is done with radusergroup table. User-Profile internal AVP is used to assign a profile to a user with radcheck table. So, a complete profile configuration is done by the following steps.
Creating user limitations with group management
Assigning group to a profile holder user
Assigning created profile to users
In this article, we will create the following three profiles for MikroTik PPPoE users according to the above steps.
Profile Name
Properties
512k_Profile
Bandwidth 512kbps and IP Pool will be 512k_pool
1M_Profile
Bandwidth 1Mbps and IP Pool will be 1M_pool
2M_Profile
Bandwidth 2Mbps and IP Pool will be 2M_pool
Creating User Limitation with Group Management
In freeRADIUS, group is used to categorize user check and reply attributes that actually apply user limitations. The radgroupcheck table contains check AVPs and the radgroupreply table contains reply AVPs. As we will create three user profiles, we have to create three groups also. Our proposed groups name and its check and reply attributes are summarized with the following tables.
Group Name
Check AVPs
Reply AVPs
512k
Framed-Protocol to check PPP
MikroTik-Rate-Limit to apply 512kbps bandwidth and Framed-Pool to assign IP to the requested client.
1M
Framed-Protocol to check PPP
MikroTik-Rate-Limit to apply 1Mbps bandwidth and Framed-Pool to assign IP to the requested client.
2M
Framed-Protocol to check PPP
MikroTik-Rate-Limit to apply 2Mbps bandwidth and Framed-Pool to assign IP to the requested client.
The radgroupcheck table contains group check AVPs. So, we have to insert group check AVP Framed-Protocol in radgroupcheck table. The following steps will show how to insert check AVP in the radgroupcheck table.
Now login to your Database Server and select your RADIUS database (radius). You should replace your database username and password in the following command.
[root@freeradius ~]# mysql -uroot -pPasskey85 radius
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 243
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.
MariaDB [radius]>
Issue the following command to insert Framed-Protocol check AVP for 512k group. As we are creating profile for MikroTik PPPoE user, the value of Framed-Protocol attribute should be PPP.
MariaDB [radius]> insert into radgroupcheck (groupname,attribute,op,value) values (“512k”,”Framed-Protocol”,”==”,”PPP”);
Now issue the following command to insert Framed-Protocol AVP for 1M group.
MariaDB [radius]> insert into radgroupcheck (groupname,attribute,op,value) values (“1M”,”Framed-Protocol”,”==”,”PPP”);
Similarly issue the following command to insert Framed-Protocol for 2M group.
MariaDB [radius]> insert into radgroupcheck (groupname,attribute,op,value) values (“2M”,”Framed-Protocol”,”==”,”PPP”);
To show entry in the radgroupreply table, issue the following command.
MariaDB [radius]> select * from radgroupcheck;
+—-+———–+—————–+—-+——-+
| id | groupname | attribute | op | value |
+—-+———–+—————–+—-+——-+
| 1 | 512k | Framed-Protocol | == | PPP |
| 2 | 1M | Framed-Protocol | == | PPP |
| 3 | 2M | Framed-Protocol | == | PPP |
+—-+———–+—————–+—-+——-+
These entries ensure that group reply only applicable for the PPP request. After inserting group checking, we will now insert group reply AVP in the radgroupreply table. The following steps will show how to insert reply AVPs in the radgroupreply table.
Issue the following command to set IP Pool for 512k group user.
MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“512k”,”Framed-Pool”,”=”,”512k_pool”);
Similarly, issue the following command to set IP Pool for 1M group user.
MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“1M”,”Framed-Pool”,”=”,”1M_pool”);
Again, issue the following command to set IP Pool for 2M group user.
MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“2M”,”Framed-Pool”,”=”,”2M_pool”);
Issue the following command to apply bandwidth limit for 512k group.
Here, Mikrotik-Rate-Limit AVP indicates that 512k group user will get 512k upload and 512k download speed, 1M burst upload and 1M burst download, burst threshold upload 512k and download 512k and burst time is 40s for both upload and download.
Similarly, issue the following command to apply bandwidth limit for 1M group user.
Group reply attributes are inserted successfully. Now we will assign our desired user profile to group.
Assigning Group to a Profile Holder User
After creating groups, it is time to assign group to user. As discussed early, freeRADIUS profile is a user but it has no entry in radcheck and radreply table. So, our proposed three profiles (512k_Profile, 1M_Profile and 2M_Profile) are logical users and we will assign these users to group according to the following table.
Profile Name
Group Name
512k_Profile
512k
1M_Profile
1M
2M_Profile
2M
The radusergroup table contains user to group mapping. So, we need to insert entry in radusergroup table to map our profile and group. The following steps will show how to map profile and group in radusergroup table.
Issue the following command to map 512k_Profile to 512k group.
MariaDB [radius]> insert into radusergroup (username,groupname,priority) values (“512k_Profile”,”512k”,10);
Similarly, issue the following command to map 1M_Profile to 1M group.
MariaDB [radius]> insert into radusergroup (username,groupname,priority) values (“1M_Profile”,”1M”,10);
Again, issue the following command to map 2M_Profile to 2M group.
MariaDB [radius]> insert into radusergroup (username,groupname,priority) values (“2M_Profile”,”2M”,10);
To show radusergroup table entry, issue the following command.
MariaDB [radius]> select * from radusergroup;
+————–+———–+———-+
| username | groupname | priority |
+————–+———–+———-+
| 512k_Profile | 512k | 10 |
| 1M_Profile | 1M | 10 |
| 2M_Profile | 2M | 10 |
+————–+———–+———-+
Profile to group mapping has been completed. Now we will create user and assign profile to user.
Assigning Created Profile to Users
After creating user profile, we can create as many users as we want and assign their profile with User-Profile control attribute for applying user limitation. The radcheck table contains user check attribute. So, to create users, we have to insert username and password as well as other user check attribute in radcheck table. In this article, we will create three users (bob, alice and tom) and assign their profile with radcheck table. The following steps will show how to insert user check attribute in radcheck table.
To create bob user whose password will be passme, issue the following command.
MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“bob”,”Cleartext-Password”,”:=”,”passme”);
Similarly, to create alice user, issue the following command.
MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“alice”,”Cleartext-Password”,”:=”,”passme”);
Again, to create tom user, issue the following command.
MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“tom”,”Cleartext-Password”,”:=”,”passme”);
Now to assign 512k_Profile to bob user, issue the following command.
MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“bob”,”User-Profile”,”:=”,”512k_Profile”);
Similarly, to assign 1M_Profile to alice user, issue the following command.
MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“alice”,”User-Profile”,”:=”,”1M_Profile”);
Again, to assign 2M_Profile to tom user, issue to following command.
MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“tom”,”User-Profile”,”:=”,”2M_Profile”);
To show radcheck entry, issue the following command.
MariaDB [radius]> select * from radcheck;
+—-+———-+——————–+—-+————–+
| id | username | attribute | op | value |
+—-+———-+——————–+—-+————–+
| 17 | bob | Cleartext-Password | := | passme |
| 18 | alice | Cleartext-Password | := | passme |
| 19 | tom | Cleartext-Password | := | passme |
| 20 | bob | User-Profile | := | 512k_Profile |
| 21 | alice | User-Profile | := | 1M_Profile |
| 22 | tom | User-Profile | := | 2M_Profile |
+—-+———-+——————–+—-+————–+
To logout from database, issue the quit command.
We have successfully created three RADIUS users and assigned their profile. Now we will check these users login and reply attributes with radtest program.
FreeRADIUS User Profile Testing with radtest Program
We will now test our user profile configuration with radtest program. So, issue the following command to login with bob user and check his reply attribute.
[root@freeradius ~]# radtest bob passme 127.0.0.1 100 testing123 1
Sent Access-Request Id 14 from 0.0.0.0:41714 to 127.0.0.1:1812 length 79
User-Name = “bob”
User-Password = “passme”
NAS-IP-Address = 192.168.40.10
NAS-Port = 100
Message-Authenticator = 0x00
Framed-Protocol = PPP
Cleartext-Password = “passme”
Received Access-Accept Id 14 from 127.0.0.1:1812 to 0.0.0.0:0 length 152
If everything is OK, the radtest program will show the abobe output. That means, bob user will now be able to login to freeRADIUS client devices with his password and after successful login he will get an IP from 512k_pool and his bandwidth will be according to Mikrotik-Rate-Limit AVP.
Similarly, you can test alice and tom user with radtest program and can check user reply attributes. In the next article, we will test freeRADIUS user profile from a MikroTik Router where MikroTik PPPoE Service will be accessible with these users.
How to configure freeRADIUS user profile with MySQL database has been discussed in this article. I hope you will now be able to configure freeRADIUS user profile according to your organization requirements. However, if you face any confusion, feel free to discuss in comment or contact with me from Contact page. In the next article, I will show how to configure MikroTik PPPoE Service with freeRADIUS Server and authenticate and authorize PPPoE users from these profile users.
FreeRADIUS is a high performance RADIUS Server. A number of network devices can be used with freeRADIUS Server as a RADIUS Client including MikroTik Router. As a modular RADIUS suite, freeRADIUS accepts MySQL module to query user authentication and authorization and to store accounting data. In my previous article, I discussed how to install and configure FreeRADIUS MySQL module. I also discussed how to authenticate MikroTik Login with freeRADIUS MySQL user. In this article, we will talk about how to configure user group in MySQL module to categorize freeRADIUS user’s reply attributes.
FreeRADIUS User Group
Group is used to categorize some attributes those will be applied on some users. In freeRADIUS, these attribute are referred to as AVP. So, before going to configure user group, we should have clear concept on freeRADIUS AVP.
Attribute Value Pair (AVP)
AVP is the workhorse of the RADIUS Protocol. Communication between a RADIUS Server and RADIUS Client is done based on AVPs. AVP can be categorized either check or reply attributes. Check attributes are sent from the client to the server. Reply attributes are sent from the server to the client.
Attributes serve as carriers of information between the RADIUS Client and Server. The RADIUS Client supplies information about itself as well as the user connecting through it using AVP. The RADIUS Server also uses AVP to respond to the client. The Client can then use this response to control the user’s connection based on the AVPs received in the server’s response.
Each RADIUS client device (NAS) such as MikroTik Router has its own attributes known as Vendor-Specific Attributes (VSAs) that are used in Access-Request and Access-Accept packets. For example, when a MikroTik Router wants to authenticate a user from freeRADIUS Server, it sends Access-Request packets with User-Name and User-Password AVPs. If freeRADIUS is able to authenticate this user, it replies with defined reply attributes (such as MikroTik-Group) for this user. The mapping between Server and Client attributes communication is done by Dictionary. The Dictionary for the different vendors is loaded from /usr/share/freeradius directory when freeRADIUS starts.
The freeRADIUS Server also has its own attributes which are known as freeRADIUS internal Dictionary. These attributes are used to perform internal process of freeRADIUS Server and cannot be used by the RADIUS client. For example, Cleartext-Password AVP is used to assign user password and User-Profile is used to create user profile in freeRADIUS and these both attributes are freeRADIUS internal attribute.
FreeRADIUS Group Management with MySQL Database
The SQL database allows defining check and reply attributes for groups. The check attributes are stored in radgroupcheck table and the reply attributes are stored in radgroupreply table.
A Radius user can be assigned to zero or more groups. Groups are assigned through radusergroup table. An entry into this table specifies a priority of a certain group to a user. This allows certain item values in groups with higher priorities (smaller values) to override item values in groups with lower priorities (larger values).
With this in mind, we will do a simple practical example for MikroTik Router. We will create groups for MikroTik Router’s login users. We know that there are there permission levels (full, write and read) for MikroTik login user. So, we will create there groups (fullaccess, writeaccess and readaccess) in our SQL database and assign users to these groups. When a user will be authenticated to login MikroTik Router, he/she will get his permission from radgroupreply table.
How to configure MySQL (MariaDB) database with freeRADIUS was discussed in previous article. If you are new in freeRADIUS with MySQL database, spend time to configure freeRADIUS with MySQL and then follow the below section to configure freeRADIUS user group.
Insert Group Reply AVP in radgroupreply Table
Group reply AVP is stored in radgroupreply table. We will insert MikroTik-Group AVP that controls MikroTik Login user group in this table. We know that MikroTik Router has three permission levels for login user. So, we will create three groups in radgroupreply table and assign group permission with MikroTik-Group AVP. The following steps will show how to insert group reply AVP in radgroupreply table.
Login to your freeRADIUS server with root user.
Now login to your MariaDB server and select radius database.
[root@freeradius ~]# mysql -uroot -pPasskey85 radius
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 24
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.
MariaDB [radius]>
To show available columns in radgroupreply table, issue the following command.
MariaDB [radius]> show columns from radgroupreply; +———–+——————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———–+——————+——+—–+———+—————-+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| groupname | varchar(64) | NO | MUL | | |
| attribute | varchar(64) | NO | | | |
| op | char(2) | NO | | = | |
| value | varchar(253) | NO | | | |
+———–+——————+——+—–+———+—————-+
To create full permission group, issue the below command.
MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“fullaccess”,”Mikrotik-Group”,”:=”,”full”);
To create write permission group, issue the following command.
MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“writeaccess”,”Mikrotik-Group”,”:=”,”write”);
To create read permission group, issue the following command.
MariaDB [radius]> insert into radgroupreply (groupname,attribute,op,value) values (“readaccess”,”Mikrotik-Group”,”:=”,”read”);
To show all entries in radgroupreply table, issue the follow command.
MariaDB [radius]> select * from radgroupreply; +—-+————-+—————-+—-+——-+
| id | groupname | attribute | op | value |
+—-+————-+—————-+—-+——-+
| 2 | fullaccess | Mikrotik-Group | := | full |
| 3 | writeaccess | Mikrotik-Group | := | write |
| 4 | readaccess | Mikrotik-Group | := | read |
+—-+————-+—————-+—-+——-+
Three groups with reply attribute have been created successfully. Now we will create three users in freeRADIUS MySQL database.
Insert freeRADIUS User in radcheck Table
FreeRADIUS user login information is stored in radcheck table. So, we will now insert three user entries (bob, alice and tom) who will login to MikroTik Router in this table. The following steps will show how to insert user information in radcheck table.
To show available columns in radcheck table, issue the following command.
MariaDB [radius]> show columns from radcheck; +———–+——————+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+———–+——————+——+—–+———+—————-+
| id | int(11) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(64) | NO | MUL | | |
| attribute | varchar(64) | NO | | | |
| op | char(2) | NO | | == | |
| value | varchar(253) | NO | | | |
+———–+——————+——+—–+———+—————-+
To insert bob user, issue the following command.
MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“bob”,”Cleartext-Password”,”:=”,”passme”);
To insert alice user, issue the following command.
MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“alice”,”Cleartext-Password”,”:=”,”passme”);
To insert tom user, issue the following command.
MariaDB [radius]> insert into radcheck (username,attribute,op,value) values (“tom”,”Cleartext-Password”,”:=”,”passme”);
To show all entries in radcheck table, issue the following command.
MariaDB [radius]> select * from radcheck; +—-+———-+——————–+—-+——–+
| id | username | attribute | op | value |
+—-+———-+——————–+—-+——–+
| 2 | bob | Cleartext-Password | := | passme |
| 3 | alice | Cleartext-Password | := | passme |
| 4 | tom | Cleartext-Password | := | passme |
+—-+———-+——————–+—-+——–+
FreeRADIUS user creation in database table has been completed. Now we will assign these users to group.
Assigning User to Group
User and group mapping is stored in radusergroup table. So, to assign user to a group, we have to put entry in this table. The following steps will show how to map user to group with radusergroup table.
To show available columns in radusergroup table, issue the following command.
MariaDB [radius]> show columns from radusergroup; +———–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———–+————-+——+—–+———+——-+
| username | varchar(64) | NO | MUL | | |
| groupname | varchar(64) | NO | | | |
| priority | int(11) | NO | | 1 | |
+———–+————-+——+—–+———+——-+
To map bob user to fullaccess group, issue the following command.
MariaDB [radius]> insert into radusergroup (username,groupname,priority) values (“bob”,”fullaccess”,10);
To map alice user to writeaccess group, issue the following group.
MariaDB [radius]> insert into radusergroup (username,groupname,priority) values (“alice”,”writeaccess”,10);
To map tom user to read group, issue the following command.
MariaDB [radius]> insert into radusergroup (username,groupname,priority) values (“tom”,”readaccess”,10);
To view all entries, issue the following command.
MariaDB [radius]> select * from radusergroup; +———-+————-+———-+
| username | groupname | priority |
+———-+————-+———-+
| bob | fullaccess | 10 |
| alice | writeaccess | 10 |
| tom | readaccess | 10 |
+———-+————-+———-+
Assigning user to group has been completed. Now we will first test user group reply with radtest program and then MikroTik login user with Winbox.
Testing User Group with radtest Program
After user group configuration in freeRADIUS MySQL database, we will now test user group reply with radtest program. So, issue the following command to check bob user login with reply AVP.
[root@freeradius ~]# radtest bob passme 127.0.0.1 100 testing123 Sent Access-Request Id 74 from 0.0.0.0:47585 to 127.0.0.1:1812 length 73
User-Name = “bob”
User-Password = “passme”
NAS-IP-Address = 192.168.40.10
NAS-Port = 100
Message-Authenticator = 0x00
Cleartext-Password = “passme”
Received Access-Accept Id 74 from 127.0.0.1:1812 to 0.0.0.0:0 length 32
Mikrotik-Group = “full”
If everything is OK, the radtest program will show the above output. That means, bob user is able to login with username and password and after successful login he will get full permission in MikroTik Router.
Testing User Group with MikroTik Router
Now we will test freeRADIUS user group with our desired MikroTik Router. How to configure MikroTik Radius to connect with freeRADIUS was discussed in my previous article. If you are new in MikroTik Radius with freeRADIUS server, follow that article to configure your MikroTik Router with freeRADIUS server and then issue the following steps to test freeRADIUS user group with MikroTik Router.
Open Winbox software and put your MikroTik Router IP in Connect To input field and put bob username in Login input filed and then put bob password (passme) in Password input field. Now click on Connect button and if everything is OK, you will be able to login to MikroTik Router.
Go to System > Users menu and click on Active Users tab. You will find bob is a RADIUS user and his permission level is full.
Similarly you can login with alice and tom and find their permission level provided in freeRADIUS group configuration.
If you face any confusion to follow above steps properly, watch the below video about freeRADIUS user group management. I hope it will reduce your any confusion.
How to configure freeRADIUS user group has been discussed in this article. I hope you will now be able to configure your freeRADIUS group according to your desire requirement. However, if you face any confusion, feel free to discuss in comment or contact with me from Contact page. I will try my best to stay with you. In the next article, I will discuss how to configure freeRADIUS user profile to manage MikroTik PPPoE user login and their bandwidth. So, keep tuned with System Zone.
FreeRADIUS is a modular RADIUS suite. A lot of modules such as Perl, python, MySQL etc. can be integrated with freeRADIUS to enrich freeRADIUS features. MySQL is one of the best user and client sources in freeRADIUS server. One of the important advantages of MySQL with freeRADIUS server is that MySQL can easily be controlled with a lot of programming languages including PHP. So, freeRADIUS users and their authentication, authorization and accounting can easily be controlled graphically using MySQL database and PHP language. In my previous article, I discussed how to install and configure freeRADIUS on CentOS 7 Linux distribution and how to configure MikroTik Radius to use freeRADIUS as a MikroTik RADIUS server. In this article, I will discuss how to configure freeRADIUS MySQL (freeradius-mysql package) module with MariaDB database.
FreeRADIUS with MySQL (MariaDB) Database
To work with MySQL database, freeRADIUS provides freeradius-mysql package that contains necessary configuration file and SQL queries. So, we have to install freeradius-mysql package with our freeradius server package. To check whether freeradius-mysql package is installed or not, login to your CentOS server with root user and issue the following command from your command prompt.
[root@freeradius ~]# rpm -qa | grep freeradius
freeradius-utils-3.0.13-9.el7_5.x86_64
freeradius-perl-3.0.13-9.el7_5.x86_64
freeradius-3.0.13-9.el7_5.x86_64
freeradius-mysql-3.0.13-9.el7_5.x86_64
The above list is showing that the freeradius-mysql package is installed with freeradius server. If you don’t find freeradius-mysql package in this list, issue the following command to install freeradius-mysql package.
The freeradius-mysql package is now available in your system. In the next section, we will install and configure MariaDB database in our CentOS 7 Linux distribution.
MariaDB Database Installation and Configuration
MariaDB is a most popular and open source database server made by the original developers of MySQL notably Wikipedia, WordPress and Google developers. To install MariaDB database server in your CentOS Linux, enter the following command from your CentOS 7 terminal.
MariaDB package will be installed within few seconds. To ensure it is installed successfully, issue the following command from command prompt.
[root@freeradius ~]# rpm -qa | grep mariadb
mariadb-server-5.5.60-1.el7_5.x86_64
mariadb-libs-5.5.60-1.el7_5.x86_64
mariadb-5.5.60-1.el7_5.x86_64
It is desired that you will find the above list. If so, start the MariaDB service and enable starting MariaDB service automatically on every reboot with the following command.
To allow remote access to your mysql (mariadb) server with third party application, you should allow mysql service (to open port 3306) from your firewall with the following command. Keep in mind this opens port 3306 to all IPs.
By default, MariaDB does not set root user password. But to secure mariadb, we have to setup root user password. To set root user password, run the following command from your terminal and follow the instructions.
[root@webserver~]# mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!
In order to log into MySQL to secure it, we’ll need the current
password for the root user. If you’ve just installed MySQL, and
you haven’t set the root password yet, the password will be blank,
so you should just press enter here.
Enter current password for root (enter for none): ## Press Enter ##
OK, successfully used password, moving on…
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] ## Press Enter ##
New password: ## Enter new password ##
Re-enter new password: ## Re-enter new password ##
Password updated successfully!
Reloading privilege tables..
… Success!
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.
Remove anonymous users? [Y/n] ## Press Enter ##
… Success!
Normally, root should only be allowed to connect from ‘localhost’. This
ensures that someone cannot guess at the root password from the network.
Disallow root login remotely? [Y/n] ## Press Enter ##
… Success!
By default, MySQL comes with a database named ‘test’ that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.
Remove test database and access to it? [Y/n] ## Press Enter ##
– Dropping test database…
… Success!
– Removing privileges on test database…
… Success!
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
Reload privilege tables now? [Y/n] ## Press Enter ##
… Success!
Cleaning up…
All done! If you’ve completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!
Root user password has been set and your MariaDB database is secure now. If you wish to allow root login remotely, provide no at Disallow root login remotely? [Y/n] option.
Login to MariaDB server with root user password with the following command.
[root@freeradius ~]# mysql -uroot -pPasskey85
Welcome to the MariaDB monitor. Commands end with ; or g.
Your MariaDB connection id is 9
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type ‘help;’ or ‘h’ for help. Type ‘c’ to clear the current input statement.
After login you can see available databases with the following command.
MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
+——————–+
3 rows in set (0.00 sec)
You will find the default database and schema. We will now create a new database that will be used by freeRADIUS server to get user information and to keep accounting information. Issue the following command to create a new database named radius in MariaDB database.
MariaDB [(none)]> create database radius;
Query OK, 1 row affected (0.00 sec)
Now you will find your created database in your database server.
MariaDB [(none)]> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| radius |
+——————–+
4 rows in set (0.00 sec)
To logout from your database, just issue quit command.
MariaDB [(none)]> quit Bye
After creating database, we need to create database tables that will be used by freeRADIUS server to find RADIUS users and clients and to store accounting data. Fortunately, freeRADIUS MySQL package provides necessary query to create required tables in schema.sql file. The schema.sql file will be found at mods-config/sql/main/mysql directory. So, execute the following command to create required tables in radius database.
[root@freeradius raddb]# mysql -uroot -pPasskey85 radius < mods-config/sql/main/mysql/schema.sql
After creating tables, we need to create a database user who will be able to read authentication and authorization data as well as will be able to write accounting data. FreeRADIUS MySQL package also provides the necessary query to create a database user and its permission in setup.sql. This file will also be found at mods-config/sql/main/mysql directory. So, issue the following command to create database user and to provide required permission.
[root@freeradius raddb]# mysql -uroot -pPasskey85 radius < mods-config/sql/main/mysql/setup.sql
The default username and password defined in setup.sql is radius and radpass respectively. If you wish, you can change this username and password by editing this file.
If you wish to see created tables in your database, login to MariaDB Server and select radius database and then issue following command.
[root@freeradius ~]# mysql -uroot -pPasskey85 radius MariaDB [radius]> show tables;
+——————+
| Tables_in_radius |
+——————+
| nas |
| radacct |
| radcheck |
| radgroupcheck |
| radgroupreply |
| radpostauth |
| radreply |
| radusergroup |
+——————+
8 rows in set (0.00 sec)
You can also see the columns defined in a table with the following command.
MariaDB [radius]> show columns from nas;
+————-+————–+——+—–+—————+—————-+
| Field | Type | Null | Key | Default | Extra |
+————-+————–+——+—–+—————+—————-+
| id | int(10) | NO | PRI | NULL | auto_increment |
After creating database, database user and tables, we will now create a RADIUS user who will be authenticated and authorized. The radchek table keeps username and password of a user. So, issue the following command to create a radius user.
MariaDB [radius]> insert into radcheck (username,attribute,op,value) values(“bob”, “Cleartext-Password”, “:=”, “password”);
The radreply table keeps the authorization information of a user. As our created bob user will be used to login to MikroTik Router, we will assign his group permission after successful login with the following command.
MariaDB [radius]> insert into radreply (username,attribute,op,value) values(“bob”, “MikroTik-Group”, “:=”, “full”);
Now issue quit command to logout from database.
MariaDB [(none)]> quit Bye
So, till now we have installed MariaDB database server, created database and necessary tables and then created a test user. Now we will configure freeRADIUS server so that freeRADIUS can query its user and client information from database server.
Configuring FreeRADIUS to use MySQL
After configuring database, it is time to configure freeRADIUS server so that it can use database server. MySQL database configuration is defined in sql file located in mods-available directory. So, open sql file and edit the following options in this file.
[root@freeradius raddb]# vim mods-available/sql
driver = “rlm_sql_mysql”
dialect = “mysql”
server = “localhost”
port = 3306
login = “radius”
password = “radpass”
read_clients = yes
If you change username (login) and password in setup.sql file, don’t forget to change here also. Now create softlink for sql modules in mods-enabled directory so that freeRADIUS treats mysql is an enabled module with the following commands.
[root@freeradius raddb]# cd mods-enabled/ [root@freeradius mods-enabled]# ln -s ../mods-available/sql sql
MySQL module is now enabled. We will now configure active site to use MySQL.
Active Site Configuration to Support sql
After enabling MySQL module, it is time to configure active site to use MySQL. So, open sites-available/default file (I am using default site. If you use custom site, enable sql on your custom site at the following sections) and enable sql at the following sections.
[root@freeradius raddb]# vim sites-available/default
Uncomment the line containing sql in the authorize{} section.
Uncomment the line saying ‘sql’ in the accounting{} section to tell FreeRADIUS to store accounting records in SQL as well.
Add or uncomment ‘sql’ to the session{} section if you want to do Simultaneous-Use detection.
Add or uncomment ‘sql’ to the post-auth{} section if you want to log all Authentication attempts to SQL.
Add or uncomment ‘sql’ to the Post-Auth-Type REJECT{} section.
Additionally, edit sites-available/inner-tunnel and uncomment the line containing ‘sql’ under “authorize {}” section.
FreeRADIUS configuration to use MySQL has been completed. Now restart your freeRADIUS server to load MySQL module and test your freeRADIUS and MySQL configuration.
Localhost (127.0.0.1) is a default radius client for freeRADIUS Server. So, we can check freeRADIUS configuration with radtest program from localhost. Issue the following command to check bob user.
[root@freeradius raddb]# radtest bob password 127.0.0.1 100 testing123
Sent Access-Request Id 229 from 0.0.0.0:42190 to 127.0.0.1:1812 length 76
User-Name = “bob”
User-Password = “password”
NAS-IP-Address = 192.168.40.10
NAS-Port = 100
Message-Authenticator = 0x00
Cleartext-Password = “password”
Received Access-Accept Id 229 from 127.0.0.1:1812 to 0.0.0.0:0 length 32
Mikrotik-Group = “full”
If everything is OK, you will find the above response from your radtest program. That means, freeRADIUS and MySQL module is working fine. We will now check this user from MikroTik Router.
MikroTik Router with freeRADIUS and MySQL Server
We will now add our MikroTik Router (IP: 192.168.40.8) as a NAS device and then verify our test user from MikroTik login. The nas table keeps the necessary information for a RADIUS client. So, login to MariaDB server and select radius database and then issue the following command to add MikroTik Router as a RADIUS client.
[root@freeradius ~]# mysql -uroot -pPasskey85 radius
MariaDB [radius]> insert into nas (nasname,shortname,type,ports,secret,server,community,description) values(‘192.168.40.8’, ‘mikrotik-client’, ‘other’, NULL,’Passkey@85′,NULL,NULL,’MikroTik Client Router’);
MariaDB [(none)]> quit
Bye
After inserting nas device, make sure to restart freeRADIUS server. Otherwise, RADIUS client information will not be updated.
If you face any confusion to follow above procedure properly, watch the below video about freeRADIUS MySQL module configuration. I hope it will reduce your any confusion.
MikroTik Radius configuration with freeRADIUS and MySQL (MariaDB) has been discussed in this article. I hope you will now be able to configure freeRADIUS with MySQL Server and be able to connect MikroTik Router with freeRADIUS and MySQL Server. However, if you face any confusion, feel free to discuss in comment or contact with me from Contact page. I will try my best to stay with you.