Simple question for migration to Freeradius

[Olaf s]

After many years we want to migrate from Steel-belted Radius to Freeradius.


Current Freeradius setup (and working)

Centos 7.5

MSSQL (due to application requirements)

Default sql schema as provided with Freeradius.


The only requirement we have is that we need to lookup the username in a table somewhere in our own mssql db (not the freeradius db)

If the username is in the table: send accept. (not even using password)


From debug i can see this:


(0) sql: EXPAND %{User-Name}
(0) sql: --> 32490013984
(0) sql: SQL-User-Name set to '32490013984'
rlm_sql (sql): Reserved connection (0)
(0) sql: EXPAND SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id
(0) sql: --> SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '32490013984' ORDER BY id
(0) sql: Executing select query: SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '32490013984' ORDER BY id
(0) sql: User found in radcheck table

how can i adjust the select query and change it to fit my own sql schema.
I could not find it in main/mssql/queries.conf

Is it possible?
Thanks!




-
List info/subscribe/unsubscribe? See http

[Alan Buxey]

hi,

you just need to adjust the DB table names in the main sql module config
(eg radius_db and authcheck_table values). you
arent constrained by the default schema/DB - i've taken random older eg
captive portal DBs and made them work without
any DB change with FreeRADIUS

alan
-
List info/subscribe/unsubscribe? See http://www.freeradius.o

[Alan DeKok]

On Oct 12, 2018, at 1:56 AM, Olaf s <***@hotmail.com> wrote:
>
> After many years we want to migrate from Steel-belted Radius to Freeradius.

That's good!

> Current Freeradius setup (and working)
>
> Centos 7.5
>
> MSSQL (due to application requirements)
>
> Default sql schema as provided with Freeradius.

You don't need to use that. It's a default, not a requirement.

>
> The only requirement we have is that we need to lookup the username in a table somewhere in our own mssql db (not the freeradius db)
>
> If the username is in the table: send accept. (not even using password)

You can run SQL queries yourself, pretty much anywhere in the server configuration. You don't need to use the "sql" module (as such), or it's schema, or the queries,

authorize {
...

if ("%{sql: SELECT user from my_table WHERE user='%{User-Name}'" != "") {
accept
}
..

That should do it.

> From debug i can see this:
>
>
> (0) sql: EXPAND %{User-Name}
> (0) sql: --> 32490013984
> (0) sql: SQL-User-Name set to '32490013984'
> rlm_sql (sql): Reserved connection (0)
> (0) sql: EXPAND SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id
> (0) sql: --> SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '32490013984' ORDER BY id
> (0) sql: Executing select query: SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '32490013984' ORDER BY id
> (0) sql: User found in radcheck table
>
> how can i adjust the select query and change it to fit my own sql schema.

Don't do that. The default queries are meant to be used with the default schema. If you're using a custom schema, just ignore the default queries.

Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.

[Olaf s]

Hello Alan,
I was able to implement your recommendations. All "works" now with our own db.

The only issue i have is that my accept response is 0 bytes:
Example:
Mon Oct 15 18:58:18 2018 : Auth: (2) Login OK: [467190011637707] (from client 192.168.2.2/32 port 0 cli 467190011637707)
Mon Oct 15 18:58:18 2018 : Debug: (2) Sent Access-Accept Id 75 from 172.24.2.35:1812 to 192.168.2.2:52166 length 0
Mon Oct 15 18:58:18 2018 : Debug: (2) Finished request

While my response in Steelbelted radius shows this:

10/15/2018 18:53:36 User 467190011637707 being passed to attribute editing authentication methods
10/15/2018 18:53:36 Class subattribute: DistName : String Value = 467190011637707
10/15/2018 18:53:36 Class subattribute: AuthType : String Value = 200
10/15/2018 18:53:36 Class subattribute: TransactionId : Value =
10/15/2018 18:53:36 000: edbad986 6b514652 00007605 |....kQFR..v. |
10/15/2018 18:53:36 Sent accept response for user 467190011637707 to clienttest
10/15/2018 18:53:36 -----------------------------------------------------------
10/15/2018 18:53:36 Authentication Response
10/15/2018 18:53:36 Packet : Code = 0x2 ID = 0x79
10/15/2018 18:53:36 Vector =
10/15/2018 18:53:36 000: c5de6632 e70a74b9 ca26adeb 5ded3755 |..f2..t..&..].7U|
10/15/2018 18:53:36 Class : Value =
10/15/2018 18:53:36 000: 53425232 434cf6ee db98b3ad a2c6a980 |SBR2CL..........|
10/15/2018 18:53:36 010: 11803001 80048199 8c868002 8012819a |..0.............|
10/15/2018 18:53:36 020: 8dc6f389 e4e0b098 cca6e399 dceeb098 |................|
10/15/2018 18:53:36 030: c012800e 81f6eedb 98b3ada2 c6a98080 |................|
10/15/2018 18:53:36 040: 87b094 |... |
10/15/2018 18:53:36 -----------------------------------------------------------
10/15/2018 18:53:36 Packet containing 89 bytes successfully sent

It looks like im missing Class subattributes (and even more?)
What to look for... ? could you point me into the right direction?

Thanks,
Olaf


________________________________
From: Freeradius-Users <freeradius-users-bounces+siegson=***@lists.freeradius.org> on behalf of Alan DeKok <***@deployingradius.com>
Sent: Friday, October 12, 2018 4:09 PM
To: FreeRadius users mailing list
Subject: Re: Simple question for migration to Freeradius

On Oct 12, 2018, at 1:56 AM, Olaf s <***@hotmail.com> wrote:
>
> After many years we want to migrate from Steel-belted Radius to Freeradius.

That's good!

> Current Freeradius setup (and working)
>
> Centos 7.5
>
> MSSQL (due to application requirements)
>
> Default sql schema as provided with Freeradius.

You don't need to use that. It's a default, not a requirement.

>
> The only requirement we have is that we need to lookup the username in a table somewhere in our own mssql db (not the freeradius db)
>
> If the username is in the table: send accept. (not even using password)

You can run SQL queries yourself, pretty much anywhere in the server configuration. You don't need to use the "sql" module (as such), or it's schema, or the queries,

authorize {
...

if ("%{sql: SELECT user from my_table WHERE user='%{User-Name}'" != "") {
accept
}
..

That should do it.

> From debug i can see this:
>
>
> (0) sql: EXPAND %{User-Name}
> (0) sql: --> 32490013984
> (0) sql: SQL-User-Name set to '32490013984'
> rlm_sql (sql): Reserved connection (0)
> (0) sql: EXPAND SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id
> (0) sql: --> SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '32490013984' ORDER BY id
> (0) sql: Executing select query: SELECT id, UserName, Attribute, Value, op FROM radcheck WHERE Username = '32490013984' ORDER BY id
> (0) sql: User found in radcheck table
>
> how can i adjust the select query and change it to fit my own sql schema.

Don't do that. The default queries are meant to be used with the default schema. If you're using a custom schema, just ignore the default queries.

Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.fre

[Alan DeKok]

On Oct 15, 2018, at 3:16 PM, Olaf s <***@hotmail.com> wrote:
>
> I was able to implement your recommendations. All "works" now with our own db.

That's good.

> The only issue i have is that my accept response is 0 bytes:

That's what the default configuration does.

If you want it to do more, you need to configure it to do more.

> It looks like im missing Class subattributes (and even more?)
> What to look for... ? could you point me into the right direction?

Where do those attributes come from? FreeRADIUS can't just invent them, it has to get them from somewhere...

Alan DeKok.
-
List info/subscribe/unsu