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','-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','-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','-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 expressio
