Installing and configuring FreeRADIUS with MySQL authentication

Here's a *near* step-by-step guide to setup FreeRADIUS with MySQL backed authentication done on Ubuntu 9.10 (Karmic).  Start by installing the necessary deb files:

# apt-get install freeradius freeradius-mysql

Add the following lines to the /etc/freeradius/users file: 

abc     Cleartext-Password := "123"

Stop freeradius service just in case:

/etc/init.d/freeradius stop

Now test using the radtest client:

# radtest abc 123 localhost 1812 testing123
Sending Access-Request of id 149 to 127.0.0.1 port 1812
        User-Name = "abc"
        User-Password = "123"
        NAS-IP-Address = 208.67.219.132
        NAS-Port = 1812
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=149, length=20

Ensure that the response is "Access-Accept".  The following should be displayed at the server console:

rad_recv: Access-Request packet from host 127.0.0.1 port 33425, id=149, length=55
        User-Name = "abc"
        User-Password = "123"
        NAS-IP-Address = 208.67.219.132
        NAS-Port = 1812
+- entering group authorize {...}
++[preprocess] returns ok
++[chap] returns noop
++[mschap] returns noop
[suffix] No '@' in User-Name = "abc", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
++[unix] returns notfound
[files] users: Matched entry abc at line 61
++[files] returns ok
++[expiration] returns noop
++[logintime] returns noop
++[pap] returns updated
Found Auth-Type = PAP
+- entering group PAP {...}
[pap] login attempt with password "123"
[pap] Using clear text password "123"
[pap] User authenticated successfully
++[pap] returns ok
+- entering group post-auth {...}
++[exec] returns noop
Sending Access-Accept of id 149 to 127.0.0.1 port 33425
Finished request 2.
Going to the next request
Waking up in 4.9 seconds.
Cleaning up request 2 ID 149 with timestamp +3641
Ready to process requests.

Now on to MySQL setup.  First, create a database where FreeRADIUS will store its data.  We'll call it radius:

create database radius

Import the MySQL schema from /etc/freeradius/sql/mysql/schema.sql:

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

You should have 7 tables as shown below:

radacct
radcheck
radgroupcheck
radgroupreply
radpostauth
radreply
radusergroup

Edit the file /etc/freeradius/sql.conf and change the following parameters to suite your environment:

server = "localhost"
login = "root"
password = "password"
radius_db = "radius"

Enable the SQL configuration in /etc/freeradius/radiusd.conf by uncommenting the following line:

$INCLUDE sql.conf

Enable SQL configuration in the default enabled site /etc/freeradius/sites-available/default:

authorize {
    ...
    sql
    ...
}

accounting {
    ...
    sql
    ...
}

session {
    ...
    sql
    ...
}

post-auth {
    ...
    sql
    ...
}

Insert the following record into radcheck table:

INSERT INTO `radius`.`radcheck` (`id` ,`username` ,`attribute` ,`op` ,`value`)
VALUES (NULL , 'test', 'MD5-Password', ':=', MD5( '1234' )

Note: More info on the 'op' value can be obtained by reading the 'unlang' man page.  Snippet shown below:

Operators
      The operator used to assign the value of the attribute may be one of the following, with the given meaning.

      =      Add the attribute to the list, if and only if an attribute of the same name is already present in that list.

      :=     Add  the attribute to the list.  If any attribute of the same name is already present in that list, its value is replaced with the value of the current
             attribute.

      +=     Add the attribute to the tail of the list, even if attributes of the same name are already present in the list.

Enforcement and Filtering Operators
      The following operators may also be used in addition to the ones listed above.  Their function is to perform enforcement or filtering on attributes in a list.

      -=     Remove all matching attributes from the list.  Both the attribute name and value have to match in order for the attribute to be removed from the list.

      ==     Remove all non-matching attributes from the list.  Both the attribute name and value have to match in order for the attribute to remain in the list.

             Note that this operator is very different than the '=' operator listed above!

      <=     Enforce that the integer value of the attribute is less than or equal to the value given here.  If there is no attribute of the same name in the  list,
             the  attribute  is  added  with  the  given value, is with "+=".  If an attribute in the list exists, and has value less than given here, it's value is
             unchanged.  If an attribute in the list exists, and has a value greater than given here, then that value is replaced with the one given here.

             This operator is valid only for attributes of integer type.

      >=     Enforce that the integer value of the attribute is greater than or equal to the value given here.  If there is no attribute of the  same  name  in  the
             list, the attribute is added with the given value, is with "+=".  If an attribute in the list exists, and has value greater than given here, it's value
             is unchanged.  If an attribute in the list exists, and has value less than given here, then that value is replaced with the one given here.

             This operator is valid only for attributes of integer type.

Start freeradius in debug mode by using the command below:

# freeradius -X

In another terminal console, use radtest again to test the connection:

# radtest test 1234 localhost 1812 testing123
root@mike-laptop:/etc/freeradius/sites-available# radtest test 1234 localhost 1812 testing123
Sending Access-Request of id 147 to 127.0.0.1 port 1812
        User-Name = "test"
        User-Password = "1234"
        NAS-IP-Address = 208.67.219.132
        NAS-Port = 1812
rad_recv: Access-Accept packet from host 127.0.0.1 port 1812, id=147, length=20

Again, make sure the response contains "Access-Accept".  Following output should be shown at the server terminal:

++[preprocess] returns ok
++[chap] returns noop
++[mschap] returns noop
[suffix] No '@' in User-Name = "test", looking up realm NULL
[suffix] No such realm "NULL"
++[suffix] returns noop
[eap] No EAP-Message, not doing EAP
++[eap] returns noop
++[unix] returns notfound
        expand: %{User-Name} -> test
[sql] sql_set_user escaped user --> 'test'
rlm_sql (sql): Reserving sql socket id: 3
        expand: SELECT id, username, attribute, value, op           FROM radcheck           WHERE username = '%{SQL-User-Name}'           ORDER BY id -> SELECT id, username, attribute, value, op           FROM radcheck           WHERE username = 'test'           ORDER BY id
[sql] User found in radcheck table
        expand: SELECT id, username, attribute, value, op           FROM radreply           WHERE username = '%{SQL-User-Name}'           ORDER BY id -> SELECT id, username, attribute, value, op           FROM radreply           WHERE username = 'test'           ORDER BY id
        expand: SELECT groupname           FROM radusergroup           WHERE username = '%{SQL-User-Name}'           ORDER BY priority -> SELECT groupname           FROM radusergroup           WHERE username = 'test'           ORDER BY priority
rlm_sql (sql): Released sql socket id: 3
++[sql] returns ok
++[expiration] returns noop
++[logintime] returns noop
[pap] Normalizing MD5-Password from hex encoding
++[pap] returns updated
Found Auth-Type = PAP
+- entering group PAP {...}
[pap] login attempt with password "1234"
[pap] Using MD5 encryption.
[pap] User authenticated successfully
++[pap] returns ok
+- entering group post-auth {...}
        expand: %{User-Name} -> test
[sql] sql_set_user escaped user --> 'test'
        expand: %{User-Password} -> 1234
        expand: INSERT INTO radpostauth                           (username, pass, reply, authdate)                           VALUES (                           '%{User-Name}',                           '%{%{User-Password}:-%{Chap-Password}}',                           '%{reply:Packet-Type}', '%S') -> INSERT INTO radpostauth                           (username, pass, reply, authdate)                           VALUES (                           'test',                           '1234',                           'Access-Accept', '2010-01-10 12:55:21')
rlm_sql (sql) in sql_postauth: query is INSERT INTO radpostauth                           (username, pass, reply, authdate)                           VALUES (                           'test',                           '1234',                           'Access-Accept', '2010-01-10 12:55:21')

There should also be some records in the radpostauth table:

mysql> select * from radpostauth;
+----+----------+------+---------------+---------------------+
| id | username | pass | reply         | authdate            |
+----+----------+------+---------------+---------------------+
| 14 | test     | 1234 | Access-Accept | 2010-01-10 12:00:12 |
| 15 | test     | 1234 | Access-Accept | 2010-01-10 12:19:39 |
| 16 | test     | 1234 | Access-Accept | 2010-01-10 12:55:21 |
+----+----------+------+---------------+---------------------+
3 rows in set (0.00 sec)

As you can see, it's rather dangerous to have the clear text password shown in the table.  If you want to turn off that feature, comment out the 'sql' directive in the /etc/freeradius/sites-available/default file under 'post-auth' section.

Have fun!