Re: FreeTDS affected_rows_count Problem, Virtual Servers - nas_table Server Column Problem
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... 2- Virtual Servers - nas_table Server Column Problem earlier email http://freeradius.1045715.n5.nabble.com/Virtual-Servers-amp-nas-table-Server... 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@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@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@orbit (3) SQL-User-Name set to 'test@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@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.
On Aug 6, 2020, at 6:55 AM, Ali Arslan via Freeradius-Users <freeradius-users@lists.freeradius.org> wrote:
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:
...
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:
The short summary is to file a bug upstream. i.e. with the people who wrote the FreeTDS client library. There's not a lot we can do to work around bugs in libraries that we use. And unless the library is OpenSSL, we generally *don't* work around bugs like this. Doing that would turn FreeRADIUS into a horrible collection of patches and work-arounds.
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)
You can modify this function in your source free to run the "SELECT @@ROWCOUNT" query, and return that value. I'm inclined to *not* make that change in FreeRADIUS. Because it's a bug in someone else's library.
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.
Hmm... that should work. I'll see if I can do some tests.
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
That should work, too.
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?
I'll see if I can reproduce it here. Alan DeKok.
On Aug 6, 2020, at 8:16 AM, Alan DeKok <aland@deployingradius.com> wrote:
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.
Hmm... that should work. I'll see if I can do some tests.
I've checked, and it works with the latest version from v3.0.x.
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
That should work, too.
It works with the latest code from v3.0.x. Alan DeKok.
participants (2)
-
Alan DeKok -
Ali Arslan