<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Try emulating the expected result in your query :<div><br class="webkit-block-placeholder"></div><div>SELECT '1' AS id, UserName, 'User-Password' AS Attribute, Value, '=' AS Op FROM radcheck WHERE UserName = '%{SQL-User-Name}</div><div><div><br class="webkit-block-placeholder"></div></div><div>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 </div><div>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</div><div>column/field.</div><div><br><div><div>On 2008-Feb-28, at 04:50, johnson elangbam wrote:</div><br class="Apple-interchange-newline"><blockquote type="cite">hi,<br> 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.<br> <br>Here is the output of the radius server after rejecting the user.<br><br>rad_recv: Access-Request packet from host <a href="http://127.0.0.1">127.0.0.1</a> port 32834, id=56, length=58<br> User-Name = "magnum"<br> User-Password = "opus"<br> NAS-IP-Address = <a href="http://192.168.2.227">192.168.2.227</a><br> NAS-Port = 0<br>+- entering group authorize<br>++[preprocess] returns ok<br>++[chap] returns noop<br> ++[mschap] returns noop<br> rlm_realm: No '@' in User-Name = "magnum", looking up realm NULL<br> rlm_realm: No such realm "NULL"<br>++[suffix] returns noop<br> rlm_eap: No EAP-Message, not doing EAP<br> ++[eap] returns noop<br>++[unix] returns notfound<br>++[files] returns noop<br> expand: %{User-Name} -> magnum<br>rlm_sql (sql): sql_set_user escaped user --> 'magnum'<br>rlm_sql (sql): Reserving sql socket id: 4<br> expand: SELECT UserName,Value FROM radcheck WHERE Username = '%{SQL-User-Name}' -> SELECT UserName,Value FROM radcheck WHERE Username = 'magnum'<br>query: SELECT UserName,Value FROM radcheck WHERE Username = 'magnum'<br> rlm_sql: The 'Attribute' field is empty or NULL, skipping the entire row.<br>rlm_sql (sql): Error getting data from database<br>rlm_sql (sql): SQL query error; rejecting user<br>rlm_sql (sql): Released sql socket id: 4<br> ++[sql] returns fail<br>Invalid user: [magnum/opus] (from client localhost port 0)<br> Found Post-Auth-Type Reject<br>+- entering group REJECT<br> expand: %{User-Name} -> magnum<br> attr_filter: Matched entry DEFAULT at line 11<br> ++[attr_filter.access_reject] returns updated<br>Delaying reject of request 0 for 1 seconds<br>Going to the next request<br>Waking up in 0.9 seconds.<br>Sending delayed reject for request 0<br>Sending Access-Reject of id 56 to <a href="http://127.0.0.1">127.0.0.1</a> port 32834<br> Waking up in 4.9 seconds.<br>Cleaning up request 0 ID 56 with timestamp +14<br>Ready to process requests.<br>rad_recv: Access-Request packet from host <a href="http://127.0.0.1">127.0.0.1</a> port 32834, id=115, length=56<br> User-Name = "john"<br> User-Password = "john"<br> NAS-IP-Address = <a href="http://192.168.2.227">192.168.2.227</a><br> NAS-Port = 0<br>+- entering group authorize<br>++[preprocess] returns ok<br> ++[chap] returns noop<br>++[mschap] returns noop<br> rlm_realm: No '@' in User-Name = "john", looking up realm NULL<br> rlm_realm: No such realm "NULL"<br>++[suffix] returns noop<br> rlm_eap: No EAP-Message, not doing EAP<br> ++[eap] returns noop<br>++[unix] returns notfound<br>++[files] returns noop<br> expand: %{User-Name} -> john<br>rlm_sql (sql): sql_set_user escaped user --> 'john'<br>rlm_sql (sql): Reserving sql socket id: 3<br> expand: SELECT UserName,Value FROM radcheck WHERE Username = '%{SQL-User-Name}' -> SELECT UserName,Value FROM radcheck WHERE Username = 'john'<br>query: SELECT UserName,Value FROM radcheck WHERE Username = 'john'<br> rlm_sql: The 'Attribute' field is empty or NULL, skipping the entire row.<br>rlm_sql (sql): Error getting data from database<br>rlm_sql (sql): SQL query error; rejecting user<br>rlm_sql (sql): Released sql socket id: 3<br> ++[sql] returns fail<br>Invalid user: [john/john] (from client localhost port 0)<br> Found Post-Auth-Type Reject<br>+- entering group REJECT<br> expand: %{User-Name} -> john<br> attr_filter: Matched entry DEFAULT at line 11<br> ++[attr_filter.access_reject] returns updated<br>Delaying reject of request 1 for 1 seconds<br>Going to the next request<br>Waking up in 0.9 seconds.<br>Sending delayed reject for request 1<br>Sending Access-Reject of id 115 to <a href="http://127.0.0.1">127.0.0.1</a> port 32834<br> Waking up in 4.9 seconds.<br>Cleaning up request 1 ID 115 with timestamp +23<br>Ready to process requests.<br><br><br><br>With Regards<br>Elangbam Johnson<br> -<br>List info/subscribe/unsubscribe? See <a href="http://www.freeradius.org/list/devel.html">http://www.freeradius.org/list/devel.html</a></blockquote></div><br><div> <span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-align: auto; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><span class="Apple-style-span" style="border-collapse: separate; color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: 2; text-indent: 0px; text-transform: none; white-space: normal; widows: 2; word-spacing: 0px; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; -webkit-text-decorations-in-effect: none; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px; "><div style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; "><div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; "><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 12px/normal Helvetica; ">-- </div><div><span class="Apple-style-span" style="font-family: Monaco; font-size: 10px; ">Guy Fraser</span></div><div><span class="Apple-style-span" style="font-family: Monaco; font-size: 10px; ">Network Administrator<br>The Internet Centre<br>1-888-450-6787<br>(780)450-6787</span></div></div></div></div></span></div></span></div></span> </div><br></div></body></html>