Setup freeRADIUS + mySQL + daloRADIUS for dynamic VLAN assignment on Unifi

Using the text based user files in our other freeRADIUS + Unifi = Dynamic VLAN Assignment guide works great for small installations, but in a larger or more fluid environment it’s much easier to use a database backend for the freeRADIUS server. This guide walks through the steps to configure that, and optionally setup a daloRADIUS UI for freeRADIUS.

Install mySQL:
sudo su

apt install mysql-server -y

systemctl status mysql
systemctl enable mysql

mysql_secure_installation

#sudo is required to login as root
mysql -u root mysql -p

CREATE DATABASE radius;
CREATE USER 'freeradius'@'localhost' IDENTIFIED BY 'g6bLf3mjT4AsFDw4flOT$';
GRANT ALL ON radius.* TO freeradius@localhost;
FLUSH PRIVILEGES;
exit
Install freeRADIUS:

apt install freeradius freeradius-mysql freeradius-utils -y

mysql -u root -p radius < /etc/freeradius/3.0/mods-config/sql/main/mysql/schema.sql

mysql -u freeradius -p

use radius;
show tables;
exit

nano /etc/freeradius/3.0/mods-available/sql

ln -s /etc/freeradius/3.0/mods-available/sql /etc/freeradius/3.0/mods-enabled/

chown -h freerad:freerad /etc/freeradius/3.0/mods-enabled/sql

nano /etc/freeradius/3.0/clients.conf

client unifi {
ipaddr = 192.168.1.0
netmask = 24
secret = RADIUSSECRETKEY
}

nano /etc/freeradius/3.0/mods-config/sql/main/mysql/queries.conf

systemctl stop freeradius

freeradius -X

#if stopping here control + C to exit freeradius then run

Install daloRADIUS:
apt install apache2 php libapache2-mod-php php-mail php-mail-mime php-mysql php-gd php-common php-pear php-db php-mbstring php-xml php-curl unzip wget -y

wget https://github.com/lirantal/daloradius/archive/master.zip
unzip master.zip
mv daloradius-master /var/www/html/daloradius
cd /var/www/html/daloradius

mysql -u root -p radius < contrib/db/fr2-mysql-daloradius-and-freeradius.sql
mysql -u root -p radius < contrib/db/mysql-daloradius.sql chown -R www-data:www-data /var/www/html/daloradius/ chmod 664 /var/www/html/daloradius/library/daloradius.conf.php mkdir /var/www/logs/ touch /var/www/logs/daloradius.log chown www-data:www-data /var/www/logs/daloradius.log chmod 664 /var/www/logs/daloradius.log nano /var/www/html/daloradius/library/daloradius.conf.php 

SQL Commands:

use radius;

show tables;

insert into radgroupreply (groupname, attribute, op, value) values ('VLAN#', 'Tunnel-Type', '=', '13');
insert into radgroupreply (groupname, attribute, op, value) values ('VLAN#', 'Tunnel-Medium-Type', '=', '6');
insert into radgroupreply (groupname, attribute, op, value) values ('VLAN#', 'Tunnel-Private-Group-Id', '=', 'VLAN tag #');

INSERT INTO radusergroup (username, groupname, priority) VALUES ('DEFAULT', 'VLAN#FORDEFAULTVLAN', '10');
insert into radgroupcheck (groupname, attribute, op, value) values ('VLAN#FORDEFAULTVLAN', 'Auth-Type', ':=', 'Accept');

INSERT INTO radcheck (username, attribute, op, value) VALUES('MAC', 'Cleartext-Password', ':=', 'MAC');
insert into radusergroup (username, groupname, priority) values ('MAC', 'VLAN#', 10);

INSERT INTO userinfo (username) VALUES('MAC');