Try emulating the expected result in your query : SELECT '1' AS id, UserName, 'User-Password' AS Attribute, Value, '=' AS Op FROM radcheck WHERE UserName = '%{SQL-User-Name} I don't use MS-SQL you will have to figure out the way to generate the items in the right order with the right types and values, but the above sample may work as is. IIRC the name of the field does not need to be assigned but appropriate pseudo values need to be in the correct column/field. On 2008-Feb-28, at 04:50, johnson elangbam wrote:
hi, I've tried changing SQL queries in sql.conf in order to read the username and password only from my customized table, but it doesn't work. It seems like I have to change the code in something like sql.c or rlm_sql.c, Please help.
Here is the output of the radius server after rejecting the user.
rad_recv: Access-Request packet from host 127.0.0.1 port 32834, id=56, length=58 User-Name = "magnum" User-Password = "opus" NAS-IP-Address = 192.168.2.227 NAS-Port = 0 +- entering group authorize ++[preprocess] returns ok ++[chap] returns noop ++[mschap] returns noop rlm_realm: No '@' in User-Name = "magnum", 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 expand: %{User-Name} -> magnum rlm_sql (sql): sql_set_user escaped user --> 'magnum' rlm_sql (sql): Reserving sql socket id: 4 expand: SELECT UserName,Value FROM radcheck WHERE Username = '%{SQL-User-Name}' -> SELECT UserName,Value FROM radcheck WHERE Username = 'magnum' query: SELECT UserName,Value FROM radcheck WHERE Username = 'magnum' rlm_sql: The 'Attribute' field is empty or NULL, skipping the entire row. rlm_sql (sql): Error getting data from database rlm_sql (sql): SQL query error; rejecting user rlm_sql (sql): Released sql socket id: 4 ++[sql] returns fail Invalid user: [magnum/opus] (from client localhost port 0) Found Post-Auth-Type Reject +- entering group REJECT expand: %{User-Name} -> magnum 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 Sending Access-Reject of id 56 to 127.0.0.1 port 32834 Waking up in 4.9 seconds. Cleaning up request 0 ID 56 with timestamp +14 Ready to process requests. rad_recv: Access-Request packet from host 127.0.0.1 port 32834, id=115, length=56 User-Name = "john" User-Password = "john" NAS-IP-Address = 192.168.2.227 NAS-Port = 0 +- entering group authorize ++[preprocess] returns ok ++[chap] returns noop ++[mschap] returns noop rlm_realm: No '@' in User-Name = "john", 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 expand: %{User-Name} -> john rlm_sql (sql): sql_set_user escaped user --> 'john' rlm_sql (sql): Reserving sql socket id: 3 expand: SELECT UserName,Value FROM radcheck WHERE Username = '%{SQL-User-Name}' -> SELECT UserName,Value FROM radcheck WHERE Username = 'john' query: SELECT UserName,Value FROM radcheck WHERE Username = 'john' rlm_sql: The 'Attribute' field is empty or NULL, skipping the entire row. rlm_sql (sql): Error getting data from database rlm_sql (sql): SQL query error; rejecting user rlm_sql (sql): Released sql socket id: 3 ++[sql] returns fail Invalid user: [john/john] (from client localhost port 0) Found Post-Auth-Type Reject +- entering group REJECT expand: %{User-Name} -> john attr_filter: Matched entry DEFAULT at line 11 ++[attr_filter.access_reject] returns updated Delaying reject of request 1 for 1 seconds Going to the next request Waking up in 0.9 seconds. Sending delayed reject for request 1 Sending Access-Reject of id 115 to 127.0.0.1 port 32834 Waking up in 4.9 seconds. Cleaning up request 1 ID 115 with timestamp +23 Ready to process requests.
With Regards Elangbam Johnson - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/devel.html
-- Guy Fraser Network Administrator The Internet Centre 1-888-450-6787 (780)450-6787