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.

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.

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.

You can check your MariaDB status at any time 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.

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 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.

After login you can see available databases with the following command.

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.

Now you will find your created database in your database server.

To logout from your database, just issue quit command.

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.

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.

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.

You can also see the columns defined in a table with the following command.

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.

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.

Now issue quit command to logout from database.

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.

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.

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.

• 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.

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.

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.

After inserting nas device, make sure to restart freeRADIUS server. Otherwise, RADIUS client information will not be updated.

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.