December 9, 2021 at 12:05 pm
Hi,
As I am unsure as to whether the following is a freeradius or MSSQL issue, I will start here and see if anyone has come across this before.
I enter a user into the DB with the credentials I want for the auth packet to be recieved (checked against). When I tail the log file on the linux server I see the warning "User not found in radcheck table". However the user is there. So, I configure up a second user and complete the same process and it is successful.
There is another table in the DB called "nas" and this is for the forwarding device that sends the auth packet. This is all configured correctly but the "nas" table seems to be ignored completely. I have a feeling this is more freeradius issues but it also seems like the DB works sometimes and sometimes it does not.
So, with all that information, here is the question:
Has anyone esle seen any strange MSSQL DB operations when utilised with freeradius?
December 9, 2021 at 4:00 pm
My opinion - this is likely not a database issue, but possible a configuration issue.
BUT to rule out database, I would do a dbcc checkdb to make sure nothing is corrupt. If nothing is corrupt, it is VERY likely to be a problem either with freeradius, hardware, or POSSIBLY a database configuration problem. The Database configuration problem I can think of is if you are using a case sensitive collation and freeradius expects a case insensitive collation. So if freeradius is searching the table "radcheck" (which is a user table likely created by freeradius; it is not something provided by SQL Server) for the user "usera" and the table has the user "UserA", those are 2 different values and thus you would get no results.
I am not sure what the nas table would be used for or what forwarding device you have set up for sending authentication packets or who those packets would go to.
What I would do is look through all logs - SQL Server, workstation, and server for problems with SQL Server, freeradius, and hardware. What COULD be happening that I am saying hardware is if the network infrastrucutre has a problem (NIC, cable, switch), then freeradius MAY get an error after authentication but during the read of the radcheck table and it MAY see any error from that to mean "user not found".
Now, when I say I can see possible corruption being the problem on the database side, I mean that if you are doing a SELECT * from the table, you are grabbing ALL data from the table, so SQL is likely to use the clustered index (if it has one, otherwise it'll use the heap). If freeradius is doing a "SELECT 1 FROM radcheck WHERE USER = 'UserA'" it MIGHT use a nonclustered index to find that user. If the nonclustered index is corrupt, but the clustered index is OK, SQL could be throwing an error and freeradius doesn't understand the error but got no users back and is therefore reporting that the user is not found.
Now it could also be a configuration issue with how freeradius connects to the database too. If it uses ODBC for the connection (for example; not likely as you said it was a linux server, but it could be you have a windows middle-ware tool working with freeradius to talk to the SQL instance), it could be the ODBC configuration is wrong.
There are a lot of things to check, but to me, based on the error, I would say the database is likely not the culprit and the problem is likely somewhere else. Especially since it is intermittent. Database issues are RARELY intermittent as the database just holds the data. The applications add, remove, and change the data.
My opinion - if you want to rule out the database, run an extended events session to see exactly what is being run against the database when it fails. Then you can re-run that exact query on the database side and see what is happening. I would do this on a test system as you don't want to impact production, but extended events are light on the database so the impact should be light unless you capture a boatload of information. But in your case, you are just going to need to be looking at the query that was executed, so that should be light.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply