FreeRadius + MySQL working, but I don't know how to customise SQL queries

Discussion in 'Installation/Configuration' started by awe, Oct 23, 2009.

  1. awe

    awe New Member

    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!
     
  2. awe

    awe New Member

    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.
     
    Last edited: Oct 26, 2009
  3. mndabarasa

    mndabarasa New Member

    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

    [[email protected] 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 [email protected]:/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.
     
  4. awe

    awe New Member

    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!
     
    Last edited: Apr 4, 2010
  5. awe

    awe New Member

    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.
     
    Last edited: Apr 4, 2010

Share This Page