An issue or question I see again and again – proper RADIUS logging with Microsoft NPS / Network Policy Server.
Let’s guide you through a few steps
- Install a Microsoft SQL or if not available SQL Express
- be aware – SQL Express has very tight database size limits and no SQL Agent – this might be an issue
- Create a new database via SQL Management Studio in the SQL server
- name it e.g. RADIUSLogging
- run the SQL script from this Microsoft website in a new query window against this database (make sure it is not run against any other database by accident)
- you could add a line like USE RADIUSLogging to prevent this – in the very top…
- configure your RADIUS server to log to this SQL server and database
- make sure you have fail-over logging to a text-file – to avoid issues in case your SQL DB grew to big or was not reachable for any reason
- decide in the text-file configuration if you want to deny access if there is an issue or if you still want to proceed with the logon
Now you have RADIUS logging the information to a SQL database – actually a single table – and you can dig around in it. The IT-Assets database provides a front-end example for this – you don’t need to use it – but it might be of help – see here.
To interpret all those columns and values – look at the following links for additional information:
- https://www.iana.org/assignments/radius-types/radius-types.xhtml#radius-types-27
- https://tools.ietf.org/html/rfc2865#section-5.7
- https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/dd197570(v=ws.10)
You will face the issue that the database will grow rapidly – depending on how many requests go to your RADIUS system etc… Keep an close eye on it – use a monitoring software like Paessler / PRTG to monitor the size and keep in mind that SQL Express might have size limits like 10 GB. The full version of Microsoft SQL has no such limits and further can you use SQL Agent to execute tasks. The following script can help you purging data from the RADIUS database to keep its size under control. You can use SQL Agent (not in SQL Express) to run it automatically or if you use SQL Express either run it manually or with another solution somehow automatically against the database delete older entries.
The script actually will purge data older then 14 days – you can adjust the days to your liking / needs.
1 2 | DELETE FROM dbo.accounting_data where datediff(day,dbo.accounting_data.timestamp,getdate()) >14 |