freeradius and mysql fails

[mailing at sylph.be]

hello,


i was wondering what sql tables i need to have.

Im trying to setup freeradius with mysql and i cant get out.

i've got the following tables:

radius (12)

    * Browse badusers
    * Browse mtotacct
    * Browse nas
    * Browse radacct
    * Browse radcheck
    * Browse radgroupcheck
    * Browse radgroupreply
    * Browse radpostauth
    * Browse radreply
    * Browse totacct
    * Browse usergroup
    * Browse userinfo

but when i try to logon (asa 5510 test connection to freeradius) it
doesnt work with sql

i can logon with the test account 'steve' and password 'testing' and i
can logon to the cisco ascm.

but when i add an user to mysql something goes wrong.

first steve:

radius_xlat:  'steve'
rlm_sql (sql): sql_set_user escaped user --> 'steve'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op           FROM
radcheck           WHERE Username = 'steve'           ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 3
rlm_sql (sql): User steve not found in radcheck
radius_xlat:  'SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op  FROM radgroupcheck,usergroup WHERE usergroup.Username = 'steve' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
radius_xlat:  'SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op  FROM radgroupreply,usergroup WHERE usergroup.Username = 'steve' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql (sql): User steve not found in radgroupcheck
rlm_sql (sql): Released sql socket id: 3
rlm_sql (sql): User not found
  modcall[authorize]: module "sql" returns notfound for request 21
modcall: leaving group authorize (returns ok) for request 21
  rad_check_password:  Found Auth-Type Local
auth: type Local
auth: user supplied User-Password matches local User-Password
Sending Access-Accept of id 83 to 192.168.6.1 port 1025
        Service-Type = Framed-User
        Framed-Protocol = PPP
        Framed-IP-Address = 172.16.3.33
        Framed-IP-Netmask = 255.255.255.0
        Framed-Routing = Broadcast-Listen
        Framed-Filter-Id = "std.ppp"
        Framed-MTU = 1500
        Framed-Compression = Van-Jacobson-TCP-IP
Finished request 21

-------------------sql-----------------

rad_recv: Access-Request packet from host 192.168.6.1:1025, id=89,
length=110
        User-Name = "test1"
        User-Password = "test1"
        NAS-IP-Address = 192.168.6.1
        NAS-Port-Type = Virtual
        Calling-Station-Id = "000.000.000.000"
        Cisco-AVPair = "ip:source-ip=000.000.000.000"
  Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 27
  modcall[authorize]: module "preprocess" returns ok for request 27
  modcall[authorize]: module "chap" returns noop for request 27
  modcall[authorize]: module "mschap" returns noop for request 27
    rlm_realm: No '@' in User-Name = "test1", looking up realm NULL
    rlm_realm: No such realm "NULL"
  modcall[authorize]: module "suffix" returns noop for request 27
  rlm_eap: No EAP-Message, not doing EAP
  modcall[authorize]: module "eap" returns noop for request 27
    users: Matched entry DEFAULT at line 152
  modcall[authorize]: module "files" returns ok for request 27
radius_xlat:  'test1'
rlm_sql (sql): sql_set_user escaped user --> 'test1'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op           FROM
radcheck           WHERE Username = 'test1'           ORDER BY id'
rlm_sql (sql): Reserving sql socket id: 2
radius_xlat:  'SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgroupcheck.Value,radgroupcheck.op  FROM radgroupcheck,usergroup WHERE usergroup.Username = 'test1' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op           FROM
radreply           WHERE Username = 'test1'           ORDER BY id'
radius_xlat:  'SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op  FROM radgroupreply,usergroup WHERE usergroup.Username = 'test1' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql (sql): Released sql socket id: 2
  modcall[authorize]: module "sql" returns ok for request 27
modcall: leaving group authorize (returns ok) for request 27
  rad_check_password:  Found Auth-Type System
auth: type "System"
  Processing the authenticate section of radiusd.conf
modcall: entering group authenticate for request 27
  modcall[authenticate]: module "unix" returns notfound for request 27
modcall: leaving group authenticate (returns notfound) for request 27
auth: Failed to validate the user.
Delaying request 27 for 1 seconds
Finished request 27
Going to the next request
Waking up in 6 seconds...
--- Walking the entire request list ---
Cleaning up request 26 ID 88 with timestamp 4472e7cb
Sending Access-Reject of id 89 to 192.168.6.1 port 1025
Cleaning up request 27 ID 89 with timestamp 4472e7cb
Nothing to do.  Sleeping until we see a request.



first its 'local' and with sql its 'system', is this ok?

when i test the sql statements i get:

====================
Showing rows 0 - 0 (1 total, Query took 0.0004 sec)
SQL query: SELECT id, UserName, Attribute, Value, op
FROM radcheck
WHERE Username = 'test1'
ORDER BY id
LIMIT 0 , 30

id  	 UserName  	 Attribute  	 Value  	 op
3 	test1 	User-Password 	$1$BnzqweeZ$EJ66Aqwe0/YANJdc8hBC/ 	:=
====================

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0003
sec)

SQL query: SELECT radgroupcheck.id, radgroupcheck.GroupName,
radgroupcheck.Attribute, radgroupcheck.Value, radgroupcheck.op
FROM radgroupcheck, usergroup
WHERE usergroup.Username = 'test1'
AND usergroup.GroupName = radgroupcheck.GroupName
ORDER BY radgroupcheck.id
LIMIT 0 , 30

====================


Showing rows 0 - 5 (6 total, Query took 0.0004 sec)
SQL query: SELECT id, UserName, Attribute, Value, op
FROM radreply
WHERE Username = 'test1'
ORDER BY id
LIMIT 0 , 30

1  	test1  	Framed-Protocol  	PPP  	=
2 	test1 	Framed-IP-Address 	172.16.3.33 	=
3 	test1 	Framed-IP-Netmask 	255.255.255.0 	=
4 	test1 	Framed-MTU 	1500 	=
5 	test1 	Framed-Compression 	Van-Jacobsen-TCP-IP 	=
6 	test1 	Service-Type 	Framed-User 	=

====================

MySQL returned an empty result set (i.e. zero rows). (Query took 0.0003
sec)

SQL query: SELECT radgroupreply.id, radgroupreply.GroupName,
radgroupreply.Attribute, radgroupreply.Value, radgroupreply.op
FROM radgroupreply, usergroup
WHERE usergroup.Username = 'test1'
AND usergroup.GroupName = radgroupreply.GroupName
ORDER BY radgroupreply.id
LIMIT 0 , 30

====================

i want to know if those results are good, or did i forget something?!

the password is encrypted, maybe this is the problem?

when i 'check' the password via dialup admin webinterface, it said 'No
its wrong'




greetings,