FreeRADIUS InkBridge

SQL Users

Goal: To add a new user in the SQL database schema, similar to the New User tutorial. Next, you’ll send test packets as that user, and to receive a reply.

The RADIUS server can retrieve authentication and authorization information directly from the database. It can either replace or work with the authorize or users file.

Time: 25-40 minutes.

File:

  • mods-available/sql

  • mods-config/sql/main/*

  • mods-config/sql/main/<dialect>/schema.sql

  • mods-config/sql/main/<dialect>/queries.conf

Documentation pages:

Having completed the previous SQL tutorial, which confirmed the server’s ability to communicate with the SQL database, our next step is to add user entries to the SQL database.

Step 1: Add a User into the SQL Database

Add a test user directly into the database. We’re using MySQL in this example, so you will connect to the database server using the mysql command-line client.

Connect to your MySQL server with the credentials stored in the mods-available/sql file:

  • server = "localhost"

  • port = 3306

  • login = "radius"

  • password = "password"

  • radius_db = "radius"

$ mysql -h 127.0.0.1 -u radius -p

Enter the password when prompted.

Add the test user with the following commands:

USE radius;

INSERT INTO radcheck (username, attribute, op, value)
VALUES ('alice', 'Cleartext-Password', ':=', 'testing123');

INSERT INTO radreply (username, attribute, op, value)
VALUES ('alice', 'Reply-Message', '+', 'Hello! You authenticated via the SQL database.');

These commands may need to be modified slightly, depending on the syntax required by your SQL database. These commands mirror the "check" and "reply" entries listed in the file for the user "alice".

Confirm the rows were inserted using:

Use the SQL client to verify that the entries are now in the database.

SELECT * FROM radcheck WHERE username = 'alice';
SELECT * FROM radreply WHERE username = 'alice';

The two INSERT statements are the direct SQL equivalent of the following classic authorize file entry:

mysql> SELECT * FROM radcheck WHERE username = 'alice';
+----+----------+--------------------+----+------------+
| id | username | attribute          | op | value      |
+----+----------+--------------------+----+------------+
|  1 | alice    | Cleartext-Password | := | hello      |
+----+----------+--------------------+----+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM radreply WHERE username = 'alice';
+----+----------+---------------+----+------------------------------------------------+
| id | username | attribute     | op | value                                          |
+----+----------+---------------+----+------------------------------------------------+
|  1 | alice    | Reply-Message | := | Hello! You authenticated via the SQL database. |
+----+----------+---------------+----+------------------------------------------------+
1 row in set (0.00 sec)

If you see the rows appear in the SELECT output, the test user is correctly stored in the database.

Step 2: Test Authentication with radclient

Keep the server running in debug mode (radiusd -X).

Open a second terminal and send a PAP authentication request:

$ echo 'User-Name = "alice", User-Password = "hello"' | radclient -x 127.0.0.1 auth testing123

You should see Access-Accept and the Reply-Message coming back from the server. A successful response should look similar to this:

Sent Access-Request Id 60 from 0.0.0.0:48774 to 127.0.0.1:1812 length 63
	Message-Authenticator = 0x
        User-Name = "alice"
        User-Password = "hello"
Received Access-Accept Id 60 from 127.0.0.1:1812 to 0.0.0.0:48774 via lo length 93
        Message-Authenticator = 0x27b9670734452d218b977f5518b5e1f2
        Reply-Message = "Hello! You authenticated via the SQL database."
        User-Name = "alice"

Return to the terminal where the server is running in debug mode.

After the radclient command completes, review the debug output. Look for the section that corresponds to the relevant request.

Debug output

(0)      sql - SQL-User-Name set to 'alice'
(0)        sql - | %{SQL-User-Name}
(0)        sql - | --> alice
(0)        sql - | ||
(0)        sql - | %logical_or()
(0)          sql - | User-Password
(0)            sql - | %{User-Password}
(0)            sql - | --> hello
(0)          sql - | %logical_or(...)
(0)          sql - | --> hello
(0)        sql - | %{reply.Packet-Type}
(0)        sql - | --> Access-Accept
(0)        sql - | %S
(0)        sql - | --> 2026-02-11 11:06:52
(0)        sql - | %M
(0)        sql - | --> 374075
(0)        sql - | %{reply.Class}
(0)        sql - (null)
(0)      -sql (ok)

Step 3: Diagnose Failures

If the server rejects the access request, then there are a number of steps to take, to correct the problem:

  • Verify that FreeRADIUS is connecting to the SQL server.

  • Check that FreeRADIUS has printed Ready to process requests.

  • Verify that the SQL module returns ok, rather than notfound for the "alice" user.

If necessary, edit the mods-enabled/sql file, and enable additional debugging of SQL statements via the sqltrace and sqltracefile configuration options. If the SQL queries are performed by the server and logged to the file, but the request for user "bob" is still rejected, then perform those queries by hand, using an SQL test client. Once you have verified that the SQL test client returns the correct information for the queries, then send the access request again.

Questions

  1. Why do we not use an SQL database to "authenticate" users?

  2. How do we have a DEFAULT entry in an SQL database?

  3. Why is there no "Fall-Through" entry in an SQL database?

  4. Does that DEFAULT entry differ from its use in the file? If so, why, and how? If not, why not?

  5. What other configuration entries in sites-available/default exist for the sql module, and why?