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