[prev in list] [next in list] [prev in thread] [next in thread] 

List:       freeradius-users
Subject:    Radius Accounting problem with MaxTnT
From:       "Dimitrios E. Digas" <freeradius () urnet ! gr>
Date:       2002-12-30 20:16:57
[Download RAW message or body]

Dear all,

I have a problem with radius accounting. More specifically I am using
freeradius v0.8 with oracle 8i backend. The problem arises with some
accounting records, all from a MaxTnT NAS. As can be seen from the log
entries below, the problem is that NAS does not send a username with some
accounting records. As a result the SQL query fails and the record
cannot be written into the database. Does anyone know how I can block such
accounting requests from the MaxTnT NAS or does anybody know if this is a
known MaxTnT bug ???

As a temporary solution I've modified the SQL query and used Oracle's NVL
function as follows to prevent a NULL username in the SQL statement:

accounting_start_query = "INSERT into ${acct_table1} (RadAcctId, AcctSessionId,
AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime,
AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop,
AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \
AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, \
AcctStopDelay) values('', '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
NVL(%{SQL-User-Name},
'dummyraduser'), '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port-Id}', \
'%{NAS-Port-Type}', TO_DATE('%S','yyyy-mm-dd hh24:mi:ss'), NULL, '0', \
'%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', \
'%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', \
'%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')"

This might do the trick but has a performance drawback. If anyone notices
any other problems this may cause please let me know.


RADIUS LOG
----------

rad_recv: Accounting-Request packet from host 217.19.74.12:7020, id=34, length=116
	NAS-IP-Address = 217.19.74.12
	NAS-Port = 2113
	NAS-Port-Type = Async
	Acct-Status-Type = Start
	Acct-Delay-Time = 229
	Acct-Session-Id = "369446656"
	Acct-Authentic = Local
	Idle-Timeout = 0
	X-Ascend-Modem-PortNo = 62
	X-Ascend-Modem-SlotNo = 1
	X-Ascend-Modem-ShelfNo = 1
	Calling-Station-Id = "2108150497"
	Called-Station-Id = "8962408080"
modcall: entering group preacct
  modcall[preacct]: module "preprocess" returns noop
    rlm_realm: Proxy reply, or no user name.  Ignoring.
  modcall[preacct]: module "suffix" returns noop
  modcall[preacct]: module "files" returns noop
modcall: group preacct returns noop
modcall: entering group accounting
rlm_acct_unique: WARNING: Attribute NAS-Port-Id was not found in request, unique ID \
                MAY be inconsistent
rlm_acct_unique: WARNING: Attribute User-Name was not found in request, unique ID MAY \
                be inconsistent
rlm_acct_unique: Hashing ',Client-IP-Address = 217.19.74.12,NAS-IP-Address = \
                217.19.74.12,Acct-Session-Id = "369446656",'
rlm_acct_unique: Acct-Unique-Session-ID = "e8b5c0d1af61a38f".
  modcall[accounting]: module "acct_unique" returns ok
radius_xlat:  '/usr/local/var/log/radius/radacct/217.19.74.12/detail-20021212'
rlm_detail: /usr/local/var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d \
expands to /usr/local/var/log/radius/radacct/217.19.74.12/detail-20021212  \
modcall[accounting]: module "detail" returns ok  modcall[accounting]: module \
"counter" returns noop  modcall[accounting]: module "unix" returns noop
radius_xlat:  '/usr/local/var/log/radius/radutmp'
radius_xlat:  ''
  modcall[accounting]: module "radutmp" returns ok
radius_xlat:  ''
radius_xlat:  'INSERT into radacct (RadAcctId, AcctSessionId, AcctUniqueId, UserName, \
Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, \
AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, \
AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, \
FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) values('', \
'369446656', 'e8b5c0d1af61a38f', '', '', '217.19.74.12', '', 'Async', \
TO_DATE('2002-12-12 21:54:56','yyyy-mm-dd hh24:mi:ss'), NULL, '0', 'Local', '', '', \
'0', '0', '8962408080', '2108150497', '', '', '', '', '229', '0')' rlm_sql (sql): \
                Reserving sql socket id: 3
rlm_sql_oracle: execute query failed in sql_query: ORA-01400: cannot insert NULL into \
("URNET"."RADACCT"."USERNAME") rlm_sql (sql): Attempting to connect rlm_sql_oracle #3
rlm_sql (sql): Connected new DB handle, #3
rlm_sql_oracle: execute query failed in sql_query: ORA-01400: cannot insert NULL into \
("URNET"."RADACCT"."USERNAME") rlm_sql (sql): failed after re-connect
rlm_sql (sql): Couldn't update SQL accounting for START packet - ORA-01400: cannot \
                insert NULL into ("URNET"."RADACCT"."USERNAME")
radius_xlat:  'UPDATE radacct SET AcctStartTime = TO_DATE('2002-12-12 \
21:54:56','yyyy-mm-dd hh24:mi:ss'), AcctStartDelay = '229', ConnectInfo_start = '' \
WHERE AcctSessionId = '369446656' AND UserName = '' AND NASIPAddress = '217.19.74.12' \
                AND AcctStopTime = IS NULL'
rlm_sql_oracle: execute query failed in sql_query: ORA-00936: missing expression
rlm_sql (sql): Attempting to connect rlm_sql_oracle #3
rlm_sql (sql): Connected new DB handle, #3
rlm_sql_oracle: execute query failed in sql_query: ORA-00936: missing expression
rlm_sql (sql): failed after re-connect
rlm_sql (sql): Couldn't update SQLaccounting START record - ORA-00936: missing \
expression rlm_sql (sql): Released sql socket id: 3
  modcall[accounting]: module "sql" returns fail
modcall: group accounting returns fail
Finished request 6


[prev in list] [next in list] [prev in thread] [next in thread]