Configuring FreeRadius with MYSQL on Ubuntu 8.10

Let’s first bring our server up to a place that we can manage it we will first install mysql phpmysql and vim. Make sure to write down the password you are prompted to enter for the root mysql user.

sudo apt-get install mysql-server phpmysql vim-full

We ae now going to create a mysql database by the name of fradius.
Login to the phpmyadmin interface via http://IP/phpmyadmin where IP is the ip of the appliance.
Once you login type fradius in the box and click on create new data base.
We will next create a radius user named radius. And allow full access to the fradius database. Replace the password with the password you want the radius user to use. By clicking on the sql button on the top left you will see a pop up you can past the following line into.

GRANT ALL ON fradius.* TO radius@localhost IDENTIFIED BY "password";

You can check to make sure that the user was created by clicking on the privliges link on the main colleum toward the bottom.
Now from the command line you will want to run the following command

sudo gunzip -d /usr/share/doc/freeradius/examples/mysql.sql.gz

We will no want to load the mysql schema located at
/etc/freeradius/sql/mysql/schema.sql
by running. Note make sure to replace password with your root password.
Note: change read rights to the file by running first.

sudo chmod 777 /etc/freeradius/sql/mysql/schema.sql

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

After this we will need to add a table. Goto the sql tab under phpmyadmin and fradius then issue the following command.

CREATE TABLE nas (id int(10) NOT NULL auto_increment, nasname varchar(128) NOT NULL, shortname varchar(32), type varchar(30) DEFAULT 'other', ports int(5), secret varchar(60) DEFAULT 'secret' NOT NULL, community varchar(50), description varchar(200) DEFAULT 'RADIUS Client', PRIMARY KEY (id), KEY nasname (nasname));

Now we want to configure the /etc/freeradius/sql.conf for the changes we made.

sudo vim /etc/freeradius/sql.conf

We want to make sure the password and login user are correct at line 36 or so. Example.
# Connection info:
server = "localhost"
login = "radius"
password = "fradius"

To turn the NAS management on, search for the following line and uncomment it to look like.
readclients = yes

Next save the file with shift+zz.

We now want to edit the file /etc/raddb/sites-enabled/default

sudo vim /etc/freeradius/sites-enabled/default

We want to uncomment the sql to the authorize{} section (Line 152).
We want to uncomment the sql to the accounting{} section (Line 342).
We want to uncomment the sql to the session{} section (Line 373).
We want to uncomment the sql to the post-auth{} section (Line 394).

Then type shift + ZZ to save.

Open the phpmyadmin interface again and select the radius data base on
the left hadn menu collomn. Then select the sql tab above the db listing. Enter the following command.

mysql> INSERT INTO radcheck (UserName, Attribute, Value) VALUES ('sqltest', 'Password', 'testpwd')

Fire up radius in debug mode:

freeradius -X

Go to another shell and run the test:

radtest sqltest testpwd localhost 1812 testing123

At this moment you should see a message containing something like ... Accept-Accept ... which is an indication that your user is getting authenticated just fine.
Congratulations! Your FreeRadius + MySQL setup is now working. Kick But no headache.