how to transfer from "users file to sql"
Hi, we are testing a wlan with "private preshared keys". In the cisco wlancontroller world it works with the AAA override feature and some entries in the radius database. my first with the users file works fine: If I transfer this entries to the mysql database, the aut-type was not set. What is missing? I tested it with the default configuration, the sql module was enabled. ------------------------------------ configuration with users file ------------------------------------ users file: 3c15c2e840fe Auth-Type := Accept cisco-AVPair = "psk-mode=ascii", cisco-AVPair = "psk=abcdefgh" debug output: Thu Sep 12 10:27:31 2019 : Debug: (0) Received Access-Request Id 26 from 129.217.228.186:32776 to 129.217.228.164:1812 length 255 Thu Sep 12 10:27:31 2019 : Debug: (0) User-Name = "3c15c2e840fe" Thu Sep 12 10:27:31 2019 : Debug: (0) Called-Station-Id = "70-ea-1a-84-18-c0:itmc-ipsk" Thu Sep 12 10:27:31 2019 : Debug: (0) Calling-Station-Id = "3c-15-c2-e8-40-fe" Thu Sep 12 10:27:31 2019 : Debug: (0) NAS-Port = 1 Thu Sep 12 10:27:31 2019 : Debug: (0) NAS-IP-Address = 129.217.251.242 Thu Sep 12 10:27:31 2019 : Debug: (0) NAS-Identifier = "wlc-staging" Thu Sep 12 10:27:31 2019 : Debug: (0) Airespace-Wlan-Id = 10 Thu Sep 12 10:27:31 2019 : Debug: (0) User-Password = "3c15c2e840fe" Thu Sep 12 10:27:31 2019 : Debug: (0) Service-Type = Call-Check Thu Sep 12 10:27:31 2019 : Debug: (0) Framed-MTU = 1300 Thu Sep 12 10:27:31 2019 : Debug: (0) NAS-Port-Type = Wireless-802.11 Thu Sep 12 10:27:31 2019 : Debug: (0) Tunnel-Type:0 = VLAN Thu Sep 12 10:27:31 2019 : Debug: (0) Tunnel-Medium-Type:0 = IEEE-802 Thu Sep 12 10:27:31 2019 : Debug: (0) Tunnel-Private-Group-Id:0 = "3503" Thu Sep 12 10:27:31 2019 : Debug: (0) Cisco-AVPair = "audit-session-id=81d9fbf2000001465d79fc47" Thu Sep 12 10:27:31 2019 : Debug: (0) Acct-Session-Id = "5d79fc47/3c:15:c2:e8:40:fe/1259" Thu Sep 12 10:27:31 2019 : Debug: (0) session-state: No State attribute Thu Sep 12 10:27:31 2019 : Debug: (0) # Executing section authorize from file /etc/freeradius/sites-enabled/default Thu Sep 12 10:27:31 2019 : Debug: (0) authorize { Thu Sep 12 10:27:31 2019 : Debug: (0) policy filter_username { Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name) { Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name) -> TRUE Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name) { Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name =~ / /) { Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name =~ / /) -> FALSE Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name =~ /@[^@]*@/ ) { Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name =~ /@[^@]*@/ ) -> FALSE Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name =~ /\.\./ ) { Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name =~ /\.\./ ) -> FALSE Thu Sep 12 10:27:31 2019 : Debug: (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { Thu Sep 12 10:27:31 2019 : Debug: (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name =~ /\.$/) { Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name =~ /\.$/) -> FALSE Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name =~ /@\./) { Thu Sep 12 10:27:31 2019 : Debug: (0) if (&User-Name =~ /@\./) -> FALSE Thu Sep 12 10:27:31 2019 : Debug: (0) } # if (&User-Name) = notfound Thu Sep 12 10:27:31 2019 : Debug: (0) } # policy filter_username = notfound Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: calling preprocess (rlm_preprocess) Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: returned from preprocess (rlm_preprocess) Thu Sep 12 10:27:31 2019 : Debug: (0) [preprocess] = ok Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: calling chap (rlm_chap) Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: returned from chap (rlm_chap) Thu Sep 12 10:27:31 2019 : Debug: (0) [chap] = noop Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: calling mschap (rlm_mschap) Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: returned from mschap (rlm_mschap) Thu Sep 12 10:27:31 2019 : Debug: (0) [mschap] = noop Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: calling digest (rlm_digest) Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: returned from digest (rlm_digest) Thu Sep 12 10:27:31 2019 : Debug: (0) [digest] = noop Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: calling suffix (rlm_realm) Thu Sep 12 10:27:31 2019 : Debug: (0) suffix: Checking for suffix after "@" Thu Sep 12 10:27:31 2019 : Debug: (0) suffix: No '@' in User-Name = "3c15c2e840fe", looking up realm NULL Thu Sep 12 10:27:31 2019 : Debug: (0) suffix: No such realm "NULL" Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: returned from suffix (rlm_realm) Thu Sep 12 10:27:31 2019 : Debug: (0) [suffix] = noop Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: calling eap (rlm_eap) Thu Sep 12 10:27:31 2019 : Debug: (0) eap: No EAP-Message, not doing EAP Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: returned from eap (rlm_eap) Thu Sep 12 10:27:31 2019 : Debug: (0) [eap] = noop Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: calling files (rlm_files) Thu Sep 12 10:27:31 2019 : Debug: (0) files: users: Matched entry 3c15c2e840fe at line 2 Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: returned from files (rlm_files) Thu Sep 12 10:27:31 2019 : Debug: (0) [files] = ok Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: calling expiration (rlm_expiration) Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: returned from expiration (rlm_expiration) Thu Sep 12 10:27:31 2019 : Debug: (0) [expiration] = noop Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: calling logintime (rlm_logintime) Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: returned from logintime (rlm_logintime) Thu Sep 12 10:27:31 2019 : Debug: (0) [logintime] = noop Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: calling pap (rlm_pap) Thu Sep 12 10:27:31 2019 : WARNING: (0) pap: Auth-Type already set. Not setting to PAP Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[authorize]: returned from pap (rlm_pap) Thu Sep 12 10:27:31 2019 : Debug: (0) [pap] = noop Thu Sep 12 10:27:31 2019 : Debug: (0) } # authorize = ok Thu Sep 12 10:27:31 2019 : Debug: (0) Found Auth-Type = Accept Thu Sep 12 10:27:31 2019 : Debug: (0) Auth-Type = Accept, accepting the user Thu Sep 12 10:27:31 2019 : Debug: (0) # Executing section post-auth from file /etc/freeradius/sites-enabled/default Thu Sep 12 10:27:31 2019 : Debug: (0) post-auth { Thu Sep 12 10:27:31 2019 : Debug: (0) if (session-state:User-Name && reply:User-Name && request:User-Name && (reply:User-Name == request:User-Name)) { Thu Sep 12 10:27:31 2019 : Debug: (0) if (session-state:User-Name && reply:User-Name && request:User-Name && (reply:User-Name == request:User-Name)) -> FALSE Thu Sep 12 10:27:31 2019 : Debug: (0) update { Thu Sep 12 10:27:31 2019 : Debug: (0) No attributes updated for RHS &session-state: Thu Sep 12 10:27:31 2019 : Debug: (0) } # update = noop Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[post-auth]: calling sql (rlm_sql) Thu Sep 12 10:27:31 2019 : Debug: .query Thu Sep 12 10:27:31 2019 : Debug: Parsed xlat tree: Thu Sep 12 10:27:31 2019 : Debug: literal --> .query Thu Sep 12 10:27:31 2019 : Debug: (0) sql: EXPAND .query Thu Sep 12 10:27:31 2019 : Debug: (0) sql: --> .query Thu Sep 12 10:27:31 2019 : Debug: (0) sql: Using query template 'query' Thu Sep 12 10:27:31 2019 : Debug: rlm_sql (sql): Reserved connection (0) Thu Sep 12 10:27:31 2019 : Debug: %{User-Name} Thu Sep 12 10:27:31 2019 : Debug: Parsed xlat tree: Thu Sep 12 10:27:31 2019 : Debug: attribute --> User-Name Thu Sep 12 10:27:31 2019 : Debug: (0) sql: EXPAND %{User-Name} Thu Sep 12 10:27:31 2019 : Debug: (0) sql: --> 3c15c2e840fe Thu Sep 12 10:27:31 2019 : Debug: (0) sql: SQL-User-Name set to '3c15c2e840fe' Thu Sep 12 10:27:31 2019 : Debug: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') Thu Sep 12 10:27:31 2019 : Debug: Parsed xlat tree: Thu Sep 12 10:27:31 2019 : Debug: literal --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( ' Thu Sep 12 10:27:31 2019 : Debug: attribute --> SQL-User-Name Thu Sep 12 10:27:31 2019 : Debug: literal --> ', ' Thu Sep 12 10:27:31 2019 : Debug: XLAT-IF { Thu Sep 12 10:27:31 2019 : Debug: attribute --> User-Password Thu Sep 12 10:27:31 2019 : Debug: } Thu Sep 12 10:27:31 2019 : Debug: XLAT-ELSE { Thu Sep 12 10:27:31 2019 : Debug: attribute --> CHAP-Password Thu Sep 12 10:27:31 2019 : Debug: } Thu Sep 12 10:27:31 2019 : Debug: literal --> ', ' Thu Sep 12 10:27:31 2019 : Debug: attribute --> Packet-Type Thu Sep 12 10:27:31 2019 : Debug: literal --> ', ' Thu Sep 12 10:27:31 2019 : Debug: percent --> S Thu Sep 12 10:27:31 2019 : Debug: literal --> ') Thu Sep 12 10:27:31 2019 : Debug: (0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S') Thu Sep 12 10:27:31 2019 : Debug: (0) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '3c15c2e840fe', '3c15c2e840fe', 'Access-Accept', '2019-09-12 10:27:31') Thu Sep 12 10:27:31 2019 : Debug: (0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '3c15c2e840fe', '3c15c2e840fe', 'Access-Accept', '2019-09-12 10:27:31') Thu Sep 12 10:27:31 2019 : Debug: (0) sql: SQL query returned: success Thu Sep 12 10:27:31 2019 : Debug: (0) sql: 1 record(s) updated Thu Sep 12 10:27:31 2019 : Debug: rlm_sql (sql): Released connection (0) Thu Sep 12 10:27:31 2019 : Info: Need 5 more connections to reach 10 spares Thu Sep 12 10:27:31 2019 : Info: rlm_sql (sql): Opening additional connection (5), 1 of 27 pending slots used Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[post-auth]: returned from sql (rlm_sql) Thu Sep 12 10:27:31 2019 : Debug: (0) [sql] = ok Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[post-auth]: calling exec (rlm_exec) Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[post-auth]: returned from exec (rlm_exec) Thu Sep 12 10:27:31 2019 : Debug: (0) [exec] = noop Thu Sep 12 10:27:31 2019 : Debug: (0) policy remove_reply_message_if_eap { Thu Sep 12 10:27:31 2019 : Debug: (0) if (&reply:EAP-Message && &reply:Reply-Message) { Thu Sep 12 10:27:31 2019 : Debug: (0) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE Thu Sep 12 10:27:31 2019 : Debug: (0) else { Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[post-auth]: calling noop (rlm_always) Thu Sep 12 10:27:31 2019 : Debug: (0) modsingle[post-auth]: returned from noop (rlm_always) Thu Sep 12 10:27:31 2019 : Debug: (0) [noop] = noop Thu Sep 12 10:27:31 2019 : Debug: (0) } # else = noop Thu Sep 12 10:27:31 2019 : Debug: (0) } # policy remove_reply_message_if_eap = noop Thu Sep 12 10:27:31 2019 : Debug: (0) } # post-auth = ok Thu Sep 12 10:27:31 2019 : Debug: (0) Sent Access-Accept Id 26 from 129.217.228.164:1812 to 129.217.228.186:32776 length 0 Thu Sep 12 10:27:31 2019 : Debug: (0) Cisco-AVPair = "psk-mode=ascii" Thu Sep 12 10:27:31 2019 : Debug: (0) Cisco-AVPair = "psk=abcdefgh" Thu Sep 12 10:27:31 2019 : Debug: (0) Finished request -------------------------------- configuration with mysql -------------------------------- +----+--------------+--------------+----+----------------+ | id | username | attribute | op | value | +----+--------------+--------------+----+----------------+ | 1 | 3c15c2e840fe | Auth-Type | := | Accept | | 2 | 3c15c2e840fe | cisco-AVPair | == | psk-mode=ascii | | 3 | 3c15c2e840fe | cisco-AVPair | == | psk=abcdefgh | +----+--------------+--------------+----+----------------+ debug output: --------------------------------- Thu Sep 12 10:21:00 2019 : Debug: (9) Received Access-Request Id 25 from 129.217.228.186:32776 to 129.217.228.164:1812 length 255 Thu Sep 12 10:21:00 2019 : Debug: (9) User-Name = "3c15c2e840fe" Thu Sep 12 10:21:00 2019 : Debug: (9) Called-Station-Id = "70-ea-1a-84-18-c0:itmc-ipsk" Thu Sep 12 10:21:00 2019 : Debug: (9) Calling-Station-Id = "3c-15-c2-e8-40-fe" Thu Sep 12 10:21:00 2019 : Debug: (9) NAS-Port = 1 Thu Sep 12 10:21:00 2019 : Debug: (9) NAS-IP-Address = 129.217.251.242 Thu Sep 12 10:21:00 2019 : Debug: (9) NAS-Identifier = "wlc-staging" Thu Sep 12 10:21:00 2019 : Debug: (9) Airespace-Wlan-Id = 10 Thu Sep 12 10:21:00 2019 : Debug: (9) User-Password = "3c15c2e840fe" Thu Sep 12 10:21:00 2019 : Debug: (9) Service-Type = Call-Check Thu Sep 12 10:21:00 2019 : Debug: (9) Framed-MTU = 1300 Thu Sep 12 10:21:00 2019 : Debug: (9) NAS-Port-Type = Wireless-802.11 Thu Sep 12 10:21:00 2019 : Debug: (9) Tunnel-Type:0 = VLAN Thu Sep 12 10:21:00 2019 : Debug: (9) Tunnel-Medium-Type:0 = IEEE-802 Thu Sep 12 10:21:00 2019 : Debug: (9) Tunnel-Private-Group-Id:0 = "3503" Thu Sep 12 10:21:00 2019 : Debug: (9) Cisco-AVPair = "audit-session-id=81d9fbf2000001445d79fab6" Thu Sep 12 10:21:00 2019 : Debug: (9) Acct-Session-Id = "5d79fab6/3c:15:c2:e8:40:fe/1256" Thu Sep 12 10:21:00 2019 : Debug: (9) session-state: No State attribute Thu Sep 12 10:21:00 2019 : Debug: (9) # Executing section authorize from file /etc/freeradius/sites-enabled/default Thu Sep 12 10:21:00 2019 : Debug: (9) authorize { Thu Sep 12 10:21:00 2019 : Debug: (9) policy filter_username { Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name) { Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name) -> TRUE Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name) { Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name =~ / /) { Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name =~ / /) -> FALSE Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name =~ /@[^@]*@/ ) { Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name =~ /@[^@]*@/ ) -> FALSE Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name =~ /\.\./ ) { Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name =~ /\.\./ ) -> FALSE Thu Sep 12 10:21:00 2019 : Debug: (9) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { Thu Sep 12 10:21:00 2019 : Debug: (9) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name =~ /\.$/) { Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name =~ /\.$/) -> FALSE Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name =~ /@\./) { Thu Sep 12 10:21:00 2019 : Debug: (9) if (&User-Name =~ /@\./) -> FALSE Thu Sep 12 10:21:00 2019 : Debug: (9) } # if (&User-Name) = notfound Thu Sep 12 10:21:00 2019 : Debug: (9) } # policy filter_username = notfound Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: calling preprocess (rlm_preprocess) Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: returned from preprocess (rlm_preprocess) Thu Sep 12 10:21:00 2019 : Debug: (9) [preprocess] = ok Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: calling chap (rlm_chap) Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: returned from chap (rlm_chap) Thu Sep 12 10:21:00 2019 : Debug: (9) [chap] = noop Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: calling mschap (rlm_mschap) Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: returned from mschap (rlm_mschap) Thu Sep 12 10:21:00 2019 : Debug: (9) [mschap] = noop Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: calling digest (rlm_digest) Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: returned from digest (rlm_digest) Thu Sep 12 10:21:00 2019 : Debug: (9) [digest] = noop Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: calling suffix (rlm_realm) Thu Sep 12 10:21:00 2019 : Debug: (9) suffix: Checking for suffix after "@" Thu Sep 12 10:21:00 2019 : Debug: (9) suffix: No '@' in User-Name = "3c15c2e840fe", looking up realm NULL Thu Sep 12 10:21:00 2019 : Debug: (9) suffix: No such realm "NULL" Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: returned from suffix (rlm_realm) Thu Sep 12 10:21:00 2019 : Debug: (9) [suffix] = noop Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: calling eap (rlm_eap) Thu Sep 12 10:21:00 2019 : Debug: (9) eap: No EAP-Message, not doing EAP Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: returned from eap (rlm_eap) Thu Sep 12 10:21:00 2019 : Debug: (9) [eap] = noop Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: calling files (rlm_files) Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: returned from files (rlm_files) Thu Sep 12 10:21:00 2019 : Debug: (9) [files] = noop Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: calling sql (rlm_sql) Thu Sep 12 10:21:00 2019 : Debug: %{User-Name} Thu Sep 12 10:21:00 2019 : Debug: Parsed xlat tree: Thu Sep 12 10:21:00 2019 : Debug: attribute --> User-Name Thu Sep 12 10:21:00 2019 : Debug: (9) sql: EXPAND %{User-Name} Thu Sep 12 10:21:00 2019 : Debug: (9) sql: --> 3c15c2e840fe Thu Sep 12 10:21:00 2019 : Debug: (9) sql: SQL-User-Name set to '3c15c2e840fe' Thu Sep 12 10:21:00 2019 : Debug: rlm_sql (sql): Reserved connection (10) Thu Sep 12 10:21:00 2019 : Debug: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id Thu Sep 12 10:21:00 2019 : Debug: Parsed xlat tree: Thu Sep 12 10:21:00 2019 : Debug: literal --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = ' Thu Sep 12 10:21:00 2019 : Debug: attribute --> SQL-User-Name Thu Sep 12 10:21:00 2019 : Debug: literal --> ' ORDER BY id Thu Sep 12 10:21:00 2019 : Debug: (9) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id Thu Sep 12 10:21:00 2019 : Debug: (9) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = '3c15c2e840fe' ORDER BY id Thu Sep 12 10:21:00 2019 : Debug: (9) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '3c15c2e840fe' ORDER BY id Thu Sep 12 10:21:00 2019 : The 'rlm_sql_null' driver CANNOT be used for SELECTS. Thu Sep 12 10:21:00 2019 : Please update the 'sql' module configuration to use a real database. Thu Sep 12 10:21:00 2019 : Set 'driver = ...' to the database you want to use. Thu Sep 12 10:21:00 2019 : Debug: (9) sql: ... falling-through to group processing Thu Sep 12 10:21:00 2019 : Debug: SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority Thu Sep 12 10:21:00 2019 : Debug: Parsed xlat tree: Thu Sep 12 10:21:00 2019 : Debug: literal --> SELECT groupname FROM radusergroup WHERE username = ' Thu Sep 12 10:21:00 2019 : Debug: attribute --> SQL-User-Name Thu Sep 12 10:21:00 2019 : Debug: literal --> ' ORDER BY priority Thu Sep 12 10:21:00 2019 : Debug: rlm_sql (sql): Reserved connection (3) Thu Sep 12 10:21:00 2019 : Debug: rlm_sql (sql): Released connection (3) Thu Sep 12 10:21:00 2019 : Debug: (9) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority Thu Sep 12 10:21:00 2019 : Debug: (9) sql: --> SELECT groupname FROM radusergroup WHERE username = '3c15c2e840fe' ORDER BY priority Thu Sep 12 10:21:00 2019 : Debug: (9) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = '3c15c2e840fe' ORDER BY priority Thu Sep 12 10:21:00 2019 : The 'rlm_sql_null' driver CANNOT be used for SELECTS. Thu Sep 12 10:21:00 2019 : Please update the 'sql' module configuration to use a real database. Thu Sep 12 10:21:00 2019 : Set 'driver = ...' to the database you want to use. Thu Sep 12 10:21:00 2019 : Debug: (9) sql: User not found in any groups Thu Sep 12 10:21:00 2019 : Debug: (9) sql: ... falling-through to profile processing Thu Sep 12 10:21:00 2019 : Debug: rlm_sql (sql): Released connection (10) Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: returned from sql (rlm_sql) Thu Sep 12 10:21:00 2019 : Debug: (9) [sql] = notfound Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: calling expiration (rlm_expiration) Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: returned from expiration (rlm_expiration) Thu Sep 12 10:21:00 2019 : Debug: (9) [expiration] = noop Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: calling logintime (rlm_logintime) Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: returned from logintime (rlm_logintime) Thu Sep 12 10:21:00 2019 : Debug: (9) [logintime] = noop Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: calling pap (rlm_pap) Thu Sep 12 10:21:00 2019 : WARNING: (9) pap: No "known good" password found for the user. Not setting Auth-Type Thu Sep 12 10:21:00 2019 : WARNING: (9) pap: Authentication will fail unless a "known good" password is available Thu Sep 12 10:21:00 2019 : Debug: (9) modsingle[authorize]: returned from pap (rlm_pap) Thu Sep 12 10:21:00 2019 : Debug: (9) [pap] = noop Thu Sep 12 10:21:00 2019 : Debug: (9) } # authorize = ok Thu Sep 12 10:21:00 2019 : ERROR: (9) No Auth-Type found: rejecting the user via Post-Auth-Type = Reject Thu Sep 12 10:21:00 2019 : Debug: (9) Failed to authenticate the user Mit freundlichen Grüßen Hans Bornemann TU Dortmund ITMC / Datanet Tel. 0231 7552132
On Sep 12, 2019, at 4:43 AM, Bornemann, Hans <hans.bornemann@tu-dortmund.de> wrote
we are testing a wlan with "private preshared keys". In the cisco wlancontroller world
it works with the AAA override feature and some entries in the radius database.
If I transfer this entries to the mysql database, the aut-type was not set.
What is missing?
Read the debug output. And use "radiusd -X", not "radiusd -Xx". And don't post the debug output double-spaced.
I tested it with the default configuration, the sql module was enabled.
You didn't configure the SQL module correctly. I've edited the debug output below to look like it was done with "radiusd -X":
(9) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '3c15c2e840fe' ORDER BY id The 'rlm_sql_null' driver CANNOT be used for SELECTS. Please update the 'sql' module configuration to use a real database. Set 'driver = ...' to the database you want to use.
This is why we tell everyone to READ the debug output. It tells you EXACTLY what is going wrong, and how to fix it. Please READ the documentation, and follow the instructions. It's MUCH more efficient than doing random things, and wondering why the server doesn't work. Alan DeKok.
Thanks, but after fixing the sql module, it is the same: Auth-Type not fournd (0) authorize { (0) policy filter_username { (0) if (&User-Name) { (0) if (&User-Name) -> TRUE (0) if (&User-Name) { (0) if (&User-Name =~ / /) { (0) if (&User-Name =~ / /) -> FALSE (0) if (&User-Name =~ /@[^@]*@/ ) { (0) if (&User-Name =~ /@[^@]*@/ ) -> FALSE (0) if (&User-Name =~ /\.\./ ) { (0) if (&User-Name =~ /\.\./ ) -> FALSE (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) { (0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE (0) if (&User-Name =~ /\.$/) { (0) if (&User-Name =~ /\.$/) -> FALSE (0) if (&User-Name =~ /@\./) { (0) if (&User-Name =~ /@\./) -> FALSE (0) } # if (&User-Name) = notfound (0) } # policy filter_username = notfound (0) [preprocess] = ok (0) [chap] = noop (0) [mschap] = noop (0) [digest] = noop (0) suffix: Checking for suffix after "@" (0) suffix: No '@' in User-Name = "3c15c2e840fe", looking up realm NULL (0) suffix: No such realm "NULL" (0) [suffix] = noop (0) eap: No EAP-Message, not doing EAP (0) [eap] = noop (0) sql: EXPAND %{User-Name} (0) sql: --> 3c15c2e840fe (0) sql: SQL-User-Name set to '3c15c2e840fe' rlm_sql (sql): Reserved connection (0) (0) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id (0) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = '3c15c2e840fe' ORDER BY id (0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '3c15c2e840fe' ORDER BY id (0) sql: User found in radcheck table rlm_sql (sql): Reserved connection (1) rlm_sql (sql): Released connection (1) Need 6 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (5), 1 of 27 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'radius' on Localhost via UNIX socket, server version 5.5.5-10.1.41-MariaDB-0ubuntu0.18.04.1, protocol version 10 (0) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (0) sql: --> SELECT groupname FROM radusergroup WHERE username = '3c15c2e840fe' ORDER BY priority (0) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = '3c15c2e840fe' ORDER BY priority (0) sql: User not found in any groups rlm_sql (sql): Released connection (0) (0) [sql] = noop (0) [expiration] = noop (0) [logintime] = noop (0) pap: WARNING: No "known good" password found for the user. Not setting Auth-Type (0) pap: WARNING: Authentication will fail unless a "known good" password is available (0) [pap] = noop (0) } # authorize = ok (0) ERROR: No Auth-Type found: rejecting the user via Post-Auth-Type = Reject (0) Failed to authenticate the user -----Ursprüngliche Nachricht----- Von: Freeradius-Users <freeradius-users-bounces+hans.bornemann=tu-dortmund.de@lists.freeradius.org> Im Auftrag von Alan DeKok Gesendet: Donnerstag, 12. September 2019 13:08 An: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Betreff: Re: how to transfer from "users file to sql" On Sep 12, 2019, at 4:43 AM, Bornemann, Hans <hans.bornemann@tu-dortmund.de> wrote
we are testing a wlan with "private preshared keys". In the cisco wlancontroller world
it works with the AAA override feature and some entries in the radius database.
If I transfer this entries to the mysql database, the aut-type was not set.
What is missing?
Read the debug output. And use "radiusd -X", not "radiusd -Xx". And don't post the debug output double-spaced.
I tested it with the default configuration, the sql module was enabled.
You didn't configure the SQL module correctly. I've edited the debug output below to look like it was done with "radiusd -X":
(9) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '3c15c2e840fe' ORDER BY id The 'rlm_sql_null' driver CANNOT be used for SELECTS. Please update the 'sql' module configuration to use a real database. Set 'driver = ...' to the database you want to use.
This is why we tell everyone to READ the debug output. It tells you EXACTLY what is going wrong, and how to fix it. Please READ the documentation, and follow the instructions. It's MUCH more efficient than doing random things, and wondering why the server doesn't work. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Sep 13, 2019, at 3:52 AM, Bornemann, Hans <hans.bornemann@tu-dortmund.de> wrote:
Thanks, but after fixing the sql module, it is the same: Auth-Type not fournd
(0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '3c15c2e840fe' ORDER BY id
What does that show you? Odds are it doesn't include "Auth-Type := Accept". Alan DeKok.
Hi, the sql statement shows the correct output, i think. SELECT id, username, attribute, value, op FROM radcheck WHERE username = '3c15c2e840fe' ORDER BY id; | id | username | attribute | value | op | | 1 | 3c15c2e840fe | Auth-Type | Accept | := | | 2 | 3c15c2e840fe | cisco-AVPair | psk-mode=ascii | == | | 3 | 3c15c2e840fe | cisco-AVPair | psk=abcdefgh | == | -----Ursprüngliche Nachricht----- Von: Freeradius-Users <freeradius-users-bounces+hans.bornemann=tu-dortmund.de@lists.freeradius.org> Im Auftrag von Alan DeKok Gesendet: Freitag, 13. September 2019 14:52 An: FreeRadius users mailing list <freeradius-users@lists.freeradius.org> Betreff: Re: how to transfer from "users file to sql" On Sep 13, 2019, at 3:52 AM, Bornemann, Hans <hans.bornemann@tu-dortmund.de> wrote:
Thanks, but after fixing the sql module, it is the same: Auth-Type not fournd
(0) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '3c15c2e840fe' ORDER BY id
What does that show you? Odds are it doesn't include "Auth-Type := Accept". Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Sep 13, 2019, at 9:06 AM, Bornemann, Hans <hans.bornemann@tu-dortmund.de> wrote:
the sql statement shows the correct output, i think.
SELECT id, username, attribute, value, op FROM radcheck WHERE username = '3c15c2e840fe' ORDER BY id;
| id | username | attribute | value | op |
| 1 | 3c15c2e840fe | Auth-Type | Accept | := | | 2 | 3c15c2e840fe | cisco-AVPair | psk-mode=ascii | == | | 3 | 3c15c2e840fe | cisco-AVPair | psk=abcdefgh | == |
The Cisco-AVPair checks aren't matching. And this does *not* match the "users" file entry you posted earlier: 3c15c2e840fe Auth-Type := Accept cisco-AVPair = "psk-mode=ascii", cisco-AVPair = "psk=abcdefgh" The cisco-avpair lines go into the *radreply* table. And use the "=" operator, not "==". The SQL documentation on the Wiki goes into this in detail. Alan DeKok.
participants (2)
-
Alan DeKok -
Bornemann, Hans