FreeRADIUS Accounting Data Issue

Mikrotik NAS is authenticating but not recording data in radacct table

You're experiencing an issue where Mikrotik NAS is successfully authenticating users but the accounting data is not being recorded in the FreeRADIUS radacct table in MySQL.

This guide will help you troubleshoot and resolve this issue step by step.

Step 1: Check Mikrotik Configuration

Verify that your Mikrotik is properly configured to send accounting data to FreeRADIUS:

/radius add service=login,accounting address=192.168.1.10 secret=mysecret

Step 2: Verify FreeRADIUS SQL Module

Check if the SQL module is properly configured and enabled:

# Check if SQL module is loaded grep "sql" /etc/freeradius/3.0/sites-available/default # Test SQL connection sudo freeradius -X | grep sql

Step 3: Check Accounting Configuration

Ensure accounting is enabled in FreeRADIUS sites:

# Check accounting configuration grep -A 10 "accounting" /etc/freeradius/3.0/sites-available/default # Check if sql is called in accounting section grep "sql" /etc/freeradius/3.0/sites-available/default

Detailed Configuration Steps

1. Mikrotik RADIUS Configuration

Ensure your Mikrotik is configured to send both authentication and accounting requests to FreeRADIUS:

/radius add service=login,accounting address=192.168.1.10 secret=mysecret timeout=5s

2. FreeRADIUS SQL Module Configuration

Check your /etc/freeradius/3.0/mods-enabled/sql file:

# Database configuration driver = "mysql" dialect = "mysql" # Connection info server = "localhost" port = 3306 login = "radius" password = "radpass" # Database table configuration radius_db = "radius" # Enable accounting accounting { accounting_on = yes }

3. FreeRADIUS Site Configuration

Check your /etc/freeradius/3.0/sites-enabled/default file and ensure the accounting section includes sql:

# Accounting section accounting { # Update accounting data in SQL sql # Uncomment if you need detailed accounting # detail exec }

Note: After making any configuration changes, remember to restart FreeRADIUS:

sudo systemctl restart freeradius

4. Testing the Configuration

Run FreeRADIUS in debug mode to see what's happening with accounting requests:

# Stop the service first sudo systemctl stop freeradius # Run in debug mode sudo freeradius -X

Now generate some accounting data from your Mikrotik and watch the debug output for any SQL-related errors.

Success indicators: Look for messages like "Processing accounting packets" and "SQL query executed successfully" in the debug output.

Error indicators: If you see "SQL module not active" or database connection errors, your SQL configuration needs attention.

5. Verify Database Permissions

Ensure the radius user has proper permissions on the radacct table:

mysql -u root -p GRANT ALL ON radius.radacct TO 'radius'@'localhost'; FLUSH PRIVILEGES;

Common Issues and Solutions

Issue 1: SQL Module Not Enabled

If the SQL module isn't enabled in the accounting section, data won't be saved to the database.

Solution: Edit /etc/freeradius/3.0/sites-enabled/default and ensure the accounting section includes this line:

sql

Issue 2: Database Connection Problems

If FreeRADIUS can't connect to the MySQL database, accounting data won't be saved.

Solution: Verify your database connection settings in /etc/freeradius/3.0/mods-enabled/sql

Issue 3: Incorrect SQL Query Configuration

If the SQL queries in the FreeRADIUS configuration don't match your database schema, updates will fail.

Solution: Check the accounting queries in /etc/freeradius/3.0/mods-config/sql/main/mysql/queries.conf

Issue 4: Firewall Blocking Accounting Port

The accounting port (1813) might be blocked by a firewall.

Solution: Ensure port 1813 is open on your FreeRADIUS server:

sudo ufw allow 1813/udp