rlm_sqlcounter doesn't increase counter
Hello Fellow Freeradius-Users, I have freeradius-1.1.3 installed on Cent OS and I have Cisco C2960 switch as my NAS. I have a task to allocate browsing time 2hrs per day to students in our computer labs. Logs from radius in debug mode, counter doesn't increase it always shows (Check item - counter) is greater than zero; check_item=120, counter=0. Users are not signed out after Max-Daily-Session is reached. I want to be able to disconnect a user logged in after they reach their maximum daily session. Kindly find below config files which indicate maximum daily session of 120 seconds for testing purposes. I have searched through Freeradius's mailing-List but have not found solution yet. I will be grateful for any help. Thank you. ############################### config on Cisco switch C2960 ############################# aaa new-model ! ! aaa group server radius dot1x ! aaa authentication login default group radius local aaa authentication dot1x default group radius aaa authorization exec default group radius if-authenticated aaa authorization network default group radius aaa accounting send stop-record authentication failure aaa accounting suppress null-username aaa accounting session-duration ntp-adjusted aaa accounting update newinfo periodic 1 aaa accounting exec default start-stop group radius aaa accounting network default start-stop group radius aaa accounting connection default start-stop group radius aaa accounting resource default start-stop-failure group radius interface FastEthernet0/9 switchport access vlan 6 switchport mode access authentication host-mode multi-auth authentication port-control auto authentication periodic authentication timer inactivity 5 dot1x pae both dot1x max-req 3 spanning-tree portfast radius-server host 10.1.7.202 auth-port 1812 acct-port 1813 key "key" ############################### /etc/raddb/sqlcounter.conf ################################ sqlcounter noresetcounter { counter-name = Max-All-Session-Time check-name = Max-All-Session sqlmod-inst = sql key = User-Name reset = never query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'" } sqlcounter dailycounter { filename = ${raddbdir}/db.daily count-attribute = Acct-Session-Time driver = "rlm_sqlcounter" allowed-servicetype = Framed-User counter-name = Daily-Session-Time check-name = Max-Daily-Session sqlmod-inst = sqlcca3 key = User-Name reset = daily query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'" } sqlcounter monthlycounter { counter-name = Monthly-Session-Time check-name = Max-Monthly-Session sqlmod-inst = sqlcca3 key = User-Name reset = monthly query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'" } ###################################################################### /etc/raddb/radius.conf ###################################################################### modules { $INCLUDE ${confdir}/sqlcounter.conf instantiate { exec expr dailycounter } authorize { preprocess mschap suffix eap files sql dailycounter } authenticate { Auth-Type PAP { pap } Auth-Type CHAP { chap } Auth-Type MS-CHAP { mschap } unix eap } ####################################################################### /etc/raddb/users ####################################################################### clare Max-Daily-Session := 120, User-Password := "password" Auth-Type := Reject, Reply-Message = "Your time limit is used", Simultaneous-Use := 1, ######################################################################### Dubug output ######################################################################### Going to the next request Waking up in 6 seconds... rad_recv: Access-Request packet from host 10.1.5.4:1645, id=220, length=244 User-Name = "clare" Service-Type = Framed-User Framed-MTU = 1500 Called-Station-Id = "EC-30-91-1D-69-89" Calling-Station-Id = "00-1E-33-D5-7A-68" EAP-Message = 0x0209002b19001703010020205ecf13b3dc88bd738478cf6a2111e5903a5089b64e282bc15b5c7c6c0e771c Message-Authenticator = 0x2cac65588536701091195c7b06db31a1 Cisco-AVPair = "audit-session-id=0A01050400000672A669A7C1" NAS-Port-Type = Ethernet NAS-Port = 50009 NAS-Port-Id = "FastEthernet0/9" State = 0xb014050291b7b551b59b2a5f168e4f7d NAS-IP-Address = 10.1.5.4 Processing the authorize section of radiusd.conf modcall: entering group authorize for request 26 modcall[authorize]: module "preprocess" returns ok for request 26 modcall[authorize]: module "mschap" returns noop for request 26 rlm_realm: No '@' in User-Name = "clare", looking up realm NULL rlm_realm: No such realm "NULL" modcall[authorize]: module "suffix" returns noop for request 26 rlm_eap: EAP packet type response id 9 length 43 rlm_eap: No EAP Start, assuming it's an on-going EAP conversation modcall[authorize]: module "eap" returns updated for request 26 users: Matched entry clare at line 97 modcall[authorize]: module "files" returns ok for request 26 radius_xlat: 'clare' rlm_sql (sql): sql_set_user escaped user --> 'clare' radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = 'clare' ORDER BY id' rlm_sql (sql): Reserving sql socket id: 4 rlm_sql (sql): User clare not found in radcheck radius_xlat: 'SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'clare' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id' radius_xlat: 'SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'clare' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id' rlm_sql (sql): User clare not found in radgroupcheck rlm_sql (sql): Released sql socket id: 4 rlm_sql (sql): User not found modcall[authorize]: module "sql" returns notfound for request 26 rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(AcctSessionTime - GREATEST((1330102800 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{User-Name}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime
'1330102800'' radius_xlat: 'SELECT SUM(AcctSessionTime - GREATEST((1330102800 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='clare' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1330102800'' sqlcounter_expand: '%{sql:SELECT SUM(AcctSessionTime - GREATEST((1330102800 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='clare' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime '1330102800'}' radius_xlat: Running registered xlat function of module sql for string 'SELECT SUM(AcctSessionTime - GREATEST((1330102800 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='clare' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1330102800'' rlm_sql (sql): - sql_xlat radius_xlat: 'clare' rlm_sql (sql): sql_set_user escaped user --> 'clare' radius_xlat: 'SELECT SUM(AcctSessionTime - GREATEST((1330102800 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='clare' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1330102800'' rlm_sql (sql): Reserving sql socket id: 3 rlm_sql (sql): row[0] returned NULL rlm_sql (sql): Released sql socket id: 3 radius_xlat: '' rlm_sqlcounter: (Check item - counter) is greater than zero rlm_sqlcounter: Authorized user clare, check_item=120, counter=0 rlm_sqlcounter: Sent Reply-Item for user clare, Type=Session-Timeout, value=120 modcall[authorize]: module "dailycounter" returns ok for request 26 modcall: leaving group authorize (returns updated) for request 26 rad_check_password: Found Auth-Type EAP auth: type "EAP" Processing the authenticate section of radiusd.conf modcall: entering group authenticate for request 26 rlm_eap: Request found, released from the list rlm_eap: EAP/peap rlm_eap: processing type peap rlm_eap_peap: Authenticate rlm_eap_tls: processing TLS eaptls_verify returned 7 rlm_eap_tls: Done initial handshake eaptls_process returned 7 rlm_eap_peap: EAPTLS_OK rlm_eap_peap: Session established. Decoding tunneled attributes. rlm_eap_peap: Received EAP-TLV response. rlm_eap_peap: Tunneled data is valid. rlm_eap_peap: Success rlm_eap: Freeing handler modcall[authenticate]: module "eap" returns ok for request 26 modcall: leaving group authenticate (returns ok) for request 26 radius_xlat: 'Your time limit is used' Login OK: [clare] (from client C2960_NOC_LAN1 port 50009 cli 00-1E-33-D5-7A-68) Sending Access-Accept of id 220 to 10.1.5.4 port 1645 Reply-Message = "Your time limit is used" Session-Timeout = 120 MS-MPPE-Recv-Key = 0x33430a677c0ab2f0331e95b8ad8a7c96de6ac7e8065f622a7883bb736882d0e0 MS-MPPE-Send-Key = 0xed2c5c1c315e2387e636c3796035e004cb93d3d159a17c696a73bcd418ac483b EAP-Message = 0x03090004 Message-Authenticator = 0x00000000000000000000000000000000 User-Name = "clare" Finished request 26
hi, you dont seem to have SQL enabled in the accounting section... the WIKI entry should work http://wiki.freeradius.org/Rlm_sqlcounter alan
Hello Alan, Thanks for your response. Sorry I did not include accounting module of radiusd.conf. I have sql declared in accounting module. I have followed examples from http://wiki.freeradius.org/Rlm_sqlcounter, but my counter doesn't still increase. What am I missing please. ############################## # config on Cisco switch C2960 ############################# aaa new-model ! ! aaa group server radius dot1x ! aaa authentication login default group radius local aaa authentication dot1x default group radius aaa authorization exec default group radius if-authenticated aaa authorization network default group radius aaa accounting send stop-record authentication failure aaa accounting suppress null-username aaa accounting session-duration ntp-adjusted aaa accounting update newinfo periodic 1 aaa accounting exec default start-stop group radius aaa accounting network default start-stop group radius aaa accounting connection default start-stop group radius aaa accounting resource default start-stop-failure group radius interface FastEthernet0/9 switchport access vlan 6 switchport mode access authentication host-mode multi-auth authentication port-control auto authentication periodic authentication timer inactivity 5 dot1x pae both dot1x max-req 3 spanning-tree portfast radius-server host 10.1.7.202 auth-port 1812 acct-port 1813 key "key" ############################### /etc/raddb/sqlcounter.conf ################################ sqlcounter noresetcounter { counter-name = Max-All-Session-Time check-name = Max-All-Session sqlmod-inst = sql key = User-Name reset = never query = "SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}'" } sqlcounter dailycounter { filename = ${raddbdir}/db.daily count-attribute = Acct-Session-Time driver = "rlm_sqlcounter" allowed-servicetype = Framed-User counter-name = Daily-Session-Time check-name = Max-Daily-Session sqlmod-inst = sqlcca3 key = User-Name reset = daily query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'" } sqlcounter monthlycounter { counter-name = Monthly-Session-Time check-name = Max-Monthly-Session sqlmod-inst = sqlcca3 key = User-Name reset = monthly query = "SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'" } ###################################################################### /etc/raddb/radius.conf ###################################################################### modules { $INCLUDE ${confdir}/sqlcounter.conf instantiate { exec expr dailycounter } authorize { preprocess mschap suffix eap files sql dailycounter } authenticate { Auth-Type PAP { pap } Auth-Type CHAP { chap } Auth-Type MS-CHAP { mschap } unix eap } accounting { detail radutmp sql } ####################################################################### /etc/raddb/users ####################################################################### clare Max-Daily-Session := 120, User-Password := "password" Auth-Type := Reject, Reply-Message = "Your time limit is used", Simultaneous-Use := 1, ######################################################################### Dubug output ######################################################################### Going to the next request Waking up in 6 seconds... rad_recv: Access-Request packet from host 10.1.5.4:1645, id=110, length=244 User-Name = "clare" Service-Type = Framed-User Framed-MTU = 1500 Called-Station-Id = "EC-30-91-1D-69-89" Calling-Station-Id = "00-1E-33-D5-7A-68" EAP-Message = 0x0209002b190017030100206eb3a54de149101a446ac5628da57e59a88d30e6e483ecd1910a62e906bfebc8 Message-Authenticator = 0xd0a6c72ab057beaf9af45de1cc156ad1 Cisco-AVPair = "audit-session-id=0A010504000000DE03269279" NAS-Port-Type = Ethernet NAS-Port = 50009 NAS-Port-Id = "FastEthernet0/9" State = 0xdd9c47cdca777967fd16da5036327708 NAS-IP-Address = 10.1.5.4 Processing the authorize section of radiusd.conf modcall: entering group authorize for request 8 modcall[authorize]: module "preprocess" returns ok for request 8 modcall[authorize]: module "mschap" returns noop for request 8 rlm_realm: No '@' in User-Name = "clare", looking up realm NULL rlm_realm: No such realm "NULL" modcall[authorize]: module "suffix" returns noop for request 8 rlm_eap: EAP packet type response id 9 length 43 rlm_eap: No EAP Start, assuming it's an on-going EAP conversation modcall[authorize]: module "eap" returns updated for request 8 users: Matched entry clare at line 97 modcall[authorize]: module "files" returns ok for request 8 radius_xlat: 'clare' rlm_sql (sql): sql_set_user escaped user --> 'clare' radius_xlat: 'SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = 'clare' ORDER BY id' rlm_sql (sql): Reserving sql socket id: 3 rlm_sql (sql): User clare not found in radcheck radius_xlat: 'SELECT radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE usergroup.Username = 'clare' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id' radius_xlat: 'SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE usergroup.Username = 'clare' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id' rlm_sql (sql): User clare not found in radgroupcheck rlm_sql (sql): Released sql socket id: 3 rlm_sql (sql): User not found modcall[authorize]: module "sql" returns notfound for request 8 rlm_sqlcounter: Entering module authorize code sqlcounter_expand: 'SELECT SUM(AcctSessionTime - GREATEST((1330300800 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{User-Name}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime
'1330300800'' radius_xlat: 'SELECT SUM(AcctSessionTime - GREATEST((1330300800 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='clare' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1330300800'' sqlcounter_expand: '%{sqlcca3:SELECT SUM(AcctSessionTime - GREATEST((1330300800 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='clare' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime '1330300800'}' WARNING: Attempt to use unknown xlat function, or non-existent attribute in string %{sqlcca3:SELECT SUM(AcctSessionTime - GREATEST((1330300800 - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='clare' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '1330300800'} radius_xlat: '' rlm_sqlcounter: (Check item - counter) is greater than zero rlm_sqlcounter: Authorized user clare, check_item=120, counter=0 rlm_sqlcounter: Sent Reply-Item for user clare, Type=Session-Timeout, value=120 modcall[authorize]: module "dailycounter" returns ok for request 8 modcall: leaving group authorize (returns updated) for request 8 rad_check_password: Found Auth-Type EAP auth: type "EAP" Processing the authenticate section of radiusd.conf modcall: entering group authenticate for request 8 rlm_eap: Request found, released from the list rlm_eap: EAP/peap rlm_eap: processing type peap rlm_eap_peap: Authenticate rlm_eap_tls: processing TLS eaptls_verify returned 7 rlm_eap_tls: Done initial handshake eaptls_process returned 7 rlm_eap_peap: EAPTLS_OK rlm_eap_peap: Session established. Decoding tunneled attributes. rlm_eap_peap: Received EAP-TLV response. rlm_eap_peap: Tunneled data is valid. rlm_eap_peap: Success rlm_eap: Freeing handler modcall[authenticate]: module "eap" returns ok for request 8 modcall: leaving group authenticate (returns ok) for request 8 radius_xlat: 'Your time limit is used' Login OK: [clare] (from client C2960_NOC_LAN1 port 50009 cli 00-1E-33-D5-7A-68) Sending Access-Accept of id 110 to 10.1.5.4 port 1645 Reply-Message = "Your time limit is used" Session-Timeout = 120 MS-MPPE-Recv-Key = 0xaa033a908b53d8bc3573d3b0273a3feafa798f8a29d09b78e921e86aa1d7f143 MS-MPPE-Send-Key = 0xa08c74c90d15407a2413af4c7f7a6c56fce18da60f5a33d288fbc6470349bed4 EAP-Message = 0x03090004 Message-Authenticator = 0x00000000000000000000000000000000 User-Name = "clare" Finished request 8 Going to the next request Waking up in 6 seconds...
On Mon, Feb 27, 2012 at 11:19 PM, pamela pomary <ppomary@gmail.com> wrote:
Hello Alan,
Thanks for your response. Sorry I did not include accounting module of radiusd.conf. I have sql declared in accounting module. I have followed examples from http://wiki.freeradius.org/Rlm_sqlcounter, but my counter doesn't still increase. What am I missing please.
Do you have accounting records for that user in radacct? -- Fajar
Hello once again, Thank you for your help in resolving this problem. I have counters increasing now after defining Max-Daily-Session for DEFAULT user in the users file like below and adding the line aaa accounting dot1x default start-stop group radius suggested by Alan Buxey to config on my Cisco 2960 switch NAS. DEFAULT Service-Type == Login-User Framed-IP-Address = 255.255.255.254, Framed-MTU = 576, Max-Daily-Session = 240, I found the following in the log ### Debug log ### rlm_counter: Entering module authorize code rlm_counter: Searching the database for key 'clare' rlm_counter: Key Found. rlm_counter: Check item = 240, Count = 2386 rlm_counter: Rejected user clare, check_item=240, counter=2386 modcall[authorize]: module "daily" returns reject for request 0 modcall: leaving group authorize (returns reject) for request 0 Invalid user (rlm_counter: Maximum hourly usage time reached): [clare] (from client C2960_NOC_LAN1 port 50009 cli 00-1E-33-D5-7A-68) Delaying request 0 for 1 seconds Finished request 0 Going to the next request --- Walking the entire request list --- Waking up in 1 seconds... --- Walking the entire request list --- Waking up in 1 seconds... --- Walking the entire request list --- Sending Access-Reject of id 230 to 10.1.5.4 port 1645 Reply-Message = "Your maximum hourly usage time has been reached" I realise user clare is rejected only when user login after cable is unplugged and plugged back into the computer. What it means is that when a user login and is granted access, user's counter keeps increasing beyond the Max-Daily-Session until cable is unplugged from the computer.When cable is plugged back into the computer and user is prompted to login, user is rejected because he/she has exceeded the maximum daily session. What I want to achieve is to get user session disconnected/timeout automatically while cable is still plugged in and user reaching his/her maximum daily session set for the day. I hope it is possible to do :) I have the following config on my NAS- Cisco 2960 switch aaa authentication login default group radius local aaa authentication dot1x default group radius aaa authorization exec default group radius if-authenticated aaa authorization network default group radius aaa accounting suppress null-username aaa accounting session-duration ntp-adjusted aaa accounting update newinfo periodic 1 aaa accounting dot1x default start-stop group radius aaa accounting exec default start-stop group radius aaa accounting network default start-stop group radius aaa accounting connection default start-stop group radius aaa accounting resource default start-stop-failure group radius interface FastEthernet0/9 switchport access vlan 6 switchport mode access authentication host-mode multi-auth authentication port-control auto authentication periodic authentication timer reauthenticate 60 authentication violation protect dot1x pae both dot1x max-req 3 spanning-tree portfast
participants (3)
-
Alan Buxey -
Fajar A. Nugraha -
pamela pomary