Freeradius and MySQL

This guide is a continuation of the Freeradius EAP/PEAP guide, which can be found here. http://linuxlasse.net/linux/howtos/Freeradius_EAP-PEAP_(TLS)

installation

Install the mysql module to freeradius.

apt-get install freeradius-mysql

Configuration

When installting the mysql module/package, it will install some templates which we can use to create the database schemas etc.

Create the database

Create the database. Note you can change the password, and you should.

mysql -u root -p
mysql> CREATE DATABASE radius;
mysql> GRANT ALL ON radius.* TO radius@localhost IDENTIFIED BY "radpass";

Next add the schema to the radius database we just created.

mysql -uroot -p radius < /etc/freeradius/sql/mysql/schema.sql

enable MySQL

Now we must enable SQL on freeradius, by uncommenting $INCLUDE sql.conf"
The line should be around linenumber 700.

/etc/freeradius/radiusd.conf
$INCLUDE sql.conf

We must also tell to look in the MySQL database, by removing the comment for sql in /etc/freeradius/sites-enabled/default.
Should be around line 177.

/etc/freeradius/sites-available/default
sql

Now enable tunnel SQL lookups. If this is not done, "normal" radtest will work, but mschap will fail.
Should be around line 177.

/etc/freeradius/sites-available/inner-tunnel
sql

Configuring MySQL

Now me must configure the server to use MySQL.
We do this by editing the file sql.conf. Make sure that the database is set to MySQL, and that the username and password is correct.

/etc/freeradius/sql.conf
database = "mysql"
server = "localhost"
login = "radius"
password = "radpass"
radius_db = "radius"

Restart freeradius, after the changes

/etc/init.d/freeradius restart

Creating users

Users are added to the radcheck table.

mysql>insert into radcheck (username,attribute,op,value) VALUES ('mysqluser1','Cleartext-Password',':=','testpass');

Asuming the user mysqluser1 is belonging to the group dynamic. (other option is static, for static IP-addresses).

INSERT INTO radusergroup VALUES ('mysqluser1','dynamic',1);

For each group, add some default attributes to radgroupreply, in this example the dynamic group.

mysql>INSERT INTO radgroupreply (groupname, attribute, op, value) VALUES ('dynamic','Framed-Compression',\
':=','Van-Jacobsen-TCP-IP'),('dynamic','Framed-Protocol',':=','PPP'),('dynamic','Service-Type',':=',\
'Framed-User'),('dynamic','Acct-Interim-Interval','=','60');

Testing

Now, it should be possible to login with the newly created user. If not start up freeradius in debug mode "freeradius -X"
Testing the account.

radtest mysqluser1 "testpass" localhost 1812 testing123
Sending Access-Request of id 179 to 127.0.0.1 port 1812
	User-Name = "mysqluser1"
	User-Password = "testpass"
	NAS-IP-Address = 192.168.10.2
	NAS-Port = 1812
	Message-Authenticator = 0x00000000000000000000000000000000
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=179, length=20

Testing the account for mschap.
If this fails, it would not be possible to authenticate WLAN users!

radtest -t mschap mysqluser1 testpass 127.0.0.1:18120 0 testing123
Sending Access-Request of id 4 to 127.0.0.1 port 18120
	User-Name = "mysqluser1"
	NAS-IP-Address = 192.168.10.2
	NAS-Port = 0
	Message-Authenticator = 0x00000000000000000000000000000000
	MS-CHAP-Challenge = 0x8dc331c91dee6a48
	MS-CHAP-Response = 0x00010000000000000000000000000000000000000000000000003a437b420539bbc4e6f52e1cb59f1f72d4d9e34784999e68
rad_recv: Access-Accept packet from host 127.0.0.1 port 18120, id=4, length=84
	MS-CHAP-MPPE-Keys = 0x3ae6ccce2a2a253f8eff585d2a73a2906dc517ed014bc58a0000000000000000
	MS-MPPE-Encryption-Policy = 0x00000001
	MS-MPPE-Encryption-Types = 0x00000006