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

[Nick Porter]

As a side note, if you install the freeradius-freetds package, there is 
no need to use unixodbc.

Set the driver option in mods-enabled/sql to be rlm_sql_freetds to use 
this driver.

This takes one layer of abstraction out of the whole connecting to MS SQL.

If you go down this route, you may want to use FreeRADIUS version 3.2 
(change freeradius-3.0 to freeradius-3.2 in your repo specification) as 
there was an issue fixed in the handling of the number of affected rows 
for the FreeTDS driver which is in the 3.2 packages.

Nick

On 22/08/2022 20:38, Niko Reinhard wrote:
> 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
>
> -
> List info/subscribe/unsubscribe? See 
> http://www.freeradius.org/list/users.html

-- 
Nick Porter

Porter Computing Ltd
Registered in England No 12659380