Configure SQL to timeout requests rather than rejecting if no connections available
I am using FreeRADIUS 3.0.4, compiled from source on CentOS 6.5. I'm using the source-compiled version rather than from package because my RADIUS servers need to make a connection to a MS SQL always-on availability group (cluster) that relies on the Microsoft SQL Native Client 11 ODBC driver (which requires a source-compiled version of unix-odbc and thus prevents me from installing the package versions of ODBC & FreeRADIUS). My infrastructure is set up with multiple MikroTik routers as the RADIUS clients, connecting to two FreeRADIUS servers in a round-robin fashion. The MikroTik routers are configured to wait 1500ms for a response from their primary RADIUS server before they re-submit the request. If they make a total of 3 unsuccessful requests with no response (the original + 2 resends) on the primary server, they will re-submit the request to the secondary server. The RADIUS requests are being made to authorize DHCP requests and hotspot sessions. Because of a difference in what reply information (for rate-limit and other similar properties) need to go out based on the two types of requests, we are using realms to route the requests off to an appropriate virtual server. Both virtual servers share the same SQL connection pool (and have different queries they pass). Occasionally, we have a network disruption (intentional or otherwise) that drops multiple hotspot sessions simultaneously, "crushing" the RADIUS server under a sharp spike in request load. The SQL module is configured as follows: pool { start = 15 min = 0 max = 45 spare = 15 uses = 0 lifetime = 0 idle_timeout = 0 } Under the heavy load situation, we're seeing entries in radius.log that there aren't enough spares, it starts opening another, and eventually runs out of connections before it can open enough additional connections (it usually only tries to add 1, maybe 2 new connections total during the course of the spike). Looking through the log of the "master" virtual server that handles the requests and proxies it off to the realm-specific handler (another virtual server on the same physical machine), we're seeing results along the lines of: Auth: Login incorrect (Home Server says so): [<client MAC>@hotspot<realm>] (from client <nas identity> port 16346 cli <client MAC>) The realm-specific virtual server is returning: Auth: Invalid user: [<client MAC>@hotspot<realm>] (from client <nas identity> port 16415 cli <client MAC> via TLS tunnel) Is there a setting change I can make to get the server to just ignore/drop the request if there are no SQL connections available, rather than sending a reject? I'm obviously still tuning my max concurrency limits to minimize the frequency of these spikes, but it would be nice if RADIUS would also help my by generating re-requests that spread out the load and distribute it across both servers, rather than just generating a bunch of rejects that block my customers until someone intervenes and cleans up the rejected sessions. Thank you, Noah Engelberth MetaLINK Technologies
Is there a setting change I can make to get the server to just ignore/drop the request if there are no SQL connections available,
sql { fail = 1 } if (fail) { do_not_respond } Why are you using the Unix ODBC driver? There's the FreeTDS driver written specifically for communicating with Microsoft servers... Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS development team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
Is there a setting change I can make to get the server to just ignore/drop the request if there are no SQL connections available,
sql { fail = 1 } if (fail) { do_not_respond }
Thanks, I appreciate the syntax help.
Why are you using the Unix ODBC driver? There's the FreeTDS driver written specifically for communicating with Microsoft servers...
Using the FreeTDS driver won't work for us because our SQL cluster requires the MultiSubnetFailover parameter to be set by connecting clients (it's a geo-redundant Always On Availability Group), which requires the Microsoft Native Client ODBC driver -- FreeTDS doesn't support that parameter as far as any documentation I could find suggests. With just FreeTDS, we'd be in a situation where our RADIUS servers would have a 50/50 chance of actually connecting when they tried to open a new SQL connection, which is kind of sub-optimal. Thank you, Noah Engelberth
On 17 Jan 2015, at 08:13, Noah Engelberth <nengelberth@team-meta.net> wrote:
Is there a setting change I can make to get the server to just ignore/drop the request if there are no SQL connections available,
sql { fail = 1 } if (fail) { do_not_respond }
Thanks, I appreciate the syntax help.
Why are you using the Unix ODBC driver? There's the FreeTDS driver written specifically for communicating with Microsoft servers...
Using the FreeTDS driver won't work for us because our SQL cluster requires the MultiSubnetFailover parameter to be set by connecting clients (it's a geo-redundant Always On Availability Group), which requires the Microsoft Native Client ODBC driver -- FreeTDS doesn't support that parameter as far as any documentation I could find suggests. With just FreeTDS, we'd be in a situation where our RADIUS servers would have a 50/50 chance of actually connecting when they tried to open a new SQL connection, which is kind of sub-optimal.
Ah, ok, fair enough :) FreeTDS is generally the better tested module, the UnixODBC module really needs updating to use the newer ODBC API. But if it works for you, great! -Arran Arran Cudbard-Bell <a.cudbardb@freeradius.org> FreeRADIUS development team FD31 3077 42EC 7FCD 32FE 5EE2 56CF 27F9 30A8 CAA2
Is there a setting change I can make to get the server to just ignore/drop the request if there are no SQL connections available,
sql { fail = 1 } if (fail) { do_not_respond }
Thanks, I appreciate the syntax help.
After a few days of operating this and recreating my "crushing load" situation with the log files open, it looks like when the virtual server does not respond, the "master" server is still kicking a reject -- the log entry is: Auth: Login incorrect (Home Server failed to respond): *snipped client info* So, do I need to do the same syntax for the "suffix" module in my master virtual server? Thank you, Noah Engelberth MetaLINK Technologies
On Jan 21, 2015, at 12:58 PM, Noah Engelberth <nengelberth@team-meta.net> wrote:
After a few days of operating this and recreating my "crushing load" situation with the log files open, it looks like when the virtual server does not respond, the "master" server is still kicking a reject -- the log entry is:
Auth: Login incorrect (Home Server failed to respond): *snipped client info*
So, do I need to do the same syntax for the "suffix" module in my master virtual server?
No. The “suffix” module just marks the request as “it should be proxied”. The suffix module doesn’t actually do the proxying. The issue is that you don’t want to respond to the *client*. You *do* want the virtual server to respond to the proxy. As always, run the server in debugging mode to see what’s going on. It will quickly become clear what’s going on. And, *where* you need to make changes. In your existing configuration, you can probably just change the proxying, to add: post-proxy { … Post-Proxy-Type Fail { do_not_respond } ... } And the proxy won’t respond to the client. But I’d have to see the full debug log to be sure. Alan DeKok.
After a few days of operating this and recreating my "crushing load" situation with the log files open, it looks like when the virtual server does not respond, the "master" server is still kicking a reject -- the log entry is:
Auth: Login incorrect (Home Server failed to respond): *snipped client info*
So, do I need to do the same syntax for the "suffix" module in my master virtual server?
No. The "suffix" module just marks the request as "it should be proxied". The suffix module doesn't actually do the proxying.
The issue is that you don't want to respond to the *client*. You *do* want the virtual server to respond to the proxy.
As always, run the server in debugging mode to see what's going on. It will quickly become clear what's going on. And, *where* you need to make changes.
In your existing configuration, you can probably just change the proxying, to add:
post-proxy { ... Post-Proxy-Type Fail { do_not_respond }
... }
And the proxy won't respond to the client. But I'd have to see the full debug log to be sure.
Alan DeKok.
So, "in your existing configuration" means what I have with the virtual server not responding on an SQL "fail"? Or to revert that change and then add that post-proxy handling? My server configuration currently (with the virtual server not responding on SQL "fail") is: --- server platreg { authorize { mikrotik_realm suffix } preacct { mikrotik_realm suffix } } server platreg_hotspot { authorize { update request { SQL-Reply-Table := 'ml_hotspot_freeradius_service_radreply' } platreg { fail = 1 } if ( fail ) { do_not_respond } } authenticate { Auth-Type CHAP { chap } } preacct { preprocess acct_unique suffix files } accounting { detail unix radutmp platreg exec attr_filter.accounting_response } post-auth { exec Post-Auth-Type REJECT { # log failed authentications in SQL, too. platreg attr_filter.access_reject } } } --- So, basically, the mikrotik_realm module looks at the realm name that came in from the client (which might be requesting hotspot.metalink.net or dhcp.metalink.net) and update the username to username@realm. Suffix then proxies it off to the virtual server named platreg_<realm> "platreg" is the name of my sql module. I can't easily run the live servers in debug mode -- they're under fairly heavy volume and it's hard to get useful information from them. I have fairly high request logging turned up at the moment, and I can try to set up my debug environment with a test case that could replicate the issue if necessary. Thank you, Noah Engelberth System Administration MetaLINK Technologies nengelberth@team-meta.net 419-990-0342
On Wed, Jan 21, 2015 at 06:20:34PM +0000, Noah Engelberth wrote:
I can't easily run the live servers in debug mode -- they're under fairly heavy volume and it's hard to get useful information from them. I have fairly high request logging turned up at the moment, and I can try to set up my debug environment with a test case that could replicate the issue if necessary.
radmin can help, e.g. # radmin -d /path/to/raddb radmin> debug file debugout radmin> show debug file /var/log/freeradius/debugout radmin> debug condition 'Calling-Station-Id == "aa-bb-cc-dd-ee-ff"' (do test from client aa-bb-cc-dd-ee-ff) radmin> debug condition (tail -f & wait for file to finish being written to) radmin> debug file radmin> exit then the debugout file should contain only debug output from when the condition was true on the incoming request. To get the server config, just run radiusd -X on the running server. It will dump out the startup logging, then refuse to run any further because there is already another radiusd listening on the udp ports. To use radmin you need to have enabled the control-socket virtual server, and set "mode = rw". Matthew -- Matthew Newton, Ph.D. <mcn4@le.ac.uk> Systems Specialist, Infrastructure Services, I.T. Services, University of Leicester, Leicester LE1 7RH, United Kingdom For IT help contact helpdesk extn. 2253, <ithelp@le.ac.uk>
On Jan 21, 2015, at 1:20 PM, Noah Engelberth <nengelberth@team-meta.net> wrote:
So, "in your existing configuration" means what I have with the virtual server not responding on an SQL "fail"? Or to revert that change and then add that post-proxy handling?
<sigh> What I said was very clear. I said to change ONE thing. The PROXY. I didn’t say to revert a change.
My server configuration currently (with the virtual server not responding on SQL "fail") is:
I didn’t ask for that. I asked for the DEBUG OUTPUT. Paying attention would be nice. Following instructions would be better.
So, basically, the mikrotik_realm module looks at the realm name that came in from the client (which might be requesting hotspot.metalink.net or dhcp.metalink.net) and update the username to username@realm. Suffix then proxies it off to the virtual server named platreg_<realm>
NO. I said that the suffix module DOES NOT DO PROXYING. Please pay attention.
I can't easily run the live servers in debug mode -- they're under fairly heavy volume and it's hard to get useful information from them. I have fairly high request logging turned up at the moment, and I can try to set up my debug environment with a test case that could replicate the issue if necessary.
If only there was a free “radius client” tool which would let you send test packets. If only it was possible to create a test configuration *independent* of the live configuration, where test packets could be sent. Yes, I find this frustrating. I’m trying to help you, and you’re making it as difficult as possible. Alan DeKok.
<sigh> What I said was very clear. I said to change ONE thing. The PROXY. I didn't say to revert a change.
I read your previous as "The issue is that you don't want to respond to the *client*. You *do* want the virtual server to respond to the proxy." This made me think: "The previous change I made to make the virtual server not respond in the case of SQL failing is causing the virtual server not to respond the proxy, so I must need to revert that change. But I'm kind of confused on whether or not that's the correct course of action, so I'm going to request clarification before I make a change, since I don't have a working method of replicating this problem in my test environment right now because my test environment isn't set up to make 30+ RADIUS requests in a single second and resource exhaust my test RADIUS server." I then read back through my previous postings and realized I hadn't included the original configuration, so I posted it in hopes that maybe my confusion was stemming from a mis-communication between us caused by my failure to initially supply all linformation.
If only there was a free "radius client" tool which would let you send test packets.
If only it was possible to create a test configuration *independent* of the live configuration, where test packets could be sent.
Yes, I find this frustrating. I'm trying to help you, and you're making it as difficult as possible.
Ok, so it's apparent that I need to get an environment set up where I can get debug information for the situation. The only reason for my previous comments was to exhaust all other options before I started doing that, because I expected it was going to take me a couple hours to either (1) set up my dev environment to generate a resource exhaustion scenario or (2) learn enough about how to use the radius client tool to simulate the same sort of setup. I do appreciate the effort you have put in and are continuing to put toward assisting me with my problem. I'm disappointed by the nature of the response I got for trying to clarify things before I spent hours generating debug results, in case the confusion was stemming from something easily cleared up by me including information I originally left out. I'll reply back after I'm able to generate the necessary debug logs. Thank you, Noah Engelberth MetaLINK Technologies
I'll reply back after I'm able to generate the necessary debug logs.
Ok, I have the debug log of a request that failed due to SQL connection exhaustion - what's the best way to get it to you? I'm not wild about posting an un-doctored version of it to a public mailing list, as I wound up having to generate it off our live network during a maintenance window, and there is a lot of IP & username information I really shouldn't post to a public mailing list. Would it be helpful for me to just post a censored version of one of the requests that failed, or do you need to see other debug information beyond that request? Thank you, Noah Engelberth System Administration MetaLINK Technologies nengelberth@team-meta.net 419-990-0342
On Jan 21, 2015, at 2:14 PM, Noah Engelberth <nengelberth@team-meta.net> wrote:
This made me think: "The previous change I made to make the virtual server not respond in the case of SQL failing is causing the virtual server not to respond the proxy, so I must need to revert that change.
I didn’t say to revert that change. So...
But I'm kind of confused on whether or not that's the correct course of action, so I'm going to request clarification before I make a change, since I don't have a working method of replicating this problem in my test environment right now because my test environment isn't set up to make 30+ RADIUS requests in a single second and resource exhaust my test RADIUS server.”
You can use radclient to hammer the server.
I then read back through my previous postings and realized I hadn't included the original configuration, so I posted it in hopes that maybe my confusion was stemming from a mis-communication between us caused by my failure to initially supply all information.
Which is why we ask for all of the data.
Ok, so it's apparent that I need to get an environment set up where I can get debug information for the situation. The only reason for my previous comments was to exhaust all other options before I started doing that, because I expected it was going to take me a couple hours to either (1) set up my dev environment to generate a resource exhaustion scenario or (2) learn enough about how to use the radius client tool to simulate the same sort of setup.
It takes less time to set up a debug environment than to wait for responses on the list. I used to work with a guy whose attitude towards testing was “I don’t have time to write tests”. For me, I don’t have time to *not* write tests.
I do appreciate the effort you have put in and are continuing to put toward assisting me with my problem. I'm disappointed by the nature of the response I got for trying to clarify things before I spent hours generating debug results, in case the confusion was stemming from something easily cleared up by me including information I originally left out.
That’s why we ask for debug logs. And why a good chunk of responses are questions like “What do you mean?” or “What are you really doing”? If you care about privacy, send the debug messages to me off-list. I’ll take a look. Alan DeKok.
participants (4)
-
Alan DeKok -
Arran Cudbard-Bell -
Matthew Newton -
Noah Engelberth