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@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
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-- Nick Porter Porter Computing Ltd Registered in England No 12659380