sqlippool - Duplicate IP

Neville nev at itsnev.co.uk
Tue Sep 1 00:05:38 CEST 2009


Hi,

I've facing a problem since rebuild, where every user is being allocated the same IP from the sqlippool, and I'm not sure why this is happening.

I have a DaloRadius / FreeRadius2.1.6 / Poptop (pptpd) 1.3.4 / ppp 2.4.4.-2 / mysql 5.0.45

pppd does not pass back Client-IP-Address or Client-Station-Id

table structure for radipool is

( `id` int(11) unsigned NOT NULL auto_increment,
`pool_name` varchar(30) NOT NULL, 
`framedipaddress` varchar(15) NOT NULL default '',
`nasipaddress` varchar(15) NOT NULL default '', 
`calledstationid` varchar(30) NOT NULL,
`callingstationid` varchar(30) NOT NULL,
`expiry_time` datetime default NULL,
`username` varchar(64) NOT NULL default '',
`pool_key` varchar(30) NOT NULL, PRIMARY KEY (`id`) )

1st Login

Ready to process requests.
rad_recv: Access-Request packet from host NASIPHERE port 53621, id=117, length=147
        Service-Type = Framed-User
        Framed-Protocol = PPP
        User-Name = "TESTUSER"
        MS-CHAP-Challenge = 0xe325bfbeb22fbbb7a33a21326e5ce18a
        MS-CHAP2-Response = 0x51009da7f84750dd0f01bed231e11bab1f9a00000000000000002b9f4dad6844332eaec4aabcc1d8f03911ff654b6a7a8e96
        NAS-Identifier = "NASIPHERE"
        NAS-Port = 0
+- entering group authorize {...}
++[preprocess] returns ok
[auth_log]      expand: /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d -> /var/log/radius/radacct/NASIPHERE/auth-detail-20090831
[auth_log] /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d expands to /var/log/radius/radacct/NASIPHERE/auth-detail-20090831
[auth_log]      expand: %t -> Mon Aug 31 22:47:05 2009
++[auth_log] returns ok
[mschap] Found MS-CHAP attributes.  Setting 'Auth-Type  = mschap'
++[mschap] returns ok
[sql]   expand: %{User-Name} -> TESTUSER
[sql] sql_set_user escaped user --> 'TESTUSER'
rlm_sql (sql): Reserving sql socket id: 3
[sql]   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 = 'TESTUSER'           ORDER BY id
[sql] User found in radcheck table
[sql]   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 = 'TESTUSER'           ORDER BY id
[sql]   expand: SELECT groupname           FROM radusergroup           WHERE username = '%{SQL-User-Name}'           ORDER BY priority -> SELECT groupname           FROM radusergroup           WHERE username = 'TESTUSER'           ORDER BY priority
[sql]   expand: SELECT id, groupname, attribute,           Value, op           FROM radgroupcheck           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,           Value, op           FROM radgroupcheck           WHERE groupname = 'USUKTV'           ORDER BY id
[sql] User found in group USUKTV
[sql]   expand: SELECT id, groupname, attribute,           value, op           FROM radgroupreply           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,           value, op           FROM radgroupreply           WHERE groupname = 'USUKTV'           ORDER BY id
rlm_sql (sql): Released sql socket id: 3
++[sql] returns ok
++[expiration] returns noop
++[logintime] returns noop
Found Auth-Type = MSCHAP
+- entering group MS-CHAP {...}
[mschap] Told to do MS-CHAPv2 for TESTUSER with NT-Password
[mschap] adding MS-CHAPv2 MPPE keys
++[mschap] returns ok
+- entering group session {...}
++[sql] returns noop
Login OK: [TESTUSER/<via Auth-Type = mschap>] (from client VPN1-UK port 0)
+- entering group post-auth {...}
rlm_sql (sql): Reserving sql socket id: 2
[sqlippool]     expand: %{User-Name} -> TESTUSER
[sqlippool] sql_set_user escaped user --> 'TESTUSER'
[sqlippool]     expand: START TRANSACTION -> START TRANSACTION
[sqlippool]     expand: UPDATE radippool   SET nasipaddress = '', pool_key = 0,   callingstationid = '', username = '',   expiry_time = NULL   WHERE expiry_time <= NOW() - INTERVAL 1 SECOND   AND nasipaddress = '%{Nas-IP-Address}' -> UPDATE radippool   SET nasipaddress = '', pool_key = 0,   callingstationid = '', username = '',   expiry_time = NULL   WHERE expiry_time <= NOW() - INTERVAL 1 SECOND   AND nasipaddress = 'NASIPHERE'
[sqlippool]     expand: SELECT framedipaddress FROM radippool  WHERE pool_name = '%{control:Pool-Name}' AND (expiry_time < NOW() OR expiry_time IS NULL)  ORDER BY (username <> '%{User-Name}'),  (callingstationid <> '%{Calling-Station-Id}'),  expiry_time  LIMIT 1  FOR UPDATE -> SELECT framedipaddress FROM radippool  WHERE pool_name = 'tvpool' AND (expiry_time < NOW() OR expiry_time IS NULL)  ORDER BY (username <> 'TESTUSER'),  (callingstationid <> ''),  expiry_time  LIMIT 1  FOR UPDATE
[sqlippool]     expand: UPDATE radippool  SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{NAS-Port}',  callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}',  expiry_time = NOW() + INTERVAL 3600 SECOND  WHERE framedipaddress = '192.168.0.5' AND expiry_time IS NULL -> UPDATE radippool  SET nasipaddress = 'NASIPHERE', pool_key = '0',  callingstationid = '', username = 'TESTUSER',  expiry_time = NOW() + INTERVAL 3600 SECOND  WHERE framedipaddress = '192.168.0.5' AND expiry_time IS NULL
[sqlippool] Allocated IP 192.168.0.5 [0500a8c0]
[sqlippool]     expand: COMMIT -> COMMIT 


2nd Login, same user ID


Ready to process requests.
rad_recv: Access-Request packet from host NASIPHERE port 37354, id=119, length=147
        Service-Type = Framed-User
        Framed-Protocol = PPP
        User-Name = "TESTUSER"
        MS-CHAP-Challenge = 0x29bb177d40bae79e1ad242c8d9383704
        MS-CHAP2-Response = 0x4400294af6559f212980c4c7942ba81c19e600000000000000003100115915f0d911531610a4f4f639f56de1115a1f9ca249
        NAS-Identifier = "NASIPHERE"
        NAS-Port = 1
+- entering group authorize {...}
++[preprocess] returns ok
[auth_log]      expand: /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d -> /var/log/radius/radacct/NASIPHERE/auth-detail-20090831
[auth_log] /var/log/radius/radacct/%{Client-IP-Address}/auth-detail-%Y%m%d expands to /var/log/radius/radacct/NASIPHERE/auth-detail-20090831
[auth_log]      expand: %t -> Mon Aug 31 22:47:35 2009
++[auth_log] returns ok
[mschap] Found MS-CHAP attributes.  Setting 'Auth-Type  = mschap'
++[mschap] returns ok
[sql]   expand: %{User-Name} -> TESTUSER
[sql] sql_set_user escaped user --> 'TESTUSER'
rlm_sql (sql): Reserving sql socket id: 3
[sql]   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 = 'TESTUSER'           ORDER BY id
[sql] User found in radcheck table
[sql]   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 = 'TESTUSER'           ORDER BY id
[sql]   expand: SELECT groupname           FROM radusergroup           WHERE username = '%{SQL-User-Name}'           ORDER BY priority -> SELECT groupname           FROM radusergroup           WHERE username = 'TESTUSER'           ORDER BY priority
[sql]   expand: SELECT id, groupname, attribute,           Value, op           FROM radgroupcheck           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,           Value, op           FROM radgroupcheck           WHERE groupname = 'USUKTV'           ORDER BY id
[sql] User found in group USUKTV
[sql]   expand: SELECT id, groupname, attribute,           value, op           FROM radgroupreply           WHERE groupname = '%{Sql-Group}'           ORDER BY id -> SELECT id, groupname, attribute,           value, op           FROM radgroupreply           WHERE groupname = 'USUKTV'           ORDER BY id
rlm_sql (sql): Released sql socket id: 3
++[sql] returns ok
++[expiration] returns noop
++[logintime] returns noop
Found Auth-Type = MSCHAP
+- entering group MS-CHAP {...}
[mschap] Told to do MS-CHAPv2 for TESTUSER with NT-Password
[mschap] adding MS-CHAPv2 MPPE keys
++[mschap] returns ok
+- entering group session {...}
++[sql] returns noop
Login OK: [TESTUSER/<via Auth-Type = mschap>] (from client VPN1-UK port 1)
+- entering group post-auth {...}
rlm_sql (sql): Reserving sql socket id: 2
[sqlippool]     expand: %{User-Name} -> TESTUSER
[sqlippool] sql_set_user escaped user --> 'TESTUSER'
[sqlippool]     expand: START TRANSACTION -> START TRANSACTION
[sqlippool]     expand: UPDATE radippool   SET nasipaddress = '', pool_key = 0,   callingstationid = '', username = '',   expiry_time = NULL   WHERE expiry_time <= NOW() - INTERVAL 1 SECOND   AND nasipaddress = '%{Nas-IP-Address}' -> UPDATE radippool   SET nasipaddress = '', pool_key = 0,   callingstationid = '', username = '',   expiry_time = NULL   WHERE expiry_time <= NOW() - INTERVAL 1 SECOND   AND nasipaddress = 'NASIPHERE'
[sqlippool]     expand: SELECT framedipaddress FROM radippool  WHERE pool_name = '%{control:Pool-Name}' AND (expiry_time < NOW() OR expiry_time IS NULL)  ORDER BY (username <> '%{User-Name}'),  (callingstationid <> '%{Calling-Station-Id}'),  expiry_time  LIMIT 1  FOR UPDATE -> SELECT framedipaddress FROM radippool  WHERE pool_name = 'tvpool' AND (expiry_time < NOW() OR expiry_time IS NULL)  ORDER BY (username <> 'TESTUSER'),  (callingstationid <> ''),  expiry_time  LIMIT 1  FOR UPDATE
[sqlippool]     expand: UPDATE radippool  SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{NAS-Port}',  callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}',  expiry_time = NOW() + INTERVAL 3600 SECOND  WHERE framedipaddress = '192.168.0.5' AND expiry_time IS NULL -> UPDATE radippool  SET nasipaddress = 'NASIPHERE', pool_key = '1',  callingstationid = '', username = 'TESTUSER',  expiry_time = NOW() + INTERVAL 3600 SECOND  WHERE framedipaddress = '192.168.0.5' AND expiry_time IS NULL
[sqlippool] Allocated IP 192.168.0.5 [0500a8c0]
[sqlippool]     expand: COMMIT -> COMMIT


Thx
Nev
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20090831/3dfebcef/attachment.html>


More information about the Freeradius-Users mailing list