Connecting FreeRadius 2 to external SQL database for authentication with CP
Currently i have a Captive Portal setup on pfsense which links to the built in FreeRadius 2 package. I use the freeRadius for both authentication and for accounting needs. EG: The maximum amount of traffic a user can consume per day. However i would like to integrate a form of 'self-service' for users, so they can login to a PHP based website and register their details and get an account setup for them in the MYSQL server from oracle. I have created all the needed tables in a database called 'radius'. And tables in it like 'radpostauth' 'cui' 'radipool' etc…. I have no firewalls in the way of the mysql server and the pfsense box but when i try to authentication with a custom entry in the database. The captive portal says it doesn't recieve a valid Radius reply?
How do i get freeradius and SQL to connect?
I realize this may come too late to assist in your particular circumstance, however it may of use to others…
In my setup, I have observed the dreaded 'no valid radius response' error on a number of occasions, and it appears that the most common reason for this is that the mysql query did not execute for some reason. Specifically, if I remember correctly, my script was trying to perform an INSERT with a duplicate entry on a unique field.
So as a suggestion put together some web-visible php test pages or thoroughly test your queries in something like myphpadmin to check for any errors.
make sure that you set the correct IP address on CP.
make sure you set the correct listening interfaces and ports for auth (1812) and acct (1813) on freeradius
make sure you set the correct credentials on freeradius –> mysql to connect successfully to your external mysql database
make sure that the shared secret for the "clients" on your mysql database is the same as on CP - perhaps test an simple password for testing purposes
If you are using an external database then the tabs "Users", "MAC", "Clients" will not work. All this must be configured on the mysql databse.
another thing to check: if your external database is on a hosted server, make sure that you are allowing connections from your pfsense IP address. e.g. in Control Panel (on your hosted server) go to Databases -> RemoteMySQL and enter the allowed host IP or domain name