On Aug 22, 2022, at 1:58 PM, Niko Reinhard <reiniko@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.