Note the from: www.vpsserver.com/community/t…

From this tutorial we will try to install distro Server on Ubuntu 14.04 64bit 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
Copy the code

Then will will install freeradius, just do.

sudo apt-get install freeradius freeradius-mysql
Copy the code

‘freeradius-mysql’ is a required freeradius module so we can communicate with the mysql server.

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
Copy the code

Then we will have to comment out every line where it says ‘file’ and un-comment the lines which says ‘sql’.

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
    }
}
Copy the code

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
Copy the code

We will un-comment the line:

< v3 $INCLUDE sql.conf
v3 : $INCLUDE mods-enabled/sql
Copy the code

we will enter our mysql server access credentials into radius.

nano /etc/freeradius/sql.conf
Copy the code

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
	read_clients = yes
}
Copy the code

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;
Copy the code

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;
mysql -u root -p radius < /etc/freeradius/sql/mysql/nas.sql;
Copy the code

Both files can be found on Github via Google.

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:

Freeradius client ip and secret 
Users name and password
Freeradius check values for groups and indvidual users.
Freeradius reply values for groups and individual users.
Copy the code

First, we will enter the freeradius client information into the nas table.

INSERT INTO NAS VALUES (NULL, '0.0.0.0/0, 'myNAS', 'other', NULL, 'mysecret', NULL, NULL, 'RADIUS Client');Copy the code

Then we will enter user information into the radcheck table.

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

Then we need to assign the user a group.

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

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');
Copy the code

All is done for now.

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
Copy the code

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

. Ready to process requests.Copy the code

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

Your Freeradius server ip
Your username and password (you entered into radcheck earlier)
Your secret (you entered into nas table earlier)
Port is standard 1812 for authentication (do not change it)
Copy the code

If your test is successful you will see the

Access-Accept
Copy the code

##SIMULTANEOUS-USE ON FREERADIUS

It is used to control simultaneous logins of users in a particular service by the number entered in the database. 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"  
Copy the code

then entering the required value in the radgroupcheck table.

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

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

Automated script

https://gist.github.com/xjdrew/04d839479d8a6a490153
Copy the code

mysql sample data

From: wiki.freeradius.org/guide/SQL-H…

You should now created some dummy data in the database to test against. It goes something like this:

  1. In usergroup, put entries matching a user account name to a group name.
  2. In radcheck, put an entry for each user account name with a ‘Cleartext-Password’ attribute with a value of their password.
  3. In radreply, create entries for each user-specific radius reply attribute against their username
  4. In radgroupreply, create attributes to be returned to all group members

Here’s a dump of some example ‘radius’ tables from a MySQL database.This example includes three users, one with a dynamically assigned IP by the NAS (fredf), one assigned a static IP (barney), and one representing a dial-up routed connection (dialrouter):

mysql> select * from radcheck; +----+----------------+--------------------+------------------+------+ | id | UserName | Attribute | Value | Op | +----+----------------+--------------------+------------------+------+ | 1 | fredf | Cleartext-Password | wilma | := | |  2 | barney | Cleartext-Password | betty | := | | 2 | dialrouter | Cleartext-Password | dialup | := | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 3 rows in the set (0.01 SEC) mysql > select * from radreply; +----+------------+-------------------+---------------------------------+------+ | id | UserName | Attribute | Value | Op | +----+------------+-------------------+---------------------------------+------+ | 1 | barney | Framed-IP-Address | | : 1. 2. = | | 2 | dialrouter | usual - IP Address - | 2.3.4.1 | : = | | 3 | dialrouter | usual - IP - Netmask | 4:255.255.255.255 | = | | | dialrouter | usual - Routing | Broadcast - Listen | : = | | | 5 dialrouter | usual - the Route | 2.3.4.0 255.255.255.248 | : = | | | 6 dialrouter | Idle Timeout - | 900 | : = | + - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 6 rows in the set (0.01 SEC) mysql > select * from radgroupreply; +----+-----------+--------------------+---------------------+------+ | id | GroupName | Attribute | Value | Op | +----+-----------+--------------------+---------------------+------+ | 34 | dynamic | Framed-Compression | Van-Jacobsen-TCP-IP | := | | 33 | dynamic | Framed-Protocol | PPP | := | | 32 | dynamic | Service-Type | Framed-User | := | | 35 | dynamic | Framed-MTU | 1500 | := | | 37 | static | Framed-Protocol | PPP | := | | 38 | static | Service-Type  | Framed-User | := | | 39 | static | Framed-Compression | Van-Jacobsen-TCP-IP | := | | 41 | netdial | Service-Type | Framed-User | := | | 42 | netdial | Framed-Protocol | PPP | := | + - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + 12 rows in the set (0.01 SEC)Copy the code

In this example, ‘barney’ (who is a single user dialup) only needs an attribute for IP address in radreply so he gets his static IP – he does not need any other attributes here as all the others get picked up from the ‘static’ group entries in radgroupreply.

‘fred’ needs no entries in radreply as he is dynamically assigned an IP via the NAS – so he’ll just get the ‘dynamic’ group entries from radgroupreply ONLY.

‘dialrouter’ is a dial-up router, so as well as needing a static IP it needs route and mask attributes (etc) to be returned. Hence the additional entries.

‘dialrouter’ also has an idle-timeout attribute so the router gets kicked if it’s not doing anything – you could add this for other users too if you wanted to. Of course, if you feel like or need to add any other attributes, that’s kind of up to you!

Note the operator (‘op’) values used in the various tables. The password check attribute MUST use :=. Most return attributes should have a := operator, although if you’re returning multiple attributes of the same type (e.g. multiple Cisco- AVpair’s) you should use the += operator instead otherwise only the first one will be returned. Read the docs for more details on operators.