Just to give you a brief picture of my architecture.
We have a router where we provide users to login to the WIFI by registering themselves.
The registration data goes to the freeradius database under ‘Userinfo’ table.
And then router brings the session up by looking for that user in the Userinfo table and make and entry into the radacct table.
Recently we were trying to introduce a new column (Gender Column with values Male/Female ) in Userinfo table but when I try to register as new User, though I get entry into the Userinfo table, my session does not come up.
I get the following error in the freeradius logs.
Error: rlm_sql: Invalid operator “l” for attribute M
Wed Nov 22 16:14:37 2017 : Error: rlm_sql (sql): Error getting data from database
Wed Nov 22 16:14:37 2017 : Error: [sql] SQL query error; rejecting user
I am not sure what is the issue. If you see at the error it is saying character ‘I’ of Male is invalid. It always throws error at 3rd character.
Not sure if I need to change any config file at Mikrotik or Radius end?
Appreciate your timely help mates as this is bit urgent.
You cannot just tinker with the tables and expect it to work…
Seems your freeradius configuration makes it interpret that table as radcheck, and you chose to store gender on the op field, which freeradius expects to be a two character field, containing Freeradius operators.
radcheck table has to have id, username, attribute, op, and value filled with expected freeradius format for it to validate users, you need to respect the expected record fields.
Create another table for users info, and link back to user account on radcheck by using id field as key for your application, leaving radcheck alone.
Thanks Pukkita for pointing me to the right direction.
So it seems that Mikrotik try to read only these freeradius tables.
Also, what I understood from you statements is that radcheck is the table which MIkrotik checks to authenticate a user.
Seems that my already existing ‘Userinfo’ table is linked to this ‘radcheck’ by an id.
Mikrotik doesn’t check anything it simply asks freeradius: user X with password Z wants to login, should I allow it?
Then is freeradius which checks on its tables and simply answers Mikrotik router if the user successfully authenticated, and any user related reply items.
You need to check your freeradius sql configuration, as it seems is treating your userinfo table as the radcheck one.
Userinfo is a table explicitly created by me to get more information added for the user in the DB when a user registers with us.
There is no where in any schema of radius, I have mentioned 'UserInfo' table.
I am making an entry into the RadCheck table with my Username and default password.
So as per me if Mikrotik does ask for the authentication for a particular user to Radius, then Radius should checks in the RadCheck table and authenticates my user.
sq.conf
same SQL table, leave this as is. If you want them in
different tables, put the start table in acct_table1
The above process goes on well unless we make changes into the "Userinfo' table columns such as Gender and ZipCode and try to keep the characters more than one.
For E.g - If I keep value of Gender as 'Male' then I get the error and if I keep just 'M' then no error.
I cannot find where the mapping is happening between the UserInfo and the Radcheck.
As per me Userinfo should not be called for Authentication.
As already explained to you, this problem is not related to MikroTik at all. You are configuring FreeRadius and you are doing things that it does not like, and as a result the FreeRadius server no longer works and the MikroTik Router cannot authenticate your users.
I advise you to take it to another forum where FreeRadius is being discussed.
It looks like you think you can integrate your customer relation database with your authentication database, which probably is not a good idea at all.