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

[Niko Reinhard]

Thanks!
I read multiple times somewhere in the Internet documentation that the
freetds support was removed since version... 1 or 2 (didn't remember),
therefore I didn't try it by myself.
Good to know that this is not the case, I'll try it tomorrow and inform
you about the results.

Reinhard


Am 22.08.2022 um 22:41 schrieb 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
>