FreeTDS affected_rows_count Problem, Virtual Servers - nas_table Server Column Problem

[Ali Arslan]

Hello,

Today i will post 2 problems together that i have already sent by 2 different emails but still i cannot resolve without taking side ways.
I will give earlier email links but both of the problems will be stated completely here again not to make reader to consult other sources of the problem statement:


Note: Though i overcome the problems here i thought you may need to know about them to make already the best radius server on the globe better.

1- FreeTDS affected_rows_count Problem earlier email:
    http://freeradius.1045715.n5.nabble.com/mssql-freetds-interim-update-problem-td5757217.html

2- Virtual Servers - nas_table Server Column Problem earlier email
    http://freeradius.1045715.n5.nabble.com/Virtual-Servers-amp-nas-table-Server-Column-Problem-td5758312.html


Problem Statement

1-  FreeTDS affected_rows_count Problem
    Since rlm_sql_freetds does not get the true affected rows count it continues to insert new records in radacct table for the same session in interim updates.
I attached debug output (debug_freetds.txt).
Some lines from debug that will summarize the problem are as follows:

(3) WISPSQL: Executing query: UPDATE radacct SET AcctInterval = DATEDIFF(second, AcctUpdateTime, GETDATE()), AcctUpdateTime  = GETDATE(), AcctStopTime = NULL, AcctSessionTime = 121, FramedIPAddress = '192.168.10.255', AcctInputOctets = convert(bigint, '0' * POWER(2.0, 32)) | '95438', AcctOutputOctets = convert(bigint, '0' * POWER(2.0, 32)) | '45977' WHERE AcctUniqueId = '69913b8866eac834270ab589229ef6ab'
(3) WISPSQL: SQL query returned: success
(3) WISPSQL: -1 record(s) updated
(3) WISPSQL: Trying next query...
(3) WISPSQL: EXPAND IF NOT EXISTS(SELECT 1 FROM radacct WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}') INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctUpdateTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfoStart, ConnectInfoStop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress) VALUES('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{%{Packet-Src-IP-Address}:-%{NAS-IP-Address}}', '%{%{NAS-Port-Id}:-%{NAS-Port}}', '%{NAS-Port-Type}', DATEADD(second, -1 * (%{%{Acct-Session-Time}:-0}), GETDATE()), GETDATE(), NULL, %{%{Acct-Session-Time}:-NULL}, '%{Acct-Authentic}', '%{Connect-Info}', NULL, convert(bigint, '%{%{Acct-Input-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Input-Octets}:-0}', convert(bigint, '%{%{Acct-Output-Gigawords}:-0}' * POWER(2.0, 32)) | '%{%{Acct-Output-Octets}:-0}', '%{Called-Station-Id}', '%{Calling-Station-Id}', NULL, '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}')
(3) WISPSQL:    --> IF NOT EXISTS(SELECT 1 FROM radacct WHERE AcctUniqueId = '69913b8866eac834270ab589229ef6ab') INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctUpdateTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfoStart, ConnectInfoStop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress) VALUES('81600002', '69913b8866eac834270ab589229ef6ab', 'test at orbit', '', '192.168.1.200', 'LAN', 'Ethernet', DATEADD(second, -1 * (121), GETDATE()), GETDATE(), NULL, 121, 'RADIUS', '', NULL, convert(bigint, '0' * POWER(2.0, 32)) | '95438', convert(bigint, '0' * POWER(2.0, 32)) | '45977', 'PPPoE', 'D8:C4:97:15:C1:F8', NULL, 'Framed-User', 'PPP', '192.168.10.255')
(3) WISPSQL: Executing query: IF NOT EXISTS(SELECT 1 FROM radacct WHERE AcctUniqueId = '69913b8866eac834270ab589229ef6ab') INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctUpdateTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfoStart, ConnectInfoStop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress) VALUES('81600002', '69913b8866eac834270ab589229ef6ab', 'test at orbit', '', '192.168.1.200', 'LAN', 'Ethernet', DATEADD(second, -1 * (121), GETDATE()), GETDATE(), NULL, 121, 'RADIUS', '', NULL, convert(bigint, '0' * POWER(2.0, 32)) | '95438', convert(bigint, '0' * POWER(2.0, 32)) | '45977', 'PPPoE', 'D8:C4:97:15:C1:F8', NULL, 'Framed-User', 'PPP', '192.168.10.255')
(3) WISPSQL: SQL query returned: success
(3) WISPSQL: -1 record(s) updated
(3) WISPSQL: No additional queries configured
rlm_sql (WISPSQL): Released connection (8)
(3)     [WISPSQL] = noop
(3)     update control {
(3)       EXPAND %{User-Name}
(3)          --> test at orbit
(3)       SQL-User-Name set to 'test at orbit'
rlm_sql (WISPSQL): Reserved connection (7)

Here update runs and returns success
    (3) WISPSQL: SQL query returned: success
    (3) WISPSQL: -1 record(s) updated

but since it does not return true updated rows count (-1 record(s) updated) so it tries to insert new record
    (3) WISPSQL: Executing query: IF NOT EXISTS(SELECT 1 FROM radacct WHERE AcctUniqueId = '69913b8866eac834270ab589229ef6ab') INSERT INTO radacct (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctUpdateTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfoStart, ConnectInfoStop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress) VALUES('81600002', '69913b8866eac834270ab589229ef6ab', 'test at orbit', '', '192.168.1.200', 'LAN', 'Ethernet', DATEADD(second, -1 * (121), GETDATE()), GETDATE(), NULL, 121, 'RADIUS', '', NULL, convert(bigint, '0' * POWER(2.0, 32)) | '95438', convert(bigint, '0' * POWER(2.0, 32)) | '45977', 'PPPoE', 'D8:C4:97:15:C1:F8', NULL, 'Framed-User', 'PPP', '192.168.10.255')
    (3) WISPSQL: SQL query returned: success
    (3) WISPSQL: -1 record(s) updated

To disable insert i check if there is already a record for the same session
    IF NOT EXISTS(SELECT 1 FROM radacct WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}')

To check true affected rows count i added an unlang statement in accounting section, it does not do anything but just to check if sql server returns true count:
    update control {
        Tmp-Integer-0 = "%{WISPSQL:SELECT @@ROWCOUNT}"
    }

    (3)       EXPAND %{WISPSQL:SELECT @@ROWCOUNT}
    (3)          --> 1
    (3)       Tmp-Integer-0 = 1
as seen it returns count as 1.

I also checked affected rows count by freetds utility tsql and saw that it returns true values with select @@ROWCOUNT statement.

I downloaded freeradius source code from gitHub to see if i can find anything about the way it gets affected rows count:

the 2 methods in rlm_sql_freetds.c file related are:

static int sql_num_rows(rlm_sql_handle_t *handle, UNUSED rlm_sql_config_t *config)
{
    rlm_sql_freetds_conn_t *conn = handle->conn;
    CS_INT    num;

    if (ct_res_info(conn->command, CS_ROW_COUNT, &num, CS_UNUSED, NULL) != CS_SUCCEED) {
        ERROR("rlm_sql_freetds: error retrieving row count");


        return RLM_SQL_ERROR;
    }

    return num;
}

static int sql_affected_rows(rlm_sql_handle_t *handle, rlm_sql_config_t *config)
{
    return sql_num_rows(handle, config);
}

the second being a recall of the first.

Here ct_res_info method (cannot find its definition) returns affected_rows count, may be its a freetds library method or calling a method from library, and seems that that freetds library method does not do its job.
Can't it be possible to use a direct call to sql server to get @@ROWSCOUNT by a select statment to get affected rows count?


2- Virtual Servers - nas_table Server Column Problem
    I want to use vertiaul servers and add specific clients to specific servers. And to do that i set Server columns of nas_table records with virtual server name for wich i want to attach.
I encountered 2 problems with this method:
    1 - Multiple clients cannot be added to the same virtual server, if tried get error like:
        rlm_sql (sql): Processing generate_sql_clients
        rlm_sql (sql): Executing select query: SELECT Id, IPAddress, ShortName, 'other', Secret, ISNULL(Server, '') FROM nas WHERE IsActive = 1
        rlm_sql (sql): Adding client 10.5.50.1 (home) to default clients list
        rlm_sql (10.5.50.1): Client "home" (sql) added
        rlm_sql (sql): Adding client 192.168.1.210 (citylight) to default clients list
        Failed to associate clients with virtual server default
        Failed to add client, possible duplicate?
    
        Although there is no other client with same Name (IPAddress) it cannot add second client.

    2- If it gets an Access-Request it errors with "from unknown client" even if the client is added and same client works if it is made global, some lines from debug:
        rlm_sql (WISPSQL): Adding client 192.168.1.200 (orbitalanya) to WISPServer clients list
        rlm_sql (192.168.1.200): Client "orbitalanya" (WISPSQL) added

        Error with Access-Request:
        Ignoring request to auth address * port 2812 bound to server WISPServer from unknown client 192.168.1.200 port 49390 proto udp

I attached debug output as file debug_VirtualServers.txt

I got an answer from Mr. Alan DeKok on Wed, Jul 29 at 2:59 PM for my previous email for the same problem.
    >>  I've pushed a fix.  You can download the latest version of v3.0.x from gthub. Alan DeKok
I have downloaded source https://github.com/FreeRADIUS/freeradius-server/archive/v3.0.x.zip and installed 3.0.22 but still get same error. Is it wrong version or do i do something wrong with configuration?

Thanks.
    
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: debug_freetds.txt
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20200806/28966550/attachment-0002.txt>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: debug_VirtualServers.txt
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20200806/28966550/attachment-0003.txt>