Common error on sql_counter on Ver 2.1.5
Hi all, I recently installed freeradius Ver. 2.1.5 and I'm trying to configure it to work as a previous installation of Ver. 1.1.x. I'm stuck at sql counter module. On 1.1.x I use the common sessioncounter counter with sql module, but with 2.1.5 I got the message "rlm_sqlcounter: Could not find Check item value pair". I believe the configurations are indentical for both versions of freeradius, but I'm obviously missing something. Can someone help me to find where can be the error? I think it's a trivial one, but I'm stuck since 3 days. Thank You for interest. Mauro Iorio
I recently installed freeradius Ver. 2.1.5 and I'm trying to configure it to work as a previous installation of Ver. 1.1.x.
I'm stuck at sql counter module. On 1.1.x I use the common sessioncounter counter with sql module, but with 2.1.5 I got the message "rlm_sqlcounter: Could not find Check item value pair".
User entry didn't match. Post the debug (radiusd -X) and the user entry. You wouldn't be using User-Password as the password attribute? Ivan Kalik Kalik Informatika ISP
User entry didn't match. Post the debug (radiusd -X) and the user entry. You wouldn't be using User-Password as the password attribute?
From radcheck table Id Username Attribute Value op 7216 mauro Password flower ==
From usergroup table Id Username GroupName 14194 mauro 60
From radgroupcheck table ID GroupName Attribute Value op 2 60 Max-All-Session 3600 :=
radreply table is empty as it was with 1.1.x Command line user for testing radclient 192.168.4.203:1812 auth abcdefgh -f radius.packet -t 5000 radius.packet file User-Name = "mauro" User-Password = "mauropwd" NAS-IP-Address = 127.0.0.1 NAS-Port = 1 Called-Station-ID = "00-03-9D-4A-0A-0A" Below there is the debug (radiusd -X) output: Thanks, Mauro. --------------------------------------------- debug (radiusd -X) output: Ready to process requests. rad_recv: Access-Request packet from host 192.168.4.203 port 47750, id=224, length=76 User-Name = "mauro" User-Password = "mauropwd" NAS-IP-Address = 127.0.0.1 NAS-Port = 1 Called-Station-Id = "00-03-9D-4A-0A-0A" +- entering group authorize {...} ++[preprocess] returns ok ++[chap] returns noop [sql] expand: %{User-Name} -> mauro [sql] sql_set_user escaped user --> 'mauro' rlm_sql (sql): Reserving sql socket id: 0 [sql] expand: SELECT id, UserName, Attribute, Value, op FROM UtentiAutorizzati WHERE UserName = '%{SQL-User-Name}' AND MACADDWAN = '%{Called-Station-Id}' AND (CheckOnLine - UtentiConnessi) > 0 AND DataScadenza > GetDate() -> SELECT id, Us erName, Attribute, Value, op FROM UtentiAutorizzati WHERE UserName = 'mauro' AND MACADDWAN = '00-03-9D-4A-0A-0A' AND (CheckOnLine - UtentiConnessi) > 0 AND Data Scadenza > GetDate() query: SELECT id, UserName, Attribute, Value, op FROM UtentiAutorizzati WHERE U serName = 'mauro' AND MACADDWAN = '00-03-9D-4A-0A-0A' AND (CheckOnLine - UtentiC onnessi) > 0 AND DataScadenza > GetDate() WARNING: Found User-Password == "...". WARNING: Are you sure you don't mean Cleartext-Password? WARNING: See "man rlm_pap" for more information. [sql] User found in radcheck table [sql] expand: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Userna me = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FRO M radreply WHERE Username = 'mauro' ORDER BY id query: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = 'ma uro' ORDER BY id rlm_sql (sql): Released sql socket id: 0 ++[sql] returns ok rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[sessioncounter] returns noop !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!! !!! Replacing User-Password in config items with Cleartext-Password. !!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!! !!! Please update your configuration so that the "known good" !!! !!! clear text password is in Cleartext-Password, and not in User-Password. !!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!! WARNING: Please update your configuration, and remove 'Auth-Type = Local' WARNING: Use the PAP or CHAP modules instead. User-Password in the request is correct. +- entering group post-auth {...} ++[exec] returns noop Sending Access-Accept of id 224 to 192.168.4.203 port 47750 Finished request 9. Going to the next request Waking up in 4.9 seconds. Cleaning up request 9 ID 224 with timestamp +194 Ready to process requests.
User entry didn't match. Post the debug (radiusd -X) and the user entry. You wouldn't be using User-Password as the password attribute?
From radcheck table Id Username Attribute Value op 7216 mauro Password flower ==
Even worse. Password has been obsolite for at least 5 years.
--------------------------------------------- debug (radiusd -X) output:
...
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!! !!! Replacing User-Password in config items with Cleartext-Password. !!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!! !!! Please update your configuration so that the "known good" !!! !!! clear text password is in Cleartext-Password, and not in User-Password. !!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!! WARNING: Please update your configuration, and remove 'Auth-Type = Local' ...
You didn't notice any of that? How much bigger should the warnings be? Did you bother looking into users file/FAQ/SQL howto to see how user entries should look like? Ivan Kalik Kalik Informatika ISP
Am 18.05.2009 um 18:15 schrieb Mauro Iorio - Smart Soft s.r.l.:
User entry didn't match. Post the debug (radiusd -X) and the user entry. You wouldn't be using User-Password as the password attribute?
From radcheck table Id Username Attribute Value op 7216 mauro Password flower ==
Try to assign ( := ) the password, not to compare ( == ) it. Also probably Password is not the right attribute name. Try to use Cleartext-Password ...
From usergroup table
[...]
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!! !!! !!! Replacing User-Password in config items with Cleartext- Password. !!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!! !!! !!! Please update your configuration so that the "known good" !!! !!! clear text password is in Cleartext-Password, and not in User- Password. !!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!! !!!
... as the log is asking.
[...] Have a nice day! Nicolas Goutte extragroup GmbH - Karlsruhe Waldstr. 49 76133 Karlsruhe Germany Geschäftsführer: Stephan Mönninghoff, Hans Martin Kern, Tilman Haerdle Registergericht: Amtsgericht Münster / HRB: 5624 Steuer Nr.: 337/5903/0421 / UstID: DE 204607841
[sql] expand: %{User-Name} -> mauro [sql] sql_set_user escaped user --> 'mauro' rlm_sql (sql): Reserving sql socket id: 0 [sql] expand: SELECT id, UserName, Attribute, Value, op FROM UtentiAutorizzati WHERE UserName = '%{SQL-User-Name}' AND MACADDWAN = '%{Called-Station-Id}' AND (CheckOnLine - UtentiConnessi) > 0 AND DataScadenza > GetDate() -> SELECT id, Us erName, Attribute, Value, op FROM UtentiAutorizzati WHERE UserName = 'mauro' AND MACADDWAN = '00-03-9D-4A-0A-0A' AND (CheckOnLine - UtentiConnessi) > 0 AND Data Scadenza > GetDate() query: SELECT id, UserName, Attribute, Value, op FROM UtentiAutorizzati WHERE U serName = 'mauro' AND MACADDWAN = '00-03-9D-4A-0A-0A' AND (CheckOnLine - UtentiC onnessi) > 0 AND DataScadenza > GetDate() WARNING: Found User-Password == "...". WARNING: Are you sure you don't mean Cleartext-Password? WARNING: See "man rlm_pap" for more information. [sql] User found in radcheck table [sql] expand: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Userna me = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FRO M radreply WHERE Username = 'mauro' ORDER BY id query: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = 'ma uro' ORDER BY id rlm_sql (sql): Released sql socket id: 0 ++[sql] returns ok
PS. You have either disabled group checking or removed group membership query. Ivan Kalik Kalik Informatika ISP
PS. You have either disabled group checking or removed group membership query.
Ivan Kalik Kalik Informatika ISP
None of them. Group checking is enabled (read_groups = yes) and the query (authorize_group_check_query = "SELECT ... ") is defined in sql module. But simply the query isn't executed. Any Ideas? Now the attribute is Cleartext-Password and the op is := in radcheck ... The output now is shorter (without any warnings) but still no counter. Here it is: -------------------------- Ready to process requests. rad_recv: Access-Request packet from host 192.168.4.203 port 37145, id=67, lengt h=76 User-Name = "mauro" User-Password = "flower" NAS-IP-Address = 127.0.0.1 NAS-Port = 1 Called-Station-Id = "00-03-9D-4A-0A-0A" +- entering group authorize {...} ++[preprocess] returns ok ++[chap] returns noop [sql] expand: %{User-Name} -> mauro [sql] sql_set_user escaped user --> 'mauro' rlm_sql (sql): Reserving sql socket id: 4 [sql] expand: SELECT id, UserName, Attribute, Value, op FROM UtentiAutorizzati WHERE UserName = '%{SQL-User-Name}' AND MACADDWAN = '%{Called-Station-Id}' AND (CheckOnLine - UtentiConnessi) > 0 AND DataScadenza > GetDate() -> SELECT id, Us erName, Attribute, Value, op FROM UtentiAutorizzati WHERE UserName = 'mauro' AND MACADDWAN = '00-03-9D-4A-0A-0A' AND (CheckOnLine - UtentiConnessi) > 0 AND Data Scadenza > GetDate() query: SELECT id, UserName, Attribute, Value, op FROM UtentiAutorizzati WHERE U serName = 'mauro' AND MACADDWAN = '00-03-9D-4A-0A-0A' AND (CheckOnLine - UtentiC onnessi) > 0 AND DataScadenza > GetDate() [sql] User found in radcheck table [sql] expand: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Userna me = '%{SQL-User-Name}' ORDER BY id -> SELECT id,UserName,Attribute,Value,op FRO M radreply WHERE Username = 'mauro' ORDER BY id query: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username = 'ma uro' ORDER BY id rlm_sql (sql): Released sql socket id: 4 ++[sql] returns ok rlm_sqlcounter: Entering module authorize code rlm_sqlcounter: Could not find Check item value pair ++[sessioncounter] returns noop WARNING: Please update your configuration, and remove 'Auth-Type = Local' WARNING: Use the PAP or CHAP modules instead. User-Password in the request is correct. +- entering group post-auth {...} ++[exec] returns noop Sending Access-Accept of id 67 to 192.168.4.203 port 37145 Finished request 0. Going to the next request Waking up in 4.9 seconds. Cleaning up request 0 ID 67 with timestamp +134 Ready to process requests.
PS. You have either disabled group checking or removed group membership query.
Ivan Kalik Kalik Informatika ISP
None of them. Group checking is enabled (read_groups = yes) and the query (authorize_group_check_query = "SELECT ... ") is defined in sql module. But simply the query isn't executed. Any Ideas?
No, group *membership* query (when I write membership, I do mean membership). Have you just copied queries from the old version without looking if anything has changed? If you are upgrading from old version to a new one, which documentation should you follow - old or new? You opted for old, and are now wondering why things aren't working. It's no mistery to me. Use *new* documentation (user entries, sql queries, etc.). Configuration is largely compatible but things do change over years. Ivan Kalik Kalik Informatika ISP
No, group *membership* query (when I write membership, I do mean membership). Have you just copied queries from the old version without looking if anything has changed?
If you are upgrading from old version to a new one, which documentation should you follow - old or new? You opted for old, and are now wondering why things aren't working. It's no mistery to me. Use *new* documentation (user entries, sql queries, etc.). Configuration is largely compatible but things do change over years.
Ivan Kalik Kalik Informatika ISP
OK, I admit that reading the doc is *always* a fundamental step. But I've read somewhere that configuration files for v 1.1.x should work also on 2.1.x. and so I was trying to migrate with minimal modifications. Actually I was missing only the membership query that was not present in 1.1.x version of freeradius. So everything is working *except* the I've an error when counter retrieves SUM(AcctSessionTime) from radacct. Executing SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='mauro' from SQL Server Management Studio gives me 294841 (Yes, that's a lot of seconds, is a test user) while the output of radiusd -X is: rlm_sql (sql): Reserving sql socket id: 3 query: SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='mauro' [sessioncounter] sql_xlat finished rlm_sql (sql): Released sql socket id: 3 [sessioncounter] expand: %{sql:SELECT SUM(AcctSessionTime) FROM radacct W HERE UserName='mauro'} -> 2948 rlm_sqlcounter: Check item is greater than query result rlm_sqlcounter: Authorized user mauro, check_item=3600, counter=2948 rlm_sqlcounter: Sent Reply-Item for user mauro, Type=Session-Timeout, value=652 ++[sessioncounter] returns ok That's' totally wrong since 294841 is much bigger than 3600 and not smaller... And this happens with users from different groups and with different SUM(AcctSessionTime). *Every* time SUM(AcctSessionTime) is bigger than 9999 it looses the fifth digit of the response, better it looses every digits after the fourth!!! What am I missing??? (I'm going to write this to the newsletter too) Anyway I thank You very much for Your interest in my problem and for spending Your time on it. Mauro Iorio.
participants (3)
-
Ivan Kalik -
Mauro Iorio - Smart Soft s.r.l. -
Nicolas Goutte