Not working in changing SQL queries in sql.conf

[Guy Fraser]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-devel/attachments/20080228/9f15ac49/attachment.html>