Hello, This is the background: - Up and running, production, server. Ubuntu 8.04.3 - Working like a charm for almost a year now as LAMP server, OpenLDAP server, samba PDC, DNS server, OpenVPN server, and a few other server tasks. - ChilliSpot hotspot completely up and running well. FreeRADIUS with MySQL backend used for this purpose, working well. Captive portal customised and working just like it should. This server hosts a set of PHP scripts called "PHP-Residence", which is a GNU/GPL-licensed booking system and reservation management for hotel and tourist apartments. The database is MySQL. The idea is to allow access to the hotspot to all clients who are staying at the apartments. For such, authorisation would have to check for the following: IF - the username is in the clients database, AND - the password matches the reservation code, AND - the current date is within the time-frame where the guest is staying at the apartments, THEN AUTHORISE ELSE DENY Currently, if someone who stays at the aparments wants to connect to the hotspot, I have to create usernames and passwords manually. It is a tedious work to do, to create a new username and a new password everytime someone arrives. And in addition I find it particularly pointless, because I already have a MySQL database from which I can read the necessary information in order to accept or deny a connection. The idea would be to have PHP-Residence to automatically populate the radcheck table with customer name, reservation code, arrival date, and departure date. At login time, the customer would be asked for his name and his reservation code as username and password. Then FreeRADIUS would check if those match, and also if the current date is equal or greater than the arrival date AND if the current date is equal or lower than the departure date. That last bit is the tricky bit. I haven't got the smallest clue about MySQL syntax, nor FreeRADIUS operation. I have setup the hotspot according to Ubuntu documentation, and as I said it works great, only I do not know how to get it to check if the current date is valid for the specified username/password. I suppose that I can figure out how to get PHP-Residence to populate the radcheck table (if I can't then I'll come back for help). But I really don't know how to check, in addition, for the current date being valid or not. I have supposed that I have to add attributes to the radcheck table. As initially setup, radcheck contains UserName, Attribute (password), op (operand is == because we need an exact match), and Value (which is the password itself). I have added extra fields: - Attribute2 (DateBegin) - op2 (>= because current date must be greater or equal to the date when the customer arrives) - Value2 (contains the date the reservation begins) - field type is date -Attribute3 (DateEnd) - op3 (<= because current date must be lower or equal to the date when the customer departs) - Value3 (contains de date the reservation ends) - field type is date But how do I have to type the query in order to get this checked? How do I dump this into the sql.conf file? Million thanks!
Hello, Problem solved. A few hours on Google did the trick. The thing is that I had never used any SQL and I did not know a word about it. So, in case anyone is in my situation, this is what I did. RADCHECK table has these fields as standard: Username, Attribute, op, and Value. I added two new fields after that: StartDay and EndDay. The first contains the first day when the connection can be authorised and the other one contains the last day. The query is: Code: authorize_check_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authcheck_table} \ WHERE Username='%{SQL-User-Name}' AND curdate()>=StartDay AND curdate()<=EndDay \ ORDER BY id" authorize_reply_query = "SELECT id, UserName, Attribute, Value, op \ FROM ${authreply_table} \ WHERE Username='%{SQL-User-Name}' AND curdate()>=StartDay AND curdate()<=EndDay \ ORDER BY id" It's easy and straightforward, but not so much so when it's the first time you are to type a MySQL query and have to Google even for the basics. Now I am going to make the booking engine to write to RADCHECK in the RADIUS database. A new reservation will create a user and its password (name and reservation code), and insert arrival and departure days for the customer in order to authorise the connection only when the guest is staying at the apartments. No effort involved, all automatic, all the way it should be.
Greetings Mr Awe(some?) , ;-) this message is a little bit OT but still relevant to your statement that you ve worked out a Chillispot "completely up and running well". I hope you will help me out because as of now I am swimming in an ocean of doubts as to if I can troubleshoot my Freeradius+MySQL stack. A little bit of background: ----------------------------- -I am not to Freeadius (have done it before with a flat "user" file) -I am new to Mysql(I can only do some basic commands like, creating,deleting, manipulate databases, tables and records ). -I am trying to put up a hotspot solution using PHPMyPrepaid+Freeradius+Mysql -I have already installed LAMP on my centos 5.4. Mysql works fine from command line and PHPMyadmin. Freeradius is installed and "user" file works perfectly. Troubles start when I want to use mysql as backend to freeradius authentication. The error message I am getting to me seems to be related to MySQL but I can't figure out: Sorry to paste here output of the freeradius debug but I guess I have to do it so that you can advise Further info can be provided if needed(configs file, various outputs). Thank you in advance for your precious help! PM me at mndabarasa_at_gmail_dot_com [root@192 mike]# /usr/local/sbin/radiusd -XX Fri Apr 2 19:42:33 2010 : Info: FreeRADIUS Version 2.1.8, for host i686-pc-linux-gnu, built on Apr 2 2010 at 14:03:42 Fri Apr 2 19:42:33 2010 : Info: Copyright (C) 1999-2009 The FreeRADIUS server project and contributors. Fri Apr 2 19:42:33 2010 : Info: There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A Fri Apr 2 19:42:33 2010 : Info: PARTICULAR PURPOSE. Fri Apr 2 19:42:33 2010 : Info: You may redistribute copies of FreeRADIUS under the terms of the Fri Apr 2 19:42:33 2010 : Info: GNU General Public License v2. Fri Apr 2 19:42:33 2010 : Info: Starting - reading configuration files ... Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/radiusd.conf Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/clients.conf Fri Apr 2 19:42:33 2010 : Debug: including files in directory /usr/local/etc/raddb/modules/ Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/sradutmp Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/expiration Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/files Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/acct_unique Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/cui Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/ntlm_auth Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/linelog Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/preprocess Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/chap Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/pap Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/mac2ip Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/digest Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/policy Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/sql_log Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/attr_rewrite Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/etc_group Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/smsotp Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/mschap Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/perl Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/otp Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/radutmp Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/krb5 Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/detail Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/ippool Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/checkval Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/detail.example.com Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/ldap Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/passwd Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/inner-eap Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/detail.log Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/realm Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/echo Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/attr_filter Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/smbpasswd Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/unix Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/expr Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/mac2vlan Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/wimax Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/pam Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/exec Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/logintime Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/always Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/counter Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/modules/sqlcounter_expire_on_login Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/eap.conf Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/sql.conf Fri Apr 2 19:42:33 2010 : Debug: including configuration file /usr/local/etc/raddb/sites-available/default Fri Apr 2 19:42:33 2010 : Debug: main { Fri Apr 2 19:42:33 2010 : Debug: allow_core_dumps = no Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: including dictionary file /usr/local/etc/raddb/dictionary Fri Apr 2 19:42:33 2010 : Debug: main { Fri Apr 2 19:42:33 2010 : Debug: prefix = "/usr/local" Fri Apr 2 19:42:33 2010 : Debug: localstatedir = "/usr/local/var" Fri Apr 2 19:42:33 2010 : Debug: logdir = "/usr/local/var/log/radius" Fri Apr 2 19:42:33 2010 : Debug: libdir = "/usr/local/lib" Fri Apr 2 19:42:33 2010 : Debug: radacctdir = "/usr/local/var/log/radius/radacct" Fri Apr 2 19:42:33 2010 : Debug: hostname_lookups = no Fri Apr 2 19:42:33 2010 : Debug: max_request_time = 30 Fri Apr 2 19:42:33 2010 : Debug: cleanup_delay = 5 Fri Apr 2 19:42:33 2010 : Debug: max_requests = 4096 Fri Apr 2 19:42:33 2010 : Debug: pidfile = "/usr/local/var/run/radiusd/radiusd.pid" Fri Apr 2 19:42:33 2010 : Debug: checkrad = "/usr/local/sbin/checkrad" Fri Apr 2 19:42:33 2010 : Debug: debug_level = 0 Fri Apr 2 19:42:33 2010 : Debug: proxy_requests = no Fri Apr 2 19:42:33 2010 : Debug: log { Fri Apr 2 19:42:33 2010 : Debug: stripped_names = no Fri Apr 2 19:42:33 2010 : Debug: auth = yes Fri Apr 2 19:42:33 2010 : Debug: auth_badpass = no Fri Apr 2 19:42:33 2010 : Debug: auth_goodpass = no Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: security { Fri Apr 2 19:42:33 2010 : Debug: max_attributes = 200 Fri Apr 2 19:42:33 2010 : Debug: reject_delay = 1 Fri Apr 2 19:42:33 2010 : Debug: status_server = yes Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: radiusd: #### Loading Realms and Home Servers #### Fri Apr 2 19:42:33 2010 : Debug: radiusd: #### Loading Clients #### Fri Apr 2 19:42:33 2010 : Debug: client 127.0.0.1 { Fri Apr 2 19:42:33 2010 : Debug: ipaddr = 127.0.0.1 Fri Apr 2 19:42:33 2010 : Debug: require_message_authenticator = no Fri Apr 2 19:42:33 2010 : Debug: secret = "h0t5p0t5" Fri Apr 2 19:42:33 2010 : Debug: shortname = "127.0.0.1" Fri Apr 2 19:42:33 2010 : Debug: nastype = "other" Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: client 192.168.1.1 { Fri Apr 2 19:42:33 2010 : Debug: require_message_authenticator = no Fri Apr 2 19:42:33 2010 : Debug: secret = "h0t5p0t5" Fri Apr 2 19:42:33 2010 : Debug: shortname = "nanostation1" Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: client 192.168.1.0/24 { Fri Apr 2 19:42:33 2010 : Debug: require_message_authenticator = no Fri Apr 2 19:42:33 2010 : Debug: secret = "h0t5p0t5" Fri Apr 2 19:42:33 2010 : Debug: shortname = "my_self-localhost" Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: radiusd: #### Instantiating modules #### Fri Apr 2 19:42:33 2010 : Debug: instantiate { Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_exec, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_exec Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating exec Fri Apr 2 19:42:33 2010 : Debug: exec { Fri Apr 2 19:42:33 2010 : Debug: wait = no Fri Apr 2 19:42:33 2010 : Debug: input_pairs = "request" Fri Apr 2 19:42:33 2010 : Debug: shell_escape = yes Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_expr, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_expr Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating expr Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_expiration, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_expiration Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating expiration Fri Apr 2 19:42:33 2010 : Debug: expiration { Fri Apr 2 19:42:33 2010 : Debug: reply-message = "Password Has Expired " Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_logintime, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_logintime Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating logintime Fri Apr 2 19:42:33 2010 : Debug: logintime { Fri Apr 2 19:42:33 2010 : Debug: reply-message = "You are calling outside your allowed timespan " Fri Apr 2 19:42:33 2010 : Debug: minimum-timeout = 60 Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: radiusd: #### Loading Virtual Servers #### Fri Apr 2 19:42:33 2010 : Debug: server { Fri Apr 2 19:42:33 2010 : Debug: modules { Fri Apr 2 19:42:33 2010 : Debug: Module: Checking authenticate {...} for more modules to load Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_pap, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_pap Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating pap Fri Apr 2 19:42:33 2010 : Debug: pap { Fri Apr 2 19:42:33 2010 : Debug: encryption_scheme = "auto" Fri Apr 2 19:42:33 2010 : Debug: auto_header = no Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_chap, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_chap Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating chap Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_mschap, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_mschap Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating mschap Fri Apr 2 19:42:33 2010 : Debug: mschap { Fri Apr 2 19:42:33 2010 : Debug: use_mppe = yes Fri Apr 2 19:42:33 2010 : Debug: require_encryption = no Fri Apr 2 19:42:33 2010 : Debug: require_strong = no Fri Apr 2 19:42:33 2010 : Debug: with_ntdomain_hack = no Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_eap, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_eap Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating eap Fri Apr 2 19:42:33 2010 : Debug: eap { Fri Apr 2 19:42:33 2010 : Debug: default_eap_type = "md5" Fri Apr 2 19:42:33 2010 : Debug: timer_expire = 60 Fri Apr 2 19:42:33 2010 : Debug: ignore_unknown_eap_types = no Fri Apr 2 19:42:33 2010 : Debug: cisco_accounting_username_bug = no Fri Apr 2 19:42:33 2010 : Debug: max_sessions = 4096 Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to sub-module rlm_eap_md5 Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating eap-md5 Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to sub-module rlm_eap_leap Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating eap-leap Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to sub-module rlm_eap_gtc Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating eap-gtc Fri Apr 2 19:42:33 2010 : Debug: gtc { Fri Apr 2 19:42:33 2010 : Debug: challenge = "Password: " Fri Apr 2 19:42:33 2010 : Debug: auth_type = "PAP" Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to sub-module rlm_eap_tls Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating eap-tls Fri Apr 2 19:42:33 2010 : Debug: tls { Fri Apr 2 19:42:33 2010 : Debug: rsa_key_exchange = no Fri Apr 2 19:42:33 2010 : Debug: dh_key_exchange = yes Fri Apr 2 19:42:33 2010 : Debug: rsa_key_length = 512 Fri Apr 2 19:42:33 2010 : Debug: dh_key_length = 512 Fri Apr 2 19:42:33 2010 : Debug: verify_depth = 0 Fri Apr 2 19:42:33 2010 : Debug: pem_file_type = yes Fri Apr 2 19:42:33 2010 : Debug: private_key_file = "/usr/local/etc/raddb/certs/server.pem" Fri Apr 2 19:42:33 2010 : Debug: certificate_file = "/usr/local/etc/raddb/certs/server.pem" Fri Apr 2 19:42:33 2010 : Debug: CA_file = "/usr/local/etc/raddb/certs/ca.pem" Fri Apr 2 19:42:33 2010 : Debug: private_key_password = "whatever" Fri Apr 2 19:42:33 2010 : Debug: dh_file = "/usr/local/etc/raddb/certs/dh" Fri Apr 2 19:42:33 2010 : Debug: random_file = "/usr/local/etc/raddb/certs/random" Fri Apr 2 19:42:33 2010 : Debug: fragment_size = 1024 Fri Apr 2 19:42:33 2010 : Debug: include_length = yes Fri Apr 2 19:42:33 2010 : Debug: check_crl = no Fri Apr 2 19:42:33 2010 : Debug: cipher_list = "DEFAULT" Fri Apr 2 19:42:33 2010 : Debug: make_cert_command = "/usr/local/etc/raddb/certs/bootstrap" Fri Apr 2 19:42:33 2010 : Debug: cache { Fri Apr 2 19:42:33 2010 : Debug: enable = no Fri Apr 2 19:42:33 2010 : Debug: lifetime = 24 Fri Apr 2 19:42:33 2010 : Debug: max_entries = 255 Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to sub-module rlm_eap_ttls Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating eap-ttls Fri Apr 2 19:42:33 2010 : Debug: ttls { Fri Apr 2 19:42:33 2010 : Debug: default_eap_type = "md5" Fri Apr 2 19:42:33 2010 : Debug: copy_request_to_tunnel = no Fri Apr 2 19:42:33 2010 : Debug: use_tunneled_reply = no Fri Apr 2 19:42:33 2010 : Debug: virtual_server = "inner-tunnel" Fri Apr 2 19:42:33 2010 : Debug: include_length = yes Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to sub-module rlm_eap_peap Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating eap-peap Fri Apr 2 19:42:33 2010 : Debug: peap { Fri Apr 2 19:42:33 2010 : Debug: default_eap_type = "mschapv2" Fri Apr 2 19:42:33 2010 : Debug: copy_request_to_tunnel = no Fri Apr 2 19:42:33 2010 : Debug: use_tunneled_reply = no Fri Apr 2 19:42:33 2010 : Debug: proxy_tunneled_request_as_eap = yes Fri Apr 2 19:42:33 2010 : Debug: virtual_server = "inner-tunnel" Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to sub-module rlm_eap_mschapv2 Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating eap-mschapv2 Fri Apr 2 19:42:33 2010 : Debug: mschapv2 { Fri Apr 2 19:42:33 2010 : Debug: with_ntdomain_hack = no Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_attr_filter, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_attr_filter Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating attr_filter.access_challenge Fri Apr 2 19:42:33 2010 : Debug: attr_filter attr_filter.access_challenge { Fri Apr 2 19:42:33 2010 : Debug: attrsfile = "/usr/local/etc/raddb/attrs.access_challenge" Fri Apr 2 19:42:33 2010 : Debug: key = "%{User-Name}" Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: Module: Checking authorize {...} for more modules to load Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_preprocess, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_preprocess Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating preprocess Fri Apr 2 19:42:33 2010 : Debug: preprocess { Fri Apr 2 19:42:33 2010 : Debug: huntgroups = "/usr/local/etc/raddb/huntgroups" Fri Apr 2 19:42:33 2010 : Debug: hints = "/usr/local/etc/raddb/hints" Fri Apr 2 19:42:33 2010 : Debug: with_ascend_hack = no Fri Apr 2 19:42:33 2010 : Debug: ascend_channels_per_line = 23 Fri Apr 2 19:42:33 2010 : Debug: with_ntdomain_hack = no Fri Apr 2 19:42:33 2010 : Debug: with_specialix_jetstream_hack = no Fri Apr 2 19:42:33 2010 : Debug: with_cisco_vsa_hack = no Fri Apr 2 19:42:33 2010 : Debug: with_alvarion_vsa_hack = no Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_detail, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_detail Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating auth_log Fri Apr 2 19:42:33 2010 : Debug: detail auth_log { Fri Apr 2 19:42:33 2010 : Debug: detailfile = "/usr/local/var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d" Fri Apr 2 19:42:33 2010 : Debug: header = "%t" Fri Apr 2 19:42:33 2010 : Debug: detailperm = 384 Fri Apr 2 19:42:33 2010 : Debug: dirperm = 493 Fri Apr 2 19:42:33 2010 : Debug: locking = no Fri Apr 2 19:42:33 2010 : Debug: log_packet_header = no Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_realm, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_realm Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating suffix Fri Apr 2 19:42:33 2010 : Debug: realm suffix { Fri Apr 2 19:42:33 2010 : Debug: format = "suffix" Fri Apr 2 19:42:33 2010 : Debug: delimiter = "@" Fri Apr 2 19:42:33 2010 : Debug: ignore_default = no Fri Apr 2 19:42:33 2010 : Debug: ignore_null = no Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_sql, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_sql Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating sql Fri Apr 2 19:42:33 2010 : Debug: sql { Fri Apr 2 19:42:33 2010 : Debug: driver = "rlm_sql_mysql" Fri Apr 2 19:42:33 2010 : Debug: server = "localhost" Fri Apr 2 19:42:33 2010 : Debug: port = "" Fri Apr 2 19:42:33 2010 : Debug: login = "freeradius" Fri Apr 2 19:42:33 2010 : Debug: password = "fr33" Fri Apr 2 19:42:33 2010 : Debug: radius_db = "radius" Fri Apr 2 19:42:33 2010 : Debug: read_groups = yes Fri Apr 2 19:42:33 2010 : Debug: sqltrace = no Fri Apr 2 19:42:33 2010 : Debug: sqltracefile = "/usr/local/var/log/radius/sqltrace.sql" Fri Apr 2 19:42:33 2010 : Debug: readclients = no Fri Apr 2 19:42:33 2010 : Debug: deletestalesessions = yes Fri Apr 2 19:42:33 2010 : Debug: num_sql_socks = 5 Fri Apr 2 19:42:33 2010 : Debug: lifetime = 0 Fri Apr 2 19:42:33 2010 : Debug: max_queries = 0 Fri Apr 2 19:42:33 2010 : Debug: sql_user_name = "" Fri Apr 2 19:42:33 2010 : Debug: default_user_profile = "" Fri Apr 2 19:42:33 2010 : Debug: nas_query = "SELECT id,nasname,shortname,type,secret FROM nas" Fri Apr 2 19:42:33 2010 : Debug: authorize_check_query = "" Fri Apr 2 19:42:33 2010 : Debug: authorize_group_check_query = "" Fri Apr 2 19:42:33 2010 : Debug: authorize_group_reply_query = "" Fri Apr 2 19:42:33 2010 : Debug: accounting_onoff_query = "" Fri Apr 2 19:42:33 2010 : Debug: accounting_update_query = "" Fri Apr 2 19:42:33 2010 : Debug: accounting_update_query_alt = "" Fri Apr 2 19:42:33 2010 : Debug: accounting_start_query = "" Fri Apr 2 19:42:33 2010 : Debug: accounting_start_query_alt = "" Fri Apr 2 19:42:33 2010 : Debug: accounting_stop_query = "" Fri Apr 2 19:42:33 2010 : Debug: accounting_stop_query_alt = "" Fri Apr 2 19:42:33 2010 : Debug: connect_failure_retry_delay = 60 Fri Apr 2 19:42:33 2010 : Debug: simul_count_query = "" Fri Apr 2 19:42:33 2010 : Debug: simul_verify_query = "" Fri Apr 2 19:42:33 2010 : Debug: postauth_query = "" Fri Apr 2 19:42:33 2010 : Debug: safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Info: rlm_sql (sql): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked Fri Apr 2 19:42:33 2010 : Info: rlm_sql (sql): Attempting to connect to freeradius@localhost:/radius Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): starting 0 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): Attempting to connect rlm_sql_mysql #0 Fri Apr 2 19:42:33 2010 : Info: rlm_sql_mysql: Starting connect to MySQL server for #0 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): Connected new DB handle, #0 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): starting 1 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): Attempting to connect rlm_sql_mysql #1 Fri Apr 2 19:42:33 2010 : Info: rlm_sql_mysql: Starting connect to MySQL server for #1 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): Connected new DB handle, #1 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): starting 2 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): Attempting to connect rlm_sql_mysql #2 Fri Apr 2 19:42:33 2010 : Info: rlm_sql_mysql: Starting connect to MySQL server for #2 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): Connected new DB handle, #2 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): starting 3 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): Attempting to connect rlm_sql_mysql #3 Fri Apr 2 19:42:33 2010 : Info: rlm_sql_mysql: Starting connect to MySQL server for #3 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): Connected new DB handle, #3 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): starting 4 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): Attempting to connect rlm_sql_mysql #4 Fri Apr 2 19:42:33 2010 : Info: rlm_sql_mysql: Starting connect to MySQL server for #4 Fri Apr 2 19:42:33 2010 : Debug: rlm_sql (sql): Connected new DB handle, #4 Fri Apr 2 19:42:33 2010 : Debug: Module: Checking preacct {...} for more modules to load Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_acct_unique, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_acct_unique Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating acct_unique Fri Apr 2 19:42:33 2010 : Debug: acct_unique { Fri Apr 2 19:42:33 2010 : Debug: key = "User-Name, Acct-Session-Id, NAS-IP-Address, Client-IP-Address, NAS-Port" Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_files, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_files Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating files Fri Apr 2 19:42:33 2010 : Debug: files { Fri Apr 2 19:42:33 2010 : Debug: usersfile = "/usr/local/etc/raddb/users" Fri Apr 2 19:42:33 2010 : Debug: acctusersfile = "/usr/local/etc/raddb/acct_users" Fri Apr 2 19:42:33 2010 : Debug: preproxy_usersfile = "/usr/local/etc/raddb/preproxy_users" Fri Apr 2 19:42:33 2010 : Debug: compat = "no" Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: Module: Checking accounting {...} for more modules to load Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating detail Fri Apr 2 19:42:33 2010 : Debug: detail { Fri Apr 2 19:42:33 2010 : Debug: detailfile = "/usr/local/var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d" Fri Apr 2 19:42:33 2010 : Debug: header = "%t" Fri Apr 2 19:42:33 2010 : Debug: detailperm = 384 Fri Apr 2 19:42:33 2010 : Debug: dirperm = 493 Fri Apr 2 19:42:33 2010 : Debug: locking = no Fri Apr 2 19:42:33 2010 : Debug: log_packet_header = no Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_unix, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_unix Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating unix Fri Apr 2 19:42:33 2010 : Debug: unix { Fri Apr 2 19:42:33 2010 : Debug: radwtmp = "/usr/local/var/log/radius/radwtmp" Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: (Loaded rlm_radutmp, checking if it's valid) Fri Apr 2 19:42:33 2010 : Debug: Module: Linked to module rlm_radutmp Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating radutmp Fri Apr 2 19:42:33 2010 : Debug: radutmp { Fri Apr 2 19:42:33 2010 : Debug: filename = "/usr/local/var/log/radius/radutmp" Fri Apr 2 19:42:33 2010 : Debug: username = "%{User-Name}" Fri Apr 2 19:42:33 2010 : Debug: case_sensitive = yes Fri Apr 2 19:42:33 2010 : Debug: check_with_nas = yes Fri Apr 2 19:42:33 2010 : Debug: perm = 384 Fri Apr 2 19:42:33 2010 : Debug: callerid = yes Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating attr_filter.accounting_response Fri Apr 2 19:42:33 2010 : Debug: attr_filter attr_filter.accounting_response { Fri Apr 2 19:42:33 2010 : Debug: attrsfile = "/usr/local/etc/raddb/attrs.accounting_response" Fri Apr 2 19:42:33 2010 : Debug: key = "%{User-Name}" Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: Module: Checking session {...} for more modules to load Fri Apr 2 19:42:33 2010 : Debug: Module: Checking post-proxy {...} for more modules to load Fri Apr 2 19:42:33 2010 : Debug: Module: Checking post-auth {...} for more modules to load Fri Apr 2 19:42:33 2010 : Debug: Module: Instantiating attr_filter.access_reject Fri Apr 2 19:42:33 2010 : Debug: attr_filter attr_filter.access_reject { Fri Apr 2 19:42:33 2010 : Debug: attrsfile = "/usr/local/etc/raddb/attrs.access_reject" Fri Apr 2 19:42:33 2010 : Debug: key = "%{User-Name}" Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: } # modules Fri Apr 2 19:42:33 2010 : Debug: } # server Fri Apr 2 19:42:33 2010 : Debug: radiusd: #### Opening IP addresses and Ports #### Fri Apr 2 19:42:33 2010 : Debug: listen { Fri Apr 2 19:42:33 2010 : Debug: type = "auth" Fri Apr 2 19:42:33 2010 : Debug: ipaddr = 192.168.1.254 Fri Apr 2 19:42:33 2010 : Debug: port = 1812 Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: listen { Fri Apr 2 19:42:33 2010 : Debug: type = "acct" Fri Apr 2 19:42:33 2010 : Debug: ipaddr = 192.168.1.254 Fri Apr 2 19:42:33 2010 : Debug: port = 1813 Fri Apr 2 19:42:33 2010 : Debug: } Fri Apr 2 19:42:33 2010 : Debug: Listening on authentication address 192.168.1.254 port 1812 Fri Apr 2 19:42:33 2010 : Debug: Listening on accounting address 192.168.1.254 port 1813 Fri Apr 2 19:42:33 2010 : Info: Ready to process requests. ================================================================== rad_recv: Access-Request packet from host 192.168.1.254 port 49707, id=54, length=56 User-Name = "mike" User-Password = "m1k3" NAS-IP-Address = 192.168.1.254 NAS-Port = 37 Fri Apr 2 19:42:44 2010 : Info: +- entering group authorize {...} Fri Apr 2 19:42:44 2010 : Info: ++[preprocess] returns ok Fri Apr 2 19:42:44 2010 : Info: [auth_log] expand: /usr/local/var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d -> /usr/local/var/log/radius/radacct/192.168.1.254/auth-detail-20100402 Fri Apr 2 19:42:44 2010 : Info: [auth_log] /usr/local/var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d expands to /usr/local/var/log/radius/radacct/192.168.1.254/auth-detail-20100402 Fri Apr 2 19:42:44 2010 : Info: [auth_log] expand: %t -> Fri Apr 2 19:42:44 2010 Fri Apr 2 19:42:44 2010 : Info: ++[auth_log] returns ok Fri Apr 2 19:42:44 2010 : Info: ++[chap] returns noop Fri Apr 2 19:42:44 2010 : Info: ++[mschap] returns noop Fri Apr 2 19:42:44 2010 : Info: [suffix] No '@' in User-Name = "mike", looking up realm NULL Fri Apr 2 19:42:44 2010 : Info: [suffix] No such realm "NULL" Fri Apr 2 19:42:44 2010 : Info: ++[suffix] returns noop Fri Apr 2 19:42:44 2010 : Info: [eap] No EAP-Message, not doing EAP Fri Apr 2 19:42:44 2010 : Info: ++[eap] returns noop Fri Apr 2 19:42:44 2010 : Debug: rlm_sql (sql): Reserving sql socket id: 4 Fri Apr 2 19:42:44 2010 : Info: [sql] expand: -> Fri Apr 2 19:42:44 2010 : Error: [sql] Error generating query; rejecting user Fri Apr 2 19:42:44 2010 : Debug: rlm_sql (sql): Released sql socket id: 4 Fri Apr 2 19:42:44 2010 : Info: ++[sql] returns fail Fri Apr 2 19:42:44 2010 : Auth: Invalid user: [mike] (from client my_self-localhost port 37) Fri Apr 2 19:42:44 2010 : Info: Using Post-Auth-Type Reject Fri Apr 2 19:42:44 2010 : Info: +- entering group REJECT {...} Fri Apr 2 19:42:44 2010 : Info: [attr_filter.access_reject] expand: %{User-Name} -> mike Fri Apr 2 19:42:44 2010 : Debug: attr_filter: Matched entry DEFAULT at line 11 Fri Apr 2 19:42:44 2010 : Info: ++[attr_filter.access_reject] returns updated Fri Apr 2 19:42:44 2010 : Info: Delaying reject of request 0 for 1 seconds Fri Apr 2 19:42:44 2010 : Debug: Going to the next request Fri Apr 2 19:42:44 2010 : Debug: Waking up in 0.9 seconds. Fri Apr 2 19:42:45 2010 : Info: Sending delayed reject for request 0 Sending Access-Reject of id 54 to 192.168.1.254 port 49707 Fri Apr 2 19:42:45 2010 : Debug: Waking up in 4.9 seconds. Fri Apr 2 19:42:50 2010 : Info: Cleaning up request 0 ID 54 with timestamp +11 Fri Apr 2 19:42:50 2010 : Info: Ready to process requests.
Hello there, I wonder if I give the impression that I know a lot about this, but I started in linux just one year ago withou any previous background, so I still have a very long way to go before I consider myself an expert. However, I am glad to explain to you how I did it, in the hope this may be of help to you. The server at my company is Ubuntu 8.04 LTS. I simply followed the following tutorial: https://help.ubuntu.com/community/WifiDocs/ChillispotHotspot/8.04 Then I customised it with my own SQL query, database name, etc. I am no expert in SQL neither, but again I am very stubborn, so I googled until I learnt and understood the syntax for my query (in my case it's a bit more complicated than usual because I have too lookup across three tables on a database containing hotel reservations, and then autorise according to family name, reservation code as password, and the reservation being valid for the current date). Please notice the comment on the tutorial: "This HOWTO presumes you have an x86 machine. This is not going to work on AMD64, because both Chillispot and FreeRadius have bugs which prevent them to work as they should. I don't know the status of these softwares on other platforms." Could this be affecting you? I find this tutorial well explained, and I think that it may help you. In my case, I read it all a few times until I understood what I was doing before actually setting up the software. Regarding the contents of the verbous debug messages your server gives you, in my case if the syntax of the SQL query is wrong then freeradius will tell me so and the startup of the daemon halts, so freeradius does not run at all. Assuming that freeradius behaves the same way in Ubuntu as in CentOS, your case looks like a different one to me, it seems to me as if the syntax of your SQL query is good but for some reason freeradius cannot execute it, and therefore it cannot process authrosation request, and thus rejects all requests. Pay attention to the section of the tutorial where you change the authorisation method to SQL. Make sure that all the IP's you type in the config files are correct; if the IP is on the server itself then it is usually best to use the loopback address 127.0.0.1, make sure that the username and password you put in the config file are valid MySQL credentials (may be obvious, but remember that MySQL has its own sets of users with their corresponding passwords, and the user and password with which you instruct freeradius to acess the mysql database must be valid AND must have read and write privileges on the database). It is things like this that I would look at in detail. So, tell me how you progress!
Hello again, Just a few doubts now. Code: Fri Apr 2 19:42:33 2010 : Debug: sql { Fri Apr 2 19:42:33 2010 : Debug: driver = "rlm_sql_mysql" Fri Apr 2 19:42:33 2010 : Debug: server = "localhost" Fri Apr 2 19:42:33 2010 : Debug: [b]port = ""[/b] I imagine that not specifying the port number results in freeradius using the default... or not? Can freeradius really access the database engine? Now another doubt. Code: Fri Apr 2 19:42:33 2010 : Debug: nas_query = "SELECT id,nasname,shortname,type,secret FROM nas" Fri Apr 2 19:42:33 2010 : Debug: [B]authorize_check_query = ""[/B] Fri Apr 2 19:42:33 2010 : Debug: authorize_group_check_query = "" Fri Apr 2 19:42:33 2010 : Debug: authorize_group_reply_query = "" Well, the query that does the checking for determining authorisation is authorize_check_query. Or at least it works like this on my system. In your case it's an empty string "". I think that you should populate authorize_check_query with a valid SQL query, and then populate the "radcheck" table with usernames, the field Attribute always containing the string "Password", the field op containing "==" (it's the operand), and the field Value containing the password itself (because the attribute is password). The structure of my radcheck table is the following: Code: +-----------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+---------+----------------+ | id | int(11) unsigned | NO | PRI | NULL | auto_increment | | UserName | varchar(64) | NO | MUL | | | | Attribute | varchar(32) | NO | | | | | op | char(2) | NO | | == | | | Value | varchar(253) | NO | | | | +-----------+------------------+------+-----+---------+----------------+ Example: Code: +----+----------+-----------+----+-------+ | id | UserName | Attribute | op | Value | +----+----------+-----------+----+-------+ | 1 | Mike | Password | == | m1k3 | +----+----------+-----------+----+-------+ Then the query would be: Code: authorize_check_query = "SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '%{User-Name}' ORDER BY id" which is the standard query. In order to add or remove users simply edit the radcheck table. By the way, you use ${User-Name}, I use ${SQL-User-Name} instead, I suppose your syntax will be ok. In my case, the sql.conf file states that when you use ${SQL-User-Name} you ensure that the user name is correctly escaped and contains no characters that can ruin the SQL query. You can actually check across different tables, but for debugging purposes what I suggest is the starting point. Once that is working you can start refining your query. On my system, if you do what I am telling you, it should work. If you follow my suggestion then try the following command (you must be logged into the server locally or by SSH): Code: sudo radtest "Mike" m1k3 127.0.0.1 0 [your radius password] and it should return an Access-Accept. Don't forget the "0" between the IP address and the radius password, I tend to forget it, it gives a syntax error and I have to retype -I hate it when it happens-. Hope this helped.
