SQL HOWTO
From FreeRADIUS Wiki
FreeRADIUS Server works out of the box with a large list of SQL servers, but unfortunately there are a number of configuration guides available on the internet that are either for very old versions of FreeRADIUS Server, or are wrong, or both. This guide is an attempt to correct some of the misinformation.
Contents |
System
These instructions are for FreeRADIUS Server version 1.1.7 and have been tested on an openSUSE 10.2 installation.
Also, I tested this instructions on CentOS 5.0 and CentOS 5.1. CentOS 5.x came with FreeRadius 1.1.3 and you'll need to install freeradius-mysql package to use SQL function. (FR 1.1.7 or above is recommended)
Before You Start
Before starting with FreeRADIUS, please make sure your server is up and configured on your network, that you have your SQL server of choice (MySQL, Postgresql etc) installed and running, and that your NAS is configured to send RADIUS requests to your RADIUS server.
We have some sample configs for Cisco NAS available here.
Getting Started
Firstly, you need to install FreeRADIUS Server on your system. As the premiere open source RADIUS suite it is included as a standard package with numerous Operating Systems and has binary packages for many others. Installation is most easily accomplished by installing a binary package (rpm, deb), but if you have a less well known operating system you may need to build your own.
Basic Configuration
It's best to start with a simple config using the standard text files, if only to test that FreeRADIUS is correctly installed and works.
- Edit /etc/raddb/clients.conf and enter the details of your NAS unit(s). There are examples here, so it should be easy. There should already be a 'localhost' NAS preconfigured here for testing purposes (i.e. so you can use radtest).
- Edit /etc/raddb/users and create an example user account. The file is commented on how to do this.
- Edit /etc/raddb/radiusd.conf and change as needed. You may wish to change the default port to run on 1645 (old port) if you are replacing a legacy RADIUS server.
At this point you should be able to manually fire up /usr/sbin/radiusd. You should do this with the debug turned on so you can see what happens:
/usr/sbin/radiusd -X
Lots of stuff will scroll to the screen, and it should tell you it's ready to accept requests. If you get an error, READ THE DEBUG, then check the docs, check the above and try again.
You should now be able to use FreeRadius. You can use radtest to test an account from the command line:
radtest username password servername port secret
So, if your example user is 'fred' with password 'wilma', your server is called 'radius.domain.com', is using port 1645, and you put localhost (or your localhost's IP) in clients.conf with a secret of 'mysecret', you should use:
radtest fred wilma radius.domain.com 1645 mysecret
And you should get back something like:
Sending Access-Request of id 226 to 127.0.0.1:1645
User-Name = 'fred'
User-Password = '\304\2323\326B\017\376\322?K\332\350Z;}'
NAS-IP-Address = radius.domain.com
NAS-Port = 1645
rad_recv : Access-Accept packet from host 127.0.0.1:1645,id=226, length=56
Framed-IP-Address = 80.84.161.1
Framed-Protocol = PPP
Service-Type = Framed-User
Framed-Compression = Van-Jacobson-TCP-IP
Framed-IP- Netmask = 255.255.255.255
You should get an 'Access Accept' response. If you don't, do not pass Go, do not collect £200. Go back and check everything. Read the docs, READ THE DEBUG!!
If you have a Windows PC handy you may also wish to use NTradPing (downloadable from MasterSoft) to send test packets instead of radtest. If you do this, or test from any other machine, remember your PC (or other machine) needs to be in your NAS list in clients.conf too! OK, so at this point you should have text-file authentication working in FreeRadius...
Setting up the RADIUS database
First, you should create a new empty 'radius' database in SQL and a database user with permissions to that database. You could of course call the database and the user anything you like but you probably should stick with 'radius' for both to keep things simple.
Next up, you need to create the schema for your database. There is an SQL script file for each SQL type in doc/examples/ in your operating system's doc directory (or where you untar'd FreeRADIUS). On SUSE this is under /usr/share/doc/packages/freeradius/
Create MySQL Database
mysql -uroot -p CREATE DATABASE radius; GRANT ALL ON radius.* TO radius@localhost IDENTIFIED BY "radpass"; exit
Note: use a more secure password that "radpass" in the above example
cd /usr/share/doc/packages/freeradius/doc/examples/ mysql -uroot -p radius < mysql.sql
Create PostgreSQL Database
su - postgres createuser radius --no-superuser --no-createdb --no-createrole -P createdb radius --owner=radius exit
Note: choose a secure password when prompted for one by the createuser command.
cd /usr/share/doc/packages/freeradius/doc/examples/ psql -U radius radius < postgresql.sql
Configuring FreeRadius to use SQL
Edit either /etc/raddb/sql.conf or /etc/raddb/postgresql.conf and enter the server, name and password details to connect to your SQL server and the RADIUS database. The database and table names should be left at the defaults if you used the default schema. For testing/debug purposes, switch on sqltrace if you wish - FreeRadius will dump all SQL commands to the debug output with this on.
In /etc/raddb/radiusd.conf ensure that the line saying:
$INCLUDE sql.conf
is uncommented.
You will also need to edit /etc/raddb/sql.conf, and direct it to the appropriate database (PostgreSQL, MySQL, etc.), by edit the line:
database = "mysql"
with the name of the database that you are using.
If you're stripping all realm names (i.e. you want user joe@domain.com to authenticate as just 'joe'), then in file raddb/sql/database/dialup.conf , under the 'query config: username' section, you MAY need to adjust the line(s) referring to sql_user_name. I needed to do this originally because we want to dump all realms, but you probably won't need to do this with the latest FreeRadius. For example, in our case I needed to uncomment the line:
sql_user_name = '%{Stripped-User-Name}'
...and comment out the following line referring to just User-Name. If you want to see what's happening here, switch on all the logging options in radiusd.conf and run radiusd in debug mode (-X) to see what's happening : you'll see " user@domain" being passed to SQL when using User-Name, but just "user" when using Stripped-User-Name. Using the latter, realms worked for me (basically, I strip everything, as all user names are unique on the server anyway). Of course, set all your other SQL options as needed (database login details, etc)
Edit /etc/raddb/sites-available/default and uncomment the line containing 'sql' in the authorize{} section. The best place to put it is just after the 'files' entry. Indeed, if you'll just be using SQL, and not falling back to text files, you could comment out or delete the 'files' entry altogether.
Also uncomment the line saying 'sql' in the accounting{} section to tell FreeRADIUS to store accounting records in SQL as well.
Optionally add or uncomment 'sql' to the session{} section if you want to do Simultaneous-Use detection.
Optionally add or uncomment 'sql' to the post-auth{} section if you want to log all Authentication attempts to SQL.
You should not change/delete any other lines in the config file without reading and understanding the comments!
Your radiusd.conf should then look something like this:
authorise {
preprocess
chap
mschap
suffix
eap
# We leave "files" enabled to allow creation of test users in /etc/raddb/users
files
sql
pap
}
accounting {
# We leave "detail" enabled to _additionally_ log accounting to /var/log/radius/radacct
detail
sql
}
Populating SQL
You should now created some dummy data in the database to test against. It goes something like this:
- In usergroup, put entries matching a user account name to a group name.
- In radcheck, put an entry for each user account name with a 'Cleartext-Password' attribute with a value of their password.
- In radreply, create entries for each user-specific radius reply attribute against their username
- In radgroupreply, create attributes to be returned to all group members
Here's a dump of some example 'radius' tables from a MySQL database (With PostgreSQL the formating will look slightly different but it uses exactly the same content).
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 usergroup;
+----+---------------+-----------+
| id | UserName | GroupName |
+----+---------------+-----------+
| 1 | fredf | dynamic |
| 2 | barney | static |
| 2 | dialrouter | netdial |
+----+---------------+-----------+
3 rows in set (0.01 sec)
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 set (0.01 sec)
mysql> select * from radreply;
+----+------------+-------------------+---------------------------------+------+
| id | UserName | Attribute | Value | Op |
+----+------------+-------------------+---------------------------------+------+
| 1 | barney | Framed-IP-Address | 1.2.3.4 | := |
| 2 | dialrouter | Framed-IP-Address | 2.3.4.1 | := |
| 3 | dialrouter | Framed-IP-Netmask | 255.255.255.255 | := |
| 4 | dialrouter | Framed-Routing | Broadcast-Listen | := |
| 5 | dialrouter | Framed-Route | 2.3.4.0 255.255.255.248 | := |
| 6 | dialrouter | Idle-Timeout | 900 | := |
+----+------------+-------------------+---------------------------------+------+
6 rows in 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 set (0.01 sec)
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.
If you're stripping all domain name elements from usernames via realms, remember NOT to include the domain name elements in the usernames you put in the SQL tables - they should get stripped BEFORE the database is checked, so name@domain will NEVER match if you're realm stripping (assuming you follow point 2 above) – you should just have 'name' as a user in the database. Once it's working without, and if you want more complex realm handling, go back to work out not stripping (and keeping name@domain in the db) if you really want to.
Test
Fire up radiusd again in debug mode (radiusd -X). The debug output should show it connecting to the SQL database. Use radtest (or NTradPing) to test again - the user should authenticate and the debug output should show FreeRADIUS talking to SQL.
Congratulations. You're done!
Additional Snippets
- To use encrypted passwords in radcheck use the attribute 'Crypt-Password', instead of 'Cleartext-Password', and just put the encrypted password in the value field. ( i.e. UNIX crypt'd password).
- To get NTradPing to send test accounting (e.g. stop) packets it needs arguments, namely acct-session-time. Put something like 'Acct-Session-Time=99999' into the 'Additional RADIUS Attributes' box when sending stops.
- If you have a Cisco nas, set the cisco-vsa-hack
- Running a backup FreeRADIUS server and need to replicate the RADIUS database to it? I followed Colin Bloch's basic instructions at http://www.ls-l.net/mysql/ and got replication setup between two MySQL servers. Real easy. Read the MySQL docs on replication for more details.
On the subject of backup servers. If you want to run TWO MySQL servers and have FreeRadius fall over between them, you'll need to do something like this: duplicate your sql.conf and edit the second copy to reflect connecting to your backup server ; then name the files something like sql1.conf and sql2.conf ; in radiusd.conf change and duplicate the include line for sql.conf to include sql1.conf and sql2.conf instead ; in the 'authorize' section of radiusd.conf change the 'sql' entry to a 'group' one, like this:
group {
sql1 {
fail = 1
notfound = return
noop = 2
ok = return
updated = 3
reject = return
userlock = 4
invalid = 5
handled = 6
}
sql2 {
fail = 1
notfound = return
noop = 2
ok = return
updated = 3
reject = return
userlock = 4
invalid = 5
handled = 6
}
}
Note that if FreeRadius fails over to the second MySQL server and tries to update the accounting table (radacct), nasty things might possibly happen to your replication setup and database integrity as the first MySQL server won't have got the updates...