Problem with mysql authorization

[Ian Truelsen]

I am trying to set up mysql authorization, but am having some problems.
I have set up sql.conf which seems to be correct, based on the output:


-- Module: Loaded SQL 
 sql: driver = "rlm_sql_mysql"
 sql: server = "localhost"
 sql: port = ""
 sql: login = "radius"
 sql: password = "xxxx"
 sql: radius_db = "radius"
 sql: nas_table = "nas"
 sql: sqltrace = no
 sql: sqltracefile = "/var/log/radius/sqltrace.sql"
 sql: readclients = no
 sql: deletestalesessions = yes
 sql: num_sql_socks = 5
 sql: sql_user_name = "%{User-Name}"
 sql: default_user_profile = ""
 sql: query_on_not_found = no
 sql: authorize_check_query = "SELECT id, UserName, Attribute, Value, op           FROM radcheck           WHERE Username = '%{SQL-User-Name}'           ORDER BY id"
 sql: authorize_reply_query = "SELECT id, UserName, Attribute, Value, op           FROM radreply           WHERE Username = '%{SQL-User-Name}'           ORDER BY id"

To me, that looks like it is correct.

I have added info to the radcheck table:

+----+----------+-----------+--------+----+
| id | UserName | Attribute | Value  | op |
+----+----------+-----------+--------+----+
|  1 | ian      | password  | tester | == | 
+----+----------+-----------+--------+----+

Now, I try to test with radtest:

brentwood-internet ~ # radtest ian tester localhost 1812 testing123
Sending Access-Request of id 88 to 127.0.0.1 port 1812
        User-Name = "ian"
        User-Password = "tester"
        NAS-IP-Address = 255.255.255.255
        NAS-Port = 1812
rad_recv: Access-Reject packet from host 127.0.0.1:1812, id=88, length=20

So, not so good.

rad_recv: Access-Request packet from host 127.0.0.1:2048, id=88, length=55
        User-Name = "ian"
        User-Password = "tester"
        NAS-IP-Address = 255.255.255.255
        NAS-Port = 1812

That looks like the query is being processed as I would expect. The rest of the output:

radius_xlat:  'ian'
rlm_sql (sql): sql_set_user escaped user --> 'ian'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op           FROM radcheck           WHERE Username = 'ian'           ORDER BY id'
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 = 'ian' AND usergroup.GroupName = radgroupcheck.GroupName ORDER BY radgroupcheck.id'
radius_xlat:  'SELECT id, UserName, Attribute, Value, op           FROM radreply           WHERE Username = 'ian'           ORDER BY id'
radius_xlat:  'SELECT radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgroupreply.Value,radgroupreply.op  FROM radgroupreply,usergroup WHERE usergroup.Username = 'ian' AND usergroup.GroupName = radgroupreply.GroupName ORDER BY radgroupreply.id'
rlm_sql (sql): Released sql socket id: 4
  modcall[authorize]: module "sql" returns ok for request 0
modcall: leaving group authorize (returns ok) for request 0
  rad_check_password:  Found Auth-Type System
auth: type "System"
  Processing the authenticate section of radiusd.conf
modcall: entering group authenticate for request 0
rlm_unix: [ian]: invalid password
  modcall[authenticate]: module "unix" returns reject for request 0
modcall: leaving group authenticate (returns reject) for request 0
auth: Failed to validate the user.
Login incorrect: [ian/tester] (from client localhost port 1812)

So, I am not sure what is going on. When I run the command in mysql, I get the correct output, as I would expect:

mysql> select id, 
    -> UserName, Attribute, Value, op from radcheck where Username = 'ian' order by id;
+----+----------+-----------+--------+----+
| id | UserName | Attribute | Value  | op |
+----+----------+-----------+--------+----+
|  1 | ian      | password  | tester | == | 
+----+----------+-----------+--------+----+
1 row in set (0.00 sec)

Any thoughts on what I missed here?

Ian Truelsen
s/v Sting
Email: ian.truelsen at gmail.com
AIM: ihtruelsen
MSN: ihtruelsen at hotmail.com
Google Talk: ian.truelsen at gmail.com