Frerradius with M$ SQL-Problem with query.conf

[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.