Problem getting FR/MySQL to work with CHAP

[Rens Houben]

Hello all,

	Due to a policy change with MCI we now have to change our
authentication/authorization scheme for dial-in users to CHAP, but for
some reason I just gan't get it to work. 

	I've checked mailing list archives and google, and as far as
I can see I've done everything right, but I'm still getting "Cleartext
password not available."

Here's the log from freeradius -X :

rad_recv: Access-Request packet from host 195.129.12.34:1645, id=129, length=228
	User-Name = "testflex at systemec.nl"
	CHAP-Password = 0x01cf2e2a27fc74a7b6271039f9c3e1b0e6
	NAS-IP-Address = 213.116.1.36
	NAS-Port = 70
	NAS-Port-Type = ISDN
	Service-Type = Framed-User
	Framed-Protocol = PPP
	State = 0x
	Calling-Station-Id = "774642968"
	Called-Station-Id = "0676011850"
	Acct-Session-Id = "436504632"
	X-Ascend-Data-Rate = 64000
	X-Ascend-Xmit-Rate = 64000
	Proxy-State = 0x50583031000065bd93266f974b08f6115766e0d35d7719e900020691d574012400000000000000000002066dc2e5a4030000000000000000000000030000000200000f73008d192a9815e82047235efbe3c5fbb341
modcall: entering group authorize
  modcall[authorize]: module "preprocess" returns ok
  rlm_chap: Setting 'Auth-Type := CHAP'
  modcall[authorize]: module "chap" returns ok
    rlm_realm: Looking up realm "systemec.nl" for User-Name = "testflex at systemec.nl"
    rlm_realm: Found realm "systemec.nl"
    rlm_realm: Adding Stripped-User-Name = "testflex"
    rlm_realm: Proxying request from user testflex to realm systemec.nl
    rlm_realm: Adding Realm = "systemec.nl"
    rlm_realm: Authentication realm is LOCAL.
  modcall[authorize]: module "suffix" returns noop
radius_xlat:  'testflex at systemec.nl'
rlm_sql (sql): sql_set_user escaped user --> 'testflex at systemec.nl'
radius_xlat:  'SELECT id,UserName,Attribute,Value,op 
	FROM radcheck WHERE Username = 'testflex at systemec.nl' ORDER BY id'

* This returns the following data when run in a mysql shell:
+-----+----------------------+----------------+-------+------+
| id  | UserName             | Attribute      | Value | op   |
+-----+----------------------+----------------+-------+------+
| 186 | testflex at systemec.nl | Password       | ----- | ==   |
| 271 | testflex at systemec.nl | CHAP-Challenge | ----- | ==   |
| 272 | testflex at systemec.nl | Auth-Type      | Local | :=   |
+-----+----------------------+----------------+-------+------+
(password and challenge secret changed for security purposes)

rlm_sql (sql): Reserving sql socket id: 4
radius_xlat:  'SELECT radgroupcheck.id,radgroupcheck.GroupName,
	radgroupcheck.Attribute,
	radgroupcheck.Value,radgroupcheck.op  
	FROM radgroupcheck,usergroup 
	WHERE usergroup.Username = 'testflex at systemec.nl' 
	AND usergroup.GroupName = radgroupcheck.GroupName 
	ORDER BY radgroupcheck.id'

+----+-----------+----------------+-------+------+
| id | GroupName | Attribute      | Value | op   |
+----+-----------+----------------+-------+------+
|  3 | flex      | Huntgroup-Name | flex  | ==   |
|  4 | flex      | Auth-Type      | Local | :=   |
+----+-----------+----------------+-------+------+


radius_xlat:  'SELECT id,UserName,Attribute,Value,op 
	FROM radreply WHERE Username = 'testflex at systemec.nl' 
	ORDER BY id'

Empty set (0.00 sec)

radius_xlat:  'SELECT radgroupreply.id,radgroupreply.GroupName,
	radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op
	FROM radgroupreply,usergroup 
	WHERE usergroup.Username = 'testflex at systemec.nl' 
	AND usergroup.GroupName = radgroupreply.GroupName 
	ORDER BY radgroupreply.id'

+----+-----------+-----------------+-------------+------+
| id | GroupName | Attribute       | Value       | op   |
+----+-----------+-----------------+-------------+------+
|  1 | flex      | Auth-Type       | Local       | :=   |
|  4 | flex      | Framed-Protocol | PPP         | :=   |
|  5 | flex      | Service-type    | Framed-User | :=   |
+----+-----------+-----------------+-------------+------+


rlm_sql (sql): No matching entry in the database for request from user [testflex at systemec.nl]
rlm_sql (sql): Released sql socket id: 4
  modcall[authorize]: module "sql" returns notfound
modcall: group authorize returns ok
  rad_check_password:  Found Auth-Type CHAP
auth: type "CHAP"
modcall: entering group Auth-Type
  rlm_chap: login attempt by "testflex" with CHAP password
  rlm_chap: Could not find clear text password for user testflex
  modcall[authenticate]: module "chap" returns invalid
modcall: group Auth-Type returns invalid
auth: Failed to validate the user.
Login incorrect (rlm_chap: Clear text password not available): [testflex at systemec.nl/<CHAP-Password>] (from client worldcom4 port 70 cli 774642968)
Delaying request 0 for 1 seconds
Finished request 0


I've tried using the attribute names 'Password', 'User-Password',
'CHAP-Password', as well as forcing Auth-Type to CHAP, in pretty much
every configuration I could think of, but the end result remains the
same.

Does anyone have a suggestion on what I've missed? 
(Version 0.9.1, by the way)


-- 
Rens Houben                           |    opinions are mine
Resident linux guru and sysadmin      | if my employers have one
Systemec Internet Services.           |they'll tell you themselves
PGP key at http://swordbreaker.systemec.nl/~shadur/shadur.key.asc