MikroTik Radius Configuration with FreeRADIUS and MySQL

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.

MikroTik Router with FreeRADIUS and MySQL Server

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.

[root@freeradius ~]# yum install freeradius-mysql -y

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.

[root@freeradius ~]# yum install mariadb mariadb-server –y

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.

[root@freeradius ~]# systemctl start mariadb
[root@freeradius ~]# systemctl enable mariadb

You can check your MariaDB status at any time with the following command.

[root@freeradius ~]# systemctl status mariadb

mariadb.service – MariaDB database server

Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled; vendor et: disabled)

Active: active (running) since Thu 2018-12-13 18:11:15 EST; 1 day 8h ago

Process: 21380 ExecStartPost=/usr/libexec/mariadb-wait-ready $MAINPID (code=exited, status=0/SUCCESS)

Process: 21348 ExecStartPre=/usr/libexec/mariadb-prepare-db-dir %n (code=exite d, status=0/SUCCESS)

Main PID: 21379 (mysqld_safe)

Tasks: 20

CGroup: /system.slice/mariadb.service

├─21379 /bin/sh /usr/bin/mysqld_safe –basedir=/usr

└─21542 /usr/libexec/mysqld –basedir=/usr –datadir=/var/lib/mysq…

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.

[root@freeradius ~]# firewall-cmd –zone=public –add-service=mysql
[root@freeradius ~]# firewall-cmd –zone=public –permanent –add-service=mysql

Setup MySQL root Password

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.

[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

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;

+————-+————–+——+—–+—————+—————-+

+————-+————–+——+—–+—————+—————-+

| id | int(10) | NO | PRI | NULL | auto_increment |

| ports | int(5) | YES | | NULL | |

+————-+————–+——+—–+—————+—————-+

9 rows in set (0.00 sec)

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

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.

[root@freeradius raddb]# systemctl restart radiusd

FreeRADIUS with MySQL Testing

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.

[root@freeradius ~]# systemctl restart radiusd

How to configure MikroTik Radius to connect with freeRADIUS Server and how to configure MikroTik login with freeRADIUS user was discussed in my previous article. If you are a newbie in MikroTik with freeRADIUS Server, read carefully that article and test bob user from your MikroTik Login. If everything is OK, you will be able to login with database user from your login prompt.

In this article I just show how to install and configure freeRADIUS with MySQL(MariaDB) database. In the next article, I will show how to create user group in MySQL and apply user limitation groupwise.

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.

Why not a Cup of COFFEE if the solution?

If You Find This Article Helpful, We May Expect:

Share on Facebook | because your friends may also be benefited with this article.
Like Facebook Page | so that we can reach you with new topics by social media.
Subscribe to System Zone | so that we can reach you with new arrival by the email.
Subscribe to YouTube Channel | so that we can reach you with new video topics.

System Zone's Offer:

Ask for New Topic | which will be researched and published with step by step guide.
Join System Zone as Author | so that we can share your experience with thousand of loyal readers.

« Previous Post

MikroTik Radius Configuration with FreeRADIUS

FreeRADIUS User Group Management with MySQL

One comment centos freeradius mysql, freeradius mysql centos 7 mikrotik, freeradius mysql database, freeradius with mysql and mikrotik freeradius and mysql, radius mysql and mikrotik, sql accounting database sample