Logging Failure Reasons in FreeRADIUS

FreeRADIUS is an incredibly powerful RADIUS server and a tool I’m currently taking through a proof of concept as a possible replacement for Microsoft NPS. While the debug output of FreeRADIUS is fantastic in an interactive console, it doesn’t quite log everything to disk, syslog or SQL in normal operation.
One of the useful bits of information it would be nice to get is the failure reason logged in the radpostauth table in MySQL. That would make it much easier to trace why users are struggling to log into the service. It’s also assisting right now with some troubleshooting.
Making it happen isn’t complicated. We need to add the extra column to log the failure reason messages in to on radpostauth. To do this, run the following command in MySQL:
ALTER TABLE radpostauth ADD COLUMN message TEXT;
While that adds a place to log the data, we still need to add tell FreeRADIUS to enter the data in the new field. To do this, we edit the /etc/raddb/mods-config/sql/main/mysql/queries.conf file to update the post-auth query. The result should be something like this:

post-auth {
# Write SQL queries to a logfile. This is potentially useful for bulk inserts
# when used with the rlm_sql_null driver.
query = "\
INSERT INTO ${..postauth_table} \
(username, pass, reply, authdate, message) \
VALUES ( \
'%{SQL-User-Name}', \
'%{%{User-Password}:-%{Chap-Password}}', \
'%{reply:Packet-Type}', \
'%S', \
'%{reply:Reply-Message}')"
}

Restart FreeRADIUS to make the changes. The result of all this work should be something like this: