Fwd: Frerradius with M$ SQL-Problem with query.conf

[Niko Reinhard]

Betreff: Re: Frerradius with M$ SQL-Problem with query.conf
Datum: Mon, 22 Aug 2022 21:37:15 +0200
Von: Niko Reinhard <reiniko at web.de>
An: Alan DeKok <aland at deployingradius.com>


Hello Alan,

many thanks for this very quick response.

"My only suggestion here is to try tracking down...."
Hrmm. I can do that gladly, but I'm not so familiar with Freeradius and
I dont'know how to get the versions between 3.0.17 and .25... sorry.
Networkradius offers all versions between 0.20 and 0.25, this should be
enough for the start, but - I dont know how to get them! The baseurl is:
baseurl=http://packages.networkradius.com/freeradius-3.0/centos/$releasever/
and this gives me only the latest version...  can you help me with this
please?

Regarding my annex: The file is
/etc/raddb/mods-config/sql/main/mssql/queries.conf and the original
varaible was ${..class.column_name}, whitch this radiusd does not start.
I changed the variable on line 212, 282,390 and 491 to
${....class.column_name} and the the radiusd -X could be started.

Best regards
Reinhard


Am 22.08.2022 um 20:33 schrieb Alan DeKok:
> On Aug 22, 2022, at 1:58 PM, Niko Reinhard <reiniko at web.de> wrote:
>>
>> We use Freeradius V3.0.17 on Cent OS7 since few years for AAA for our
>> Cisco devices. We stored the AVPs in our existing M$ SQL 2008R2 database
>> and connected it via Freetds and the unixodbc package from Freeradius.
>> Because Cisco AAA needs the Cleartext-Password and we don't want to
>> store it in cleartext in our database we wrote a simple small function
>> which decrypt/encrypt the password before storing and after reading.
>> Therefore we changed the "queries.conf" as follows:
>>
>>   SELECT id, UserName, Attribute,dbo.DecryptPwd(Value, Attribute) as
>> Value, op \
>>   FROM ${authcheck_table} \
>>   WHERE Username = '%{SQL-User-Name}' \
>>   ORDER BY id"
>>
>> This works fine without any problems.
>
>    That's good.
>
>> Now we want to update our configuration and added a new server with
>> Oracle Linux 8 and Freeradius 3.2.0 (we installed with the sources and
>> as described at networkradius.com) in the same manner as we did before
>> (Freetds and freeradius-unixodbc) - but it works only if we use the
>> standard SQL query.
>
>    Hmm... We've had issues over the years with the standard SQL APIs not supporting anything other than a trivial "SELECT".   But all of those should have been fixed a while ago.
>
>> It does not work if we use the function or a View instead a table.
>> I tested our original query with tsql and isql and in both cases it
>> works fine. Then I installed  Freeadius 3.0.25 and it works also not,
>> but no problem with isql and tsql.
>
>    There really isn't a lot of difference in the rlm_sql module between 3.0.17 and 3.0.25.  The main thing is some unused functions were removed.
>
>> I added the radiusd -X output on the end of the mail. As you can see
>> there is no output for the Cleartext-Password.
>
>    The error show:
>
> 0) sql: Executing select query: SELECT id, UserName, Attribute,
> dbo.DecryptPwd(Value,Attribute) as Value, op FROM  radcheck WHERE
> Username = 'niklowitz' ORDER BY id
> (0) sql: User found in radcheck table
> (0) sql: Conditional check items matched, merging assignment check items
> (0) sql:   Cleartext-Password := ""
>
>    i.e. the output of the "Decrypt" call is empty.
>
>> What am I doing wrong? Are there any additional settings after 3.0.17?
>> Can someone help me with this?
>
>    It should work.
>
>    My only suggestion here is to try tracking down which release / commit broke it.  There's only a few versions between 3.0.17 and 3.0.25, and only a few changes to the rlm_sql file.  It shouldn't take longer than a few hours to track this down.
>
>    We don't run MS SQL here, so our testing ability is limited.
>
>> PS: There is an error in the file queries.conf at V3.0.25 from
>> networkradius.com, the new variable for Authorization query missed 2
>> dots (instead ${..conf it has to be ${....conf). I corrected it manually.
>
>    Which file is that?  There's more than one "queries.conf" file.
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html