MSSQL with IPPOOL

[tnt at kalik.net]

Hm, it looks like mssql schema wasn't updated. There is no
group_check_query and authorize_group... queries look like ones in
1.1.x. That's probably why groups are not processed (unless you changed
read_groups to no in sql.conf). Also no priority field in radusergroup.

Try replacing group queries with those from mysql/dialup.conf and remove
"ORDER BY priority" in group_membership_query.

Ivan Kalik
Kalik Informatika ISP


Dana 24/9/2008, "Xiaochen Jing" <xjing at datavalet.com> piše:

>Thanks Alan,
>
>I am using 2.0.5.
>
>Like I said, I have three tables defined in MSSQL, usergroup table, radcheck
>table and radgroupcheck table. I have GroupName and Pool-Name defined in
>radgroupcheck table.
>
>If I put two attributes (Cleartext-Password and Pool-Name) in radcheck
>table, in two lines, with the same UserName, it works. User gets
>authenticated and IP.
>
>If I put Pool-Name attribute in radgroupcheck table, then user only get
>authenticated, no IP.
>
>
>Here is the whole debug.
>
>rad_recv: Access-Request packet from host 172.16.1.5 port 4523, id=8,
>length=51
>        User-Name = "sqltestuser"
>        User-Password = "sqltesting"
>+- entering group authorize
>++[preprocess] returns ok
>++[chap] returns noop
>++[mschap] returns noop
>    rlm_realm: No '@' in User-Name = "sqltestuser", looking up realm NULL
>    rlm_realm: No such realm "NULL"
>++[suffix] returns noop
>  rlm_eap: No EAP-Message, not doing EAP
>++[eap] returns noop
>++[unix] returns notfound
>++[files] returns noop
>        expand: %{User-Name} -> sqltestuser
>rlm_sql (sql): sql_set_user escaped user --> 'sqltestuser'
>rlm_sql (sql): Reserving sql socket id: 4
>        expand: SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE
>Username = '%{SQL-User-Name}' ORDER BY id -> SELECT
>id,UserName,Attribute,Value,op FROM radcheck WHERE Username = 'sqltestuser'
>ORDER BY id
>query:  SELECT id,UserName,Attribute,Value,op FROM radcheck WHERE Username =
>'sqltestuser' ORDER BY id
>rlm_sql (sql): User found in radcheck table
>        expand: SELECT id,UserName,Attribute,Value,op FROM radreply WHERE
>Username = '%{SQL-User-Name}' ORDER BY id -> SELECT
>id,UserName,Attribute,Value,op FROM radreply WHERE Username = 'sqltestuser'
>ORDER BY id
>query:  SELECT id,UserName,Attribute,Value,op FROM radreply WHERE Username =
>'sqltestuser' ORDER BY id
>rlm_sql (sql): Released sql socket id: 4
>++[sql] returns ok
>++[expiration] returns noop
>++[logintime] returns noop
>++[pap] returns updated
>  rad_check_password:  Found Auth-Type
>auth: type "PAP"
>+- entering group PAP
>rlm_pap: login attempt with password "sqltesting"
>rlm_pap: Using clear text password "sqltesting"
>rlm_pap: User authenticated successfully
>++[pap] returns ok
>+- entering group post-auth
>rlm_ippool: Could not find Pool-Name attribute.
>++[netplus] returns noop
>rlm_sql (sql): Processing sql_postauth
>        expand: %{User-Name} -> sqltestuser
>rlm_sql (sql): sql_set_user escaped user --> 'sqltestuser'
>++[sql] returns noop
>Sending Access-Accept of id 8 to 172.16.1.5 port 4523
>Finished request 0.
>Going to the next request
>Waking up in 4.9 seconds.
>Cleaning up request 0 ID 8 with timestamp +10
>Ready to process requests.
>
>
>
>
>-----Original Message-----
>From: freeradius-users-bounces+xjing=datavalet.com at lists.freeradius.org
>[mailto:freeradius-users-bounces+xjing=datavalet.com at lists.freeradius.org]
>On Behalf Of tnt at kalik.net
>Sent: Wednesday, September 24, 2008 4:04 PM
>To: FreeRadius users mailing list
>Subject: Re: MSSQL with IPPOOL
>
>What version is this? In current schema group membership table is
>radusergroup not usergroup. Post the whole debug. From this we can only
>say that data from radgroupcheck table didn't make it. The bit why is
>missing.
>
>Ivan Kalik
>Kalik Informatika ISP
>
>
>Dana 24/9/2008, "Xiaochen Jing" <xjing at datavalet.com> piše:
>
>>
>>
>>Hello all,
>>
>>
>>
>>I recently got Freeradius with MS SQL to work together. But there is still
>>one thing to be completed, the IP Pool thing.
>>
>>
>>
>>I put Pool-Name and it value in radgroupcheck table in MS SQL. I have
>>Pool-Name set up in radiusd.conf.
>>
>>
>>
>>I test, user get authenticated by username/password, but no IP address is
>>assigned. Here is the error:
>>
>>
>>
>>+- entering group post-auth
>>
>>rlm_ippool: Could not find Pool-Name attribute.
>>
>>++[main_pool] returns noop
>>
>>rlm_sql (sql): Processing sql_postauth
>>
>>        expand: %{User-Name} -> easyuser
>>
>>rlm_sql (sql): sql_set_user escaped user --> 'easyuser'
>>
>>++[sql] returns noop
>>
>>
>>
>>I don't know where should I put Pool-Name in rlm_ippool? Or something
>>related to group post-auth? But I did find anything config file about it.
>>
>>
>>
>>With another method, which is that I put Pool-Name and its value in
>Radcheck
>>table in MS SQL, everything works. User gets authenticated and gets an IP
>>address from the pool.
>>
>>
>>
>>Can anyone shed lights on it? Thanks in advance
>>
>>
>>
>>
>>
>>Here is my config in MS SQL
>>
>>
>>
>>Usergroup Table:
>>
>>
>>
>>id                     | UserName
>>| GroupName                                                       |
>>
>>+------------------------+-------------------------------------------------
>-
>>---------------+-----------------------------------------------------------
>-
>>-----+
>>
>>| 1                      | easyuser
>>| secretary                                                          |
>>
>>
>>
>>
>>
>>Radcheck table:
>>
>>
>>
>>id                     | UserName
>>| Attribute                       | Value
>>| op|
>>
>>+------------------------+-------------------------------------------------
>-
>>---------------+---------------------------------+-------------------------
>-
>>---------------------------------------------------------------------------
>-
>>---------------------------------------------------------------------------
>-
>>---------------------------------------------------------------------------
>-
>>+---+
>>
>>| 1                      | easyuser
>>| Cleartext-Password        | easypass
>>| :=|
>>
>>
>>
>>
>>
>>Radgroupcheck table:
>>
>>
>>
>>id                     | GroupName
>>| Attribute                       | Value
>>| op|
>>
>>+------------------------+-------------------------------------------------
>-
>>---------------+---------------------------------+-------------------------
>-
>>---------------------------------------------------------------------------
>-
>>---------------------------------------------------------------------------
>-
>>---------------------------------------------------------------------------
>-
>>+---+
>>
>>| 1                      | secretary
>>| Pool-Name                     |   main_pool
>>| :=|
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>-
>List info/subscribe/unsubscribe? See
>http://www.freeradius.org/list/users.html
>
>
>-
>List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
>