How to configure remote Authentication using Freeradius and SQL on Ubuntu

This is a comprehensive guide on how to configure remote Authentication using Freeradius and SQL.

1. Install Prerequisites

sudo apt-get update
sudo apt-get install php5-common php5-gd php-pear php-db libapache2-mod-php5 php-mail mysql-server

You will also create the SQL Database in the process.

R1.PNG

2. Install Freeradius packages

sudo apt-get update
sudo apt-get install freeradius freeradius-mysql freeradius-utils

3. Launch mysql

mysql -u root -p

4.Create database and grant access

create database radius;
grant all on radius.* to radius@localhost identified by "passwordinquotes";
quit;

5. Insert database schema & nas

Enter your mysql password for root

mysql -u root -p radius < /etc/freeradius/sql/mysql/schema.sql
mysql -u root -p radius < /etc/freeradius/sql/mysql/nas.sql 
Radius_SQL_3.PNG

6. Check table structure

use radius;
show tables;
Radius_SQL_34.PNG

7. Create a test user

In this example the username is test and the password is test

insert into radcheck (Username, Attribute, op, Value) VALUES ('test', 'Cleartext-Password', ':=','test');

select * from radcheck;
Radius_SQL_6.PNG

8. Create a Test NAS Client

insert into nas (nasname,shortname,type,secret,description) VALUES ('127.0.0.1','localhost','other','test','test nas for localhost');
Radius_SQL_7.PNG

9. Configure Radius

9.1 Amend the radius config

This will change the radius database from the default (flat) to a MySQL database

nano /etc/freeradius/sql.conf
    database = mysql
    login = radius
    password = passwordwesetforradiusmysqluse

9.2 Uncomment the following (remove # symbol)

This means that the line of the script can be read

nano /etc/freeradius/sql.conf
readclients = yes 
read_groups = yes 

readclients = yes << Read groups instead of having to add a fallthrough attribute for each users on the Radreply

read_groups = yes << Read groups instead of having to add a fallthrough attribute for each users on the Radreply

9.3 Uncomment the following (remove # symbol) from sql under the following headings
Accounting, Session and Post-auth-typ

nano /etc/freeradius/sites-enabled/default

9.4 Uncomment $INCLUDE sql.conf (remove # symbol)
Comment #$INCLUDE clients.conf (remove this line by commenting it #)

 
nano /etc/freeradius/radiusd.conf

10.Test

Before testing make sure you restart the service and following any additions made to the Database you will need to restart the service.

 
/etc/init.d/freeradius restart
radtest test test localhost 1812 test
Radius_SQL_9.PNG

11. A few more commands

 
 
STOP - /etc/init.d/freeradius stop
START - /etc/init.d/freeradius start
STATUS - /etc/init.d/freeradius status
RESTART -/etc/init.d/freeradius restart
RUN IN DEBUG MODE - freeradius -XXX

Create a user using an encrypted password for a FreeRadius User

First generate the encrypted string

echo -n PASSWORDYOUWANTSHA1SUMFOR | sha1sum | awk '{print $1}'

and then add it to Radius database

insert into radcheck (Username, Attribute, op, Value) VALUES ('username', 'SHA1-Password',':=','SHAPASSWORDGENERATEDABOVE')

How to Disable a FreeRadius user

INSERT INTO radcheck VALUES (null,'username','Auth-Type',':=','Reject'); 

Thank you for reading – please feel free to leave a comment