Setup and Configuration of FreeRadius + MySql on Ubuntu 14.04 64bit

The FreeRADIUS Server is a daemon for unix and unix like operating systems which allows one to set up a radius protocol server, which can be used for Authentication and Accounting various types of network access. To use the server, you also need a correctly setup client which will talk to it. Samples are hotspots, vpn protocols such as openvpn, strongswan or softether and some other router OS.

FreeRadius + MySql on Ubuntu

From this tutorial we will try to install a freeradius server on Ubuntu 14.04 64bit distro with mysql support.


Prerequisites

This tutorial requires will require the following ingredients to setup freeradius+mysql:
- an Ubuntu 14 64bit server
- Root Access to the server
- An SSH client (You can download putty or bitvise depends on your operating system and liking)

We shall be making a basic freeradius setup with a mysql database for storing user credentials and other information.


Installing Freeradius

Let us first update our distro so we can be sure we will be able to install the required applications

sudo apt-get update

Then will will install freeradius, just do.

sudo apt-get install freeradius freeradius-mysql

'freeradius-mysql' is a required freeradius module so we can communicate with the mysql server. The Mysql server will store the needed data so freeradius can authenticate the client machine.

Next, we will need to edit the default file to change the AAA mechanism of freeradius from file system to sql server.

nano /etc/freeradius/sites-enabled/default

Then we will have to comment out every line where it says 'file' and un-comment the lines which says 'sql'. Below is the summary of what should be the final result of what we will do. Please do not remove any lines in the default configuration, just comment the 'file' and un-comment the 'sql' lines.

authorize {
#   files
    sql
}
authenticate {
}
preacct {
#   files
}
accounting {
    sql
}
session {
    sql
}
post-auth {
    sql
    Post-Auth-Type REJECT {
    # log failed authentications in SQL, too.
    sql
    attr_filter.access_reject
    }
}

Save the file and exit.

Next, we will go to the main radius configuration file. We will enable the mysql module so we can use it later on.

nano /etc/freeradius/radiusd.conf

We will un-comment the line:

$INCLUDE sql.conf

You can exit after saving the configuration file.

After all the configurations are done we will enter our mysql server access credentials into radius. If you have your credentials ready you can use it, but if not, I will give a sample credentials for now.

nano /etc/freeradius/sql.conf

edit the file and supply your mysql credentials.

sql {
    database = "mysql"
    server = "localhost"
    login = "sampleuser"
    password = "samplepassword"
    radius_db = "radius"
    #uncomment read_groups
    read_groups = yes
    #uncomment readclients
    readclients = yes
}

Save it and exit.

We will come back to Freeradius later on. For now we will install mysql.


Installing & Configuring MySql

To install MySql we need to execute the command.

sudo apt-get install mysql-server

enter and repeat the password for the new mysql root user.
MySql Root Password

Enter Mysql root and create the radius database and user.

CREATE DATABASE radius;
CREATE USER 'sampleuser'@'localhost' IDENTIFIED BY 'samplepassword';
GRANT ALL PRIVILEGES ON *.* TO 'sampleuser'@'localhost';
FLUSH PRIVILEGES;

Then exit Mysql root to command line.

Next, we will have to import the sql file for freeradius into the 'radius' database. The schema.sql and nas.sql file is located at '/etc/freeradius/sql/mysql' folder.

mysql -u root -p radius < /etc/freeradius/sql/mysql/schema.sql;
exit;
mysql -u root -p radius < /etc/freeradius/sql/mysql/nas.sql;

Populating Radius Database

It is important that we enter the correct freeradius values into the radius database for Freeradius to correctly read it, otherwise, Freeradius will throw an error during operation. The informations we want to enter are for the following:

  1. Freeradius client ip and secret (the secret should be unique per freeradius client and can be alphanumeric character most recommended to be more than 10).
  2. Users name and password
  3. Freeradius check values for groups and indvidual users.
  4. Freeradius reply values for groups and individual users.

First, we will enter the freeradius client information into the nas table. Enter mysql root and execute the command.

INSERT INTO  nas VALUES (NULL ,  '0.0.0.0/0,  'myNAS', 'other', NULL ,  'mysecret', NULL , NULL ,  'RADIUS Client');

Then we will enter user information into the radcheck table.

INSERT INTO radcheck (username, attribute, op, value) VALUES ('thisuser', 'User-Password', ':=', 'thispassword');

Then we need to assign the user a group.

INSERT INTO radusergroup (username, groupname, priority) VALUES ('thisuser', 'thisgroup', '1');

After that we assign the reply properties for the group in the radgroupreply table.

INSERT INTO radgroupreply (groupname, attribute, op, value) VALUES ('thisgroup', 'Service-Type', ':=', 'Framed-User'), ('thisgroup', 'Framed-Protocol', ':=', 'PPP'), ('thisgroup', 'Framed-Compression', ':=', 'Van-Jacobsen-TCP-IP');

All is done for now. Exit Mysql root and go to the next step.


Testing Freeradius+Mysql installation

To test the setup we will have to run freeradius in debug mode. We will execute the below command.

service freeradius stop
freeradius -X

To check if freeradius is running, you should see the following lines in your screen.

 ... adding new socket proxy address * port 55302
Listening on authentication address * port 1812
Listening on accounting address * port 1813
Listening on authentication address 127.0.0.1 port 18120 as server inner-tunnel
Listening on proxy address * port 1814
Ready to process requests.

Download NTRAdPing[1] (Windows only) and enter the following information.

  1. Your Freeradius server ip
  2. Your username and password (you entered into radcheck earlier)
  3. Your secret (you entered into nas table earlier)
  4. Port is standard 1812 for authentication (do not change it)

If your test is successful you will see the Access-Accept line in NTRadPing.
ntradping successful test


Simultaneous-Use on Freeradius

It is used to control simultaneous logins of users in a particular service by the number entered in the database. It is done by reading the accounting table for the username currently logged-in. if none is found the connecting user is allowed to authentication but if the limit is reached to connecting user is rejected.

To enable simultaneous-use limit we un-comment the line in '/etc/freeradius/sql/mysql/dialup.conf'.

simul_count_query = "SELECT COUNT(*) \  
                     FROM ${acct_table1} \  
                     WHERE username = '%{SQL-User-Name}' \  
                     AND acctstoptime IS NULL"  

then entering the required value in the radgroupcheck table.

INSERT INTO radgroupcheck (groupname, attribute, op, value) VALUES ('thisgroup', 'Simultaneous-Use', ':=', '3');

Which means that the users in the group 'thisgroup' is allowed up to three simultaneous logins before being rejected.


Whewww!! Such a long tutorial but its all worth it. I hope you have learned a lot reading my guides and I am sure that you will be reading this a lot more than before.

[1]: https://www.novell.com/coolsolutions/tools/14377.html

comments (12)

  • JaredRampartap

    - 6 years ago

    Hey, when I run "mysql -u root -p radius &lt; /etc/freeradius/sql/mysql/schema.sql; " I get "permission denied". Please tell me what I have to do to fix this. Thanks in advance for your help.

    • Mark

      - 6 years ago

      remove the "&lt;" and run it again. I think there is an issue with the webpage showing special characters.

      • JAREDRAMPARTAP

        - 6 years ago

        I removed it and replaced it with a "<" but it still said permission denied. I enter the below: mysql -uroot -p radius < /etc/freeradius/sql/mysql/schema.sql and get this: /etc/freeradius/sql/mysql/schema.sql: Permission denied

  • Mark

    - 6 years ago

    remove the "&lt;" and run it again. I think there is an issue with the webpage showing special characters.

  • AYSMAN

    - 6 years ago

    Hi,

    Can i use this tutorial if I want to use freeradius as AAA for pfsense captive portal?

  • rizky

    - 6 years ago

    My simultaneous-Use cant running, when i login for 3 devices and i limit on mysql 1 device. Please help

  • Kat K

    - 6 years ago

    I am not able to get NTRadPing to work.

    I get this error: Ignoring request to authentication address * port 1812 from unknown client 157.48.21.217 port 61031

    I seek you help. Appreciate your time and for the article. I must be doing something very stupid.

  • iMacCustomer

    - 5 years ago

    That's was a great article, I agree with all that you stated and if you need help regarding any issues with iMac Customer Service Number then you can contact us. https://babasupport.org/apple/imac-customer-service-number/345

  • naveed

    - 5 years ago

    Thanks for the great tutorial. I've found a little mistake which is not little for the one who don't know much in depth. please note that there is a mistake in the following query:

    INSERT INTO nas VALUES (NULL , '0.0.0.0/0, 'myNAS', 'other', NULL , 'mysecret', NULL , NULL , 'RADIUS Client');

    in '0.0.0.0/0, ending single quote ( ' ) is missing.

    Correct query is following:

    INSERT INTO nas VALUES (NULL , '0.0.0.0/0', 'myNAS', 'other', NULL , 'mysecret', NULL , NULL , 'RADIUS Client');

    thansk!

  • Saint

    - 5 years ago

    Bonjour, je suis debutant dans les commandes linux. Je dois réaliser un projet sur l'authentification radius. Dans la recherche de tuto, je suis tombé sur le votre qui me parait tres clair sauf que je suis booter à un problème APRES L'INSTALLATION DE MYSQL-SERVER (dans le tuto) COMMENT PUIS-JE ME CONNECTER A MYSQL? j'ai tenté : SU MYSQL ensuite MOT DE PASSE et j'obtiens ECHEC D'AUTHENTIFICATION. je souhaite continuer avec l'étape de création de la base de données. S'IL VOUS PLAIT AIDER MOI.

  • vbibiv

    - 3 years ago

    Muchas gracias!!!

    Estuve varios dias buscando mi error con la conneccion de mysql et freeradius, porque no queria conectarse con el NAS et viendo tu tutorial he logrado comprendrer y resolver mi error. Merci beaucoup :)

  • VANHUSSEN

    - 3 years ago

    Sir, please make new tutorial in youtube with last version on pfsense. Thank you