freeradius-2.0.3 not talking to mysql-5.1
NPY
npy at pdog-vpn.com
Wed May 14 19:54:13 CEST 2008
Hi Chris,
Thanks for the hint.
It helped to resolve the problem.
-Marcus
----- Original Message -----
From: "Chris" <cjl at viptalk.net>
To: "NPY" <npy at pdog-vpn.com>
Sent: Thursday, May 15, 2008 12:57 AM
Subject: Re: freeradius-2.0.3 not talking to mysql-5.1
> Uncommenting it in instantiate is okay (probably unnecessary), but if you
> want it to authorize using sql, you have to uncomment it in authorize
> { }. If you want to authenticate using sql, you have to uncomment it in
> authenticate { }. Want to do sql accounting? uncomment in accounting
> { }.
>
> See raddb/sites-enabled/default
>
> On May 14, 2008, at 9:42 AM, NPY wrote:
>
>> OK, I added a line 'sql' to 'instantiate' section of radiusd.conf and
>> radiusd is finally loading rlm_sql_mysql.
>> Only the authentication is still not going through ..... sigh
>>
>> Anything else I have missed? Do I need to modify 'users' file etc?
>>
>> Below is the new 'radiusd -X' output for 'radtest joy happy localhost
>> 1812 testing123'
>> ---------------------------------------------
>>
>> FreeRADIUS Version 2.0.3, for host amd64-portbld-freebsd7.0, built on
>> May 13 2008 at 14:48:48
>> Copyright (C) 1999-2008 The FreeRADIUS server project and contributors.
>> There is NO warranty; not even for MERCHANTABILITY or FITNESS FOR A
>> PARTICULAR PURPOSE.
>> You may redistribute copies of FreeRADIUS under the terms of the
>> GNU General Public License.
>> Starting - reading configuration files ...
>> including configuration file /usr/local/etc/raddb/radiusd.conf
>> including configuration file /usr/local/etc/raddb/proxy.conf
>> including configuration file /usr/local/etc/raddb/clients.conf
>> including configuration file /usr/local/etc/raddb/snmp.conf
>> including configuration file /usr/local/etc/raddb/eap.conf
>> including configuration file /usr/local/etc/raddb/sql.conf
>> including configuration file /usr/local/etc/raddb/sql/mysql/ dialup.conf
>> including configuration file /usr/local/etc/raddb/sql/mysql/ counter.conf
>> including configuration file /usr/local/etc/raddb/policy.conf
>> including files in directory /usr/local/etc/raddb/sites-enabled/
>> including configuration file /usr/local/etc/raddb/sites-enabled/ default
>> including configuration file /usr/local/etc/raddb/sites-enabled/
>> inner-tunnel
>> including dictionary file /usr/local/etc/raddb/dictionary
>> main {
>> prefix = "/usr/local"
>> localstatedir = "/var"
>> logdir = "/var/log"
>> libdir = "/usr/local/lib"
>> radacctdir = "/var/log/radacct"
>> hostname_lookups = no
>> max_request_time = 30
>> cleanup_delay = 5
>> max_requests = 1024
>> allow_core_dumps = no
>> pidfile = "/var/run/radiusd/radiusd.pid"
>> user = "freeradius"
>> group = "freeradius"
>> checkrad = "/usr/local/sbin/checkrad"
>> debug_level = 0
>> proxy_requests = yes
>> security {
>> max_attributes = 200
>> reject_delay = 1
>> status_server = yes
>> }
>> }
>> client localhost {
>> ipaddr = 127.0.0.1
>> require_message_authenticator = no
>> secret = "testing123"
>> nastype = "other"
>> }
>> radiusd: #### Loading Realms and Home Servers ####
>> proxy server {
>> retry_delay = 5
>> retry_count = 3
>> default_fallback = no
>> dead_time = 120
>> wake_all_if_all_dead = no
>> }
>> home_server localhost {
>> ipaddr = 127.0.0.1
>> port = 1812
>> type = "auth"
>> secret = "testing123"
>> response_window = 20
>> max_outstanding = 65536
>> zombie_period = 40
>> status_check = "status-server"
>> ping_check = "none"
>> ping_interval = 30
>> check_interval = 30
>> num_answers_to_alive = 3
>> num_pings_to_alive = 3
>> revive_interval = 120
>> status_check_timeout = 4
>> }
>> home_server_pool my_auth_failover {
>> type = fail-over
>> home_server = localhost
>> }
>> realm example.com {
>> auth_pool = my_auth_failover
>> }
>> realm LOCAL {
>> }
>> radiusd: #### Instantiating modules ####
>> instantiate {
>> Module: Linked to module rlm_exec
>> Module: Instantiating exec
>> exec {
>> wait = yes
>> input_pairs = "request"
>> shell_escape = yes
>> }
>> Module: Linked to module rlm_expr
>> Module: Instantiating expr
>> Module: Linked to module rlm_expiration
>> Module: Instantiating expiration
>> expiration {
>> reply-message = "Password Has Expired "
>> }
>> Module: Linked to module rlm_logintime
>> Module: Instantiating logintime
>> logintime {
>> reply-message = "You are calling outside your allowed timespan "
>> minimum-timeout = 60
>> }
>> Module: Linked to module rlm_sql
>> Module: Instantiating sql
>> sql {
>> driver = "rlm_sql_mysql"
>> server = "localhost"
>> port = ""
>> login = "radius"
>> password = "pie=3.14"
>> radius_db = "radius"
>> read_groups = yes
>> sqltrace = no
>> sqltracefile = "/var/log/sqltrace.sql"
>> readclients = no
>> deletestalesessions = yes
>> num_sql_socks = 5
>> sql_user_name = "%{User-Name}"
>> default_user_profile = ""
>> nas_query = "SELECT id, nasname, shortname, type, secret FROM nas"
>> authorize_check_query = "SELECT id, username, attribute, value, op FROM
>> radcheck WHERE username = '%{SQL-User- Name}' ORDER
>> BY id"
>> authorize_reply_query = "SELECT id, username, attribute, value, op FROM
>> radreply WHERE username = '%{SQL-User- Name}' ORDER
>> BY id"
>> authorize_group_check_query = "SELECT id, groupname, attribute, Value,
>> op FROM radgroupcheck WHERE groupname = '%
>> {Sql-Group}' ORDER BY id"
>> authorize_group_reply_query = "SELECT id, groupname, attribute, value,
>> op FROM radgroupreply WHERE groupname = '%
>> {Sql-Group}' ORDER BY id"
>> accounting_onoff_query = " UPDATE radacct SET
>> acctstoptime = '%S', acctsessiontime =
>> unix_timestamp('%S') - unix_timestamp(acctstarttime),
>> acctterminatecause = '%{Acct-Terminate-Cause}',
>> acctstopdelay = %{%{Acct-Delay-Time}:-0} WHERE
>> acctsessiontime = 0 AND acctstoptime = NULL
>> AND nasipaddress = '%{NAS-IP-Address}' AND acctstarttime
>> <= '%S'"
>> accounting_update_query = " UPDATE radacct SET
>> framedipaddress = '%{Framed-IP-Address}', acctsessiontime
>> = '%{Acct-Session-Time}', acctinputoctets =
>> '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{% {Acct-Input-Octets}:-0}',
>> acctoutputoctets = '%{% {Acct-Output-Gigawords}:-0}' << 32 |
>> '%{%{Acct-Output- Octets}:-0}' WHERE acctsessionid =
>> '%{Acct-Session- Id}' AND username = '%{SQL-User-Name}'
>> AND nasipaddress = '%{NAS-IP-Address}'"
>> accounting_update_query_alt = " INSERT INTO radacct
>> (acctsessionid, acctuniqueid, username, realm,
>> nasipaddress, nasportid, nasporttype,
>> acctstarttime, acctsessiontime, acctauthentic,
>> connectinfo_start, acctinputoctets, acctoutputoctets,
>> calledstationid, callingstationid, servicetype,
>> framedprotocol, framedipaddress, acctstartdelay,
>> xascendsessionsvrkey) VALUES ('%{Acct-Session-Id}', '%
>> {Acct-Unique-Session-Id}', '%{SQL-User-Name}', '% {Realm}',
>> '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS- Port-Type}',
>> DATE_SUB('%S', INTERVAL (%{%{Acct-Session- Time}:-0} +
>> %{%{Acct-Delay-Time}:-0}) SECOND), '%{Acct-Session- Time}',
>> '%{Acct-Authentic}', '', '%{%{Acct-Input- Gigawords}:-0}' << 32 |
>> '%{%{Acct-Input-Octets}:-0}', '%
>> {%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-
>> Octets}:-0}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%
>> {Service-Type}', '%{Framed-Protocol}', '%{Framed-IP- Address}',
>> '0', '%{X-Ascend-Session-Svr-Key}')"
>> accounting_start_query = " INSERT INTO radacct (acctsessionid,
>> acctuniqueid, username, realm, nasipaddress,
>> nasportid, nasporttype, acctstarttime, acctstoptime,
>> acctsessiontime, acctauthentic, connectinfo_start,
>> connectinfo_stop, acctinputoctets, acctoutputoctets,
>> calledstationid, callingstationid, acctterminatecause,
>> servicetype, framedprotocol, framedipaddress,
>> acctstartdelay, acctstopdelay, xascendsessionsvrkey) VALUES
>> ('% {Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-
>> User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-
>> Port-Type}', '%S', NULL, '0', '%{Acct-Authentic}', '%
>> {Connect-Info}', '', '0', '0', '%{Called-Station-Id}',
>> '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed- Protocol}',
>> '%{Framed-IP-Address}', '%{%{Acct-Delay-Time}:-0}', '0',
>> '%{X-Ascend-Session-Svr-Key}')"
>> accounting_start_query_alt = " UPDATE radacct SET
>> acctstarttime = '%S', acctstartdelay = '%{%{Acct-
>> Delay-Time}:-0}', connectinfo_start = '%{Connect- Info}'
>> WHERE acctsessionid = '%{Acct-Session-Id}' AND username =
>> '%{SQL-User-Name}' AND nasipaddress = '%{NAS-IP-Address}'"
>> accounting_stop_query = " UPDATE radacct SET acctstoptime
>> = '%S', acctsessiontime = '%{Acct- Session-Time}',
>> acctinputoctets = '%{%{Acct-Input- Gigawords}:-0}' << 32 |
>> '%{%{Acct-Input-Octets}:-0}', acctoutputoctets =
>> '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{% {Acct-Output-Octets}:-0}',
>> acctterminatecause = '%{Acct- Terminate-Cause}',
>> acctstopdelay = '%{%{Acct-Delay- Time}:-0}',
>> connectinfo_stop = '%{Connect- Info}' WHERE acctsessionid =
>> '%{Acct-Session-Id}' AND username = '%{SQL-User-Name}'
>> AND nasipaddress = '%{NAS-IP-Address}'"
>> accounting_stop_query_alt = " INSERT INTO radacct
>> (acctsessionid, acctuniqueid, username, realm,
>> nasipaddress, nasportid, nasporttype, acctstarttime,
>> acctstoptime, acctsessiontime, acctauthentic, connectinfo_start,
>> connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid,
>> callingstationid, acctterminatecause, servicetype, framedprotocol,
>> framedipaddress, acctstartdelay, acctstopdelay)
>> VALUES ('%{Acct-Session-Id}', '% {Acct-Unique-Session-Id}',
>> '%{SQL-User-Name}', '% {Realm}', '%{NAS-IP-Address}', '%{NAS-Port}',
>> '%{NAS-Port- Type}', DATE_SUB('%S',
>> INTERVAL (%{% {Acct-Session-Time}:-0} +
>> %{%{Acct-Delay-Time}:-0}) SECOND), '%S',
>> '%{Acct-Session-Time}', '%{Acct- Authentic}', '', '%{Connect-Info}',
>> '%{%{Acct-Input- Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}',
>> '% {%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-
>> Octets}:-0}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%
>> {Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-
>> Protocol}', '%{Framed-IP-Address}', '0', '%{%{Acct-
>> Delay-Time}:-0}')"
>> group_membership_query = "SELECT groupname FROM radusergroup
>> WHERE username = '%{SQL-User-Name}' ORDER BY priority"
>> connect_failure_retry_delay = 60
>> simul_count_query = ""
>> simul_verify_query = "SELECT radacctid, acctsessionid, username,
>> nasipaddress, nasportid, framedipaddress, callingstationid,
>> framedprotocol FROM radacct WHERE
>> username = '%{SQL-User-Name}' AND
>> acctstoptime = NULL"
>> postauth_query = "INSERT INTO radpostauth (username, pass, reply,
>> authdate) VALUES (
>> '%{User-Name}', '%{%{User-Password}:-% {Chap-Password}}',
>> '%{reply:Packet-Type}', '%S')"
>> safe-characters =
>> "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.- _: /"
>> }
>> rlm_sql (sql): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and
>> linked
>> rlm_sql (sql): Attempting to connect to radius at localhost:/radius
>> rlm_sql (sql): starting 0
>> rlm_sql (sql): Attempting to connect rlm_sql_mysql #0
>> rlm_sql_mysql: Starting connect to MySQL server for #0
>> rlm_sql (sql): Connected new DB handle, #0
>> rlm_sql (sql): starting 1
>> rlm_sql (sql): Attempting to connect rlm_sql_mysql #1
>> rlm_sql_mysql: Starting connect to MySQL server for #1
>> rlm_sql (sql): Connected new DB handle, #1
>> rlm_sql (sql): starting 2
>> rlm_sql (sql): Attempting to connect rlm_sql_mysql #2
>> rlm_sql_mysql: Starting connect to MySQL server for #2
>> rlm_sql (sql): Connected new DB handle, #2
>> rlm_sql (sql): starting 3
>> rlm_sql (sql): Attempting to connect rlm_sql_mysql #3
>> rlm_sql_mysql: Starting connect to MySQL server for #3
>> rlm_sql (sql): Connected new DB handle, #3
>> rlm_sql (sql): starting 4
>> rlm_sql (sql): Attempting to connect rlm_sql_mysql #4
>> rlm_sql_mysql: Starting connect to MySQL server for #4
>> rlm_sql (sql): Connected new DB handle, #4
>> }
>> radiusd: #### Loading Virtual Servers ####
>> server inner-tunnel {
>> modules {
>> Module: Checking authenticate {...} for more modules to load
>> Module: Linked to module rlm_pap
>> Module: Instantiating pap
>> pap {
>> encryption_scheme = "auto"
>> auto_header = no
>> }
>> Module: Linked to module rlm_chap
>> Module: Instantiating chap
>> Module: Linked to module rlm_mschap
>> Module: Instantiating mschap
>> mschap {
>> use_mppe = yes
>> require_encryption = no
>> require_strong = no
>> with_ntdomain_hack = no
>> }
>> Module: Linked to module rlm_unix
>> Module: Instantiating unix
>> unix {
>> radwtmp = "/var/log/radwtmp"
>> }
>> Module: Linked to module rlm_eap
>> Module: Instantiating eap
>> eap {
>> default_eap_type = "md5"
>> timer_expire = 60
>> ignore_unknown_eap_types = no
>> cisco_accounting_username_bug = no
>> }
>> Module: Linked to sub-module rlm_eap_md5
>> Module: Instantiating eap-md5
>> Module: Linked to sub-module rlm_eap_leap
>> Module: Instantiating eap-leap
>> Module: Linked to sub-module rlm_eap_gtc
>> Module: Instantiating eap-gtc
>> gtc {
>> challenge = "Password: "
>> auth_type = "PAP"
>> }
>> Module: Linked to sub-module rlm_eap_tls
>> Module: Instantiating eap-tls
>> tls {
>> rsa_key_exchange = no
>> dh_key_exchange = yes
>> rsa_key_length = 512
>> dh_key_length = 512
>> verify_depth = 0
>> pem_file_type = yes
>> private_key_file = "/usr/local/etc/raddb/certs/server.pem"
>> certificate_file = "/usr/local/etc/raddb/certs/server.pem"
>> CA_file = "/usr/local/etc/raddb/certs/ca.pem"
>> private_key_password = "whatever"
>> dh_file = "/usr/local/etc/raddb/certs/dh"
>> random_file = "/usr/local/etc/raddb/certs/random"
>> fragment_size = 1024
>> include_length = yes
>> check_crl = no
>> cipher_list = "DEFAULT"
>> make_cert_command = "/usr/local/etc/raddb/certs/bootstrap"
>> }
>> Module: Linked to sub-module rlm_eap_ttls
>> Module: Instantiating eap-ttls
>> ttls {
>> default_eap_type = "md5"
>> copy_request_to_tunnel = no
>> use_tunneled_reply = no
>> virtual_server = "inner-tunnel"
>> }
>> Module: Linked to sub-module rlm_eap_peap
>> Module: Instantiating eap-peap
>> peap {
>> default_eap_type = "mschapv2"
>> copy_request_to_tunnel = no
>> use_tunneled_reply = no
>> proxy_tunneled_request_as_eap = yes
>> virtual_server = "inner-tunnel"
>> }
>> Module: Linked to sub-module rlm_eap_mschapv2
>> Module: Instantiating eap-mschapv2
>> mschapv2 {
>> with_ntdomain_hack = no
>> }
>> Module: Checking authorize {...} for more modules to load
>> Module: Linked to module rlm_realm
>> Module: Instantiating suffix
>> realm suffix {
>> format = "suffix"
>> delimiter = "@"
>> ignore_default = no
>> ignore_null = no
>> }
>> Module: Linked to module rlm_files
>> Module: Instantiating files
>> files {
>> usersfile = "/usr/local/etc/raddb/users"
>> acctusersfile = "/usr/local/etc/raddb/acct_users"
>> preproxy_usersfile = "/usr/local/etc/raddb/preproxy_users"
>> compat = "no"
>> }
>> Module: Checking session {...} for more modules to load
>> Module: Linked to module rlm_radutmp
>> Module: Instantiating radutmp
>> radutmp {
>> filename = "/var/log/radutmp"
>> username = "%{User-Name}"
>> case_sensitive = yes
>> check_with_nas = yes
>> perm = 384
>> callerid = yes
>> }
>> Module: Checking post-proxy {...} for more modules to load
>> Module: Checking post-auth {...} for more modules to load
>> Module: Linked to module rlm_attr_filter
>> Module: Instantiating attr_filter.access_reject
>> attr_filter attr_filter.access_reject {
>> attrsfile = "/usr/local/etc/raddb/attrs.access_reject"
>> key = "%{User-Name}"
>> }
>> }
>> }
>> server {
>> modules {
>> Module: Checking authenticate {...} for more modules to load
>> Module: Checking authorize {...} for more modules to load
>> Module: Linked to module rlm_preprocess
>> Module: Instantiating preprocess
>> preprocess {
>> huntgroups = "/usr/local/etc/raddb/huntgroups"
>> hints = "/usr/local/etc/raddb/hints"
>> with_ascend_hack = no
>> ascend_channels_per_line = 23
>> with_ntdomain_hack = no
>> with_specialix_jetstream_hack = no
>> with_cisco_vsa_hack = no
>> with_alvarion_vsa_hack = no
>> }
>> Module: Checking preacct {...} for more modules to load
>> Module: Linked to module rlm_acct_unique
>> Module: Instantiating acct_unique
>> acct_unique {
>> key = "User-Name, Acct-Session-Id, NAS-IP-Address, Client-IP- Address,
>> NAS-Port"
>> }
>> Module: Checking accounting {...} for more modules to load
>> Module: Linked to module rlm_detail
>> Module: Instantiating detail
>> detail {
>> detailfile = "/var/log/radacct/%{Client-IP-Address}/detail-%Y%m%d"
>> header = "%t"
>> detailperm = 384
>> dirperm = 493
>> locking = no
>> log_packet_header = no
>> }
>> Module: Instantiating attr_filter.accounting_response
>> attr_filter attr_filter.accounting_response {
>> attrsfile = "/usr/local/etc/raddb/attrs.accounting_response"
>> key = "%{User-Name}"
>> }
>> Module: Checking session {...} for more modules to load
>> Module: Checking post-proxy {...} for more modules to load
>> Module: Checking post-auth {...} for more modules to load
>> }
>> }
>> radiusd: #### Opening IP addresses and Ports ####
>> listen {
>> type = "auth"
>> ipaddr = *
>> port = 0
>> }
>> listen {
>> type = "acct"
>> ipaddr = *
>> port = 0
>> }
>> Listening on authentication address * port 1812
>> Listening on accounting address * port 1813
>> Listening on proxy address * port 1814
>> Ready to process requests.
>> User-Name = "joy"
>> User-Password = "happy"
>> NAS-IP-Address = 123.242.231.112
>> NAS-Port = 1812
>> +- entering group authorize
>> ++[preprocess] returns ok
>> ++[chap] returns noop
>> ++[mschap] returns noop
>> rlm_realm: No '@' in User-Name = "joy", looking up realm NULL
>> rlm_realm: No such realm "NULL"
>> ++[suffix] returns noop
>> rlm_eap: No EAP-Message, not doing EAP
>> ++[eap] returns noop
>> ++[unix] returns notfound
>> ++[files] returns noop
>> ++[expiration] returns noop
>> ++[logintime] returns noop
>> rlm_pap: WARNING! No "known good" password found for the user.
>> Authentication may fail because of this.
>> ++[pap] returns noop
>> auth: No authenticate method (Auth-Type) configuration found for the
>> request: Rejecting the user
>> auth: Failed to validate the user.
>> Login incorrect: [joy/happy] (from client localhost port 1812)
>> Found Post-Auth-Type Reject
>> +- entering group REJECT
>> expand: %{User-Name} -> joy
>> attr_filter: Matched entry DEFAULT at line 11
>> ++[attr_filter.access_reject] returns updated
>> Delaying reject of request 0 for 1 seconds
>> Going to the next request
>> Waking up in 0.9 seconds.
>> Sending delayed reject for request 0
>> Waking up in 4.9 seconds.
>>
>>
>> ----- Original Message ----- From: "Alan DeKok"
>> <aland at deployingradius.com
>> >
>> To: "FreeRadius users mailing list"
>> <freeradius-users at lists.freeradius.org
>> >
>> Sent: Wednesday, May 14, 2008 11:51 PM
>> Subject: Re: freeradius-2.0.3 not talking to mysql-5.1
>>
>>
>>> NPY wrote:
>>>> I notice when running 'radiusd -X' that no module rlm_sql_mysql was
>>>> loaded. Is that a problem?
>>>> How do I resolve it?
>>>
>>> Ensure that the MySQL client libraries and headers are installed, and
>>> then re-build the server.
>>>
>>> Also, un-comment the references to SQL in the configuration files. It
>>> appears you haven't done that, so I have no idea why you would expect
>>> it
>>> to use SQL.
>>>
>>> If you do un-comment the reference to SQL in the config files, the
>>> server will look for the MySQL libraries. If they've been built, the
>>> server will use them. If not, it will complain.
>>>
>>> Alan DeKok.
>>> -
>>> List info/subscribe/unsubscribe? See
>>> http://www.freeradius.org/list/users.html
>>
>> -
>> List info/subscribe/unsubscribe? See
>> http://www.freeradius.org/list/users.html
>
>
More information about the Freeradius-Users
mailing list