mssql-freetds interim-update problem

[Alan DeKok]

On Mar 8, 2020, at 10:01 AM, Ali Arslan via Freeradius-Users <freeradius-users at lists.freeradius.org> wrote:
> 
> I am using freeradius 3.0.16 on 2 ubuntu 18.04 servers. One is using mysql the other mssql server as backends. The radius server using mysql is working properly but the one that is using mssql with freetds inserts new rows in radacct table for interim updates.

  New rows are inserted when there is no pre-existing rows.

> The debug output parts for interim update are as follows:

  The debug output shows the SQL queries which are being executed.

> (3) sql: EXPAND %{tolower:type.%{Acct-Status-Type}.query}
> (3) sql:    --> type.interim-update.query
> (3) sql: Using query template 'query'
> rlm_sql (sql): Closing connection (3): Hit idle_timeout, was idle for 120 seconds
> rlm_sql_freetds: socket destructor called, closing socket
> rlm_sql (sql): Reserved connection (1)
> (3) sql: EXPAND %{User-Name}
> (3) sql:    --> test at orbit
> (3) sql: SQL-User-Name set to 'test at orbit'
> (3) sql: EXPAND UPDATE radacct SET AcctUpdateTime  = '%S', AcctStopTime = NULL, FramedIPAddress = '%{Framed-IP-Address}', FramedIPv6Address = '%{Framed-IPv6-Address}', FramedIPv6Prefix = '%{Framed-IPv6-Prefix}', FramedInterfaceId = '%{Framed-Interface-Id}', DelegatedIPv6Prefix = '%{Delegated-IPv6-Prefix}', AcctSessionTime = %{%{Acct-Session-Time}:-NULL}, AcctInputOctets = convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', AcctOutputOctets = convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}' WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}'
> (3) sql:    --> UPDATE radacct SET AcctUpdateTime  = '2020-03-08 16:07:09', AcctStopTime = NULL, FramedIPAddress = '192.168.10.255', FramedIPv6Address = '', FramedIPv6Prefix = '', FramedInterfaceId = '', DelegatedIPv6Prefix = '', AcctSessionTime = 600, AcctInputOctets = convert(bigint, '0' * POWER(2.0, 32)) | '407068', AcctOutputOctets = convert(bigint, '0' * POWER(2.0, 32)) | '687067' WHERE AcctUniqueId = '72029846d9c951718be3d5e42432e88f'
> (3) sql: Executing query: UPDATE radacct SET AcctUpdateTime  = '2020-03-08 16:07:09', AcctStopTime = NULL, FramedIPAddress = '192.168.10.255', FramedIPv6Address = '', FramedIPv6Prefix = '', FramedInterfaceId = '', DelegatedIPv6Prefix = '', AcctSessionTime = 600, AcctInputOctets = convert(bigint, '0' * POWER(2.0, 32)) | '407068', AcctOutputOctets = convert(bigint, '0' * POWER(2.0, 32)) | '687067' WHERE AcctUniqueId = '72029846d9c951718be3d5e42432e88f'
> (3) sql: SQL query returned: success
> (3) sql: -1 record(s) updated
> 
> ******************** here it tries next query and inserts new row  **********************

  Because no row was updated.

> Why it inserts a new row in interim-update after updating record?

  Because there's no pre-existing row.

  Read ALL of the debug output for one user.  Does the server get an accounting "start" packet?  If so, what's in it?  What is inserted into the database?  How does that information differ from an "update" packet?

  FreeRADIUS depends on the NAS to send the same information in each packet.  If the NAS sends *different* information in the start and update packets... well, FreeRADIUS can't do much about that.

  Alan DeKok.