From Debuntu

This article will describe how to set a Freeradius server to authenticate/authorize and perform accounting against a MySQL database.

On top of this, it will also describe how to store the password as a MD5 hash instead of clear text password:

This was done on Debian Lenny

Contents 1 Requirements 2 Setting up Freeradius 2.1 Without MySQL 2.2 Setting MySQL 2.2.1 MySQL database 2.2.2 FreeRadius configuration 2.2.3 Testing 2.3 Moving to MD5 password

Requirements

You need to install the following packages:

# apt-get install mysql-server freeradius-mysql

Setting up Freeradius

Without MySQL

Let first start setting up free radius to do authentication using a configuration stored on the filesystem, then we will move to MySQL

We are going to create a user called chantra with password chantrapass.

Go and edit /etc/freeradius/users and add:

chantra Cleartext-Password := "chantrapass"
  Service-Type = Framed-User,
  Framed-Protocol = PPP,
  Framed-Compression = Van-Jacobsen-TCP-IP

Then, test it works with:

$ radtest chantra chantrapass localhost 10 testing123
Sending Access-Request of id 5 to 127.0.0.1 port 1812
	User-Name = "chantra"
	User-Password = "chantrapass"
	NAS-IP-Address = 127.0.1.1
	NAS-Port = 10
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=5, length=38
	Service-Type = Framed-User
	Framed-Protocol = PPP
	Framed-Compression = Van-Jacobson-TCP-IP

Setting MySQL

MySQL database

First you needs to create the database, here we are going to call it radsql, user radsql will be able to access it from localhost using password radsql. Log in your MySQL server as root and type this at the prompt:

mysql> CREATE DATABASE radsql;
mysql> GRANT ALL PRIVILEGES ON radsql.* TO radsql@localhost IDENTIFIED BY 'radsql';

Then, import the default MySQL database structure from freeradius-mysql package:

# mysql -u root -p radsql < /etc/freeradius/sql/mysql/schema.sql

Finally create your first user. Here I will assume that user chantra belongs to group dynamic. So, let set this up in MySQL:

INSERT INTO radcheck (username, attribute, op, value) VALUES ('chantra','Cleartext-Password',':=','chantrapass');
INSERT INTO radusergroup VALUES ('chantra','dynamic',1);
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');

FreeRadius configuration

and now, change /etc/freeradius/sites-enabled/default so it looks like:

authorize {
  sql
}
authenticate {
}
preacct {
}
accounting {
  sql
}
session {
  sql
}
post-auth {
  sql
}
pre-proxy {
}
post-proxy {
}

In /etc/freeradius/sql.conf change these values to reflect your settings:

  server = "localhost"
  login = "radsql"
  password = "radsql"

  # Database table configuration for everything except Oracle
  radius_db = "radsql"

In /etc/freeradius/radiusd.conf uncomment:

    $INCLUDE sql.conf

also, you can disable proxy by setting proxy_requests to no.

Testing

Restart freeradius or run it from the command line in debug mode:

# freeradius  -X

and test it with:

radtest chantra chantrapass localhost 10 testing123
Sending Access-Request of id 200 to 127.0.0.1 port 1812
	User-Name = "chantra"
	User-Password = "chantrapass"
	NAS-IP-Address = 127.0.1.1
	NAS-Port = 10
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=200, length=44
	Framed-Compression = Van-Jacobson-TCP-IP
	Framed-Protocol = PPP
	Service-Type = Framed-User
	Acct-Interim-Interval = 60

Moving to MD5 password

Ok, now, let make the password be hashed through MD5 so we dont keep clear-text password in the database.

Log into MySQL into radsql database and run:

UPDATE radcheck SET attribute='MD5-Password', value=MD5('chantrapass') WHERE username='chantra';
INSERT INTO radgroupcheck VALUES (Null,'dynamic','Auth-Type',':=','PAP');

In /etc/freeradius/sites-enabled/default uncomment under the section authenticate:

  Auth-Type PAP {
    pap
  }

Restart freeradius and run the previous test:

# radtest chantra chantrapass localhost 10 testing123
Sending Access-Request of id 172 to 127.0.0.1 port 1812
	User-Name = "chantra"
	User-Password = "chantrapass"
	NAS-IP-Address = 127.0.1.1
	NAS-Port = 10
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=172, length=44
	Framed-Compression = Van-Jacobson-TCP-IP
	Framed-Protocol = PPP
	Service-Type = Framed-User
	Acct-Interim-Interval = 60