- Freeradius-Users - lists.freeradius.org

Freeradius, MySQL and unique accounting records.
by Mark J Elkins 29 Dec '06

29 Dec '06

I'm getting multiple "accounting_start_query" records in my "radacct" table. When a "Stop" arrives, each gets the same accounting info (time online, bytes in, bytes out - etc)... However - when I run SQL queries - I do not get true stats - which is a problem. My "accounting_start_query" are stock standard (I'm running an oldish version of radius though). What is happening is that I'm getting multiple Start Records - and logging each one. accounting_start_query = "INSERT into ${acct_table1} (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}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', '%S', '0', '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')" INSERT into radacct_rad2 (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('7/0/3/5.117_18EB0F13', 'ade8e732c3a7aef6', 'scubaw(a)pop.co.za', 'pop.co.za', '196.43.27.19', '1929707637', 'Virtual', '2006-12-20 06:03:25', '0', '0', 'RADIUS', 'AutoShapedVC', '', '0', '0', '', '', '', 'Framed-User', 'PPP', '41.242.241.194', '0', '0'); INSERT into radacct_rad2 (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('7/0/3/5.117_18EB0F13', 'ade8e732c3a7aef6', 'scubaw(a)pop.co.za', 'pop.co.za', '196.43.27.19', '1929707637', 'Virtual', '2006-12-20 06:03:29', '0', '0', 'RADIUS', 'AutoShapedVC', '', '0', '0', '', '', '', 'Framed-User', 'PPP', '41.242.241.194', '5', '0'); INSERT into radacct_rad2 (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('7/0/3/5.117_18EB0F13', 'e71dbe474c39274d', 'scubaw(a)pop.co.za', 'pop.co.za', '196.43.27.19', '1929707637', 'Virtual', '2006-12-20 06:03:35', '0', '0', 'RADIUS', 'AutoShapedVC', '', '0', '0', '', '', '', 'Framed-User', 'PPP', '41.242.241.194', '15', '0'); INSERT into radacct_rad2 (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('7/0/3/5.117_18EB0F13', 'db5198a07e9ebe18', 'scubaw(a)pop.co.za', 'pop.co.za', '196.43.27.19', '1929707637', 'Virtual', '2006-12-20 06:03:39', '0', '0', 'RADIUS', 'AutoShapedVC', '', '0', '0', '', '', '', 'Framed-User', 'PPP', '41.242.241.194', '10', '0'); [the stop record] UPDATE radacct_rad2 SET AcctStopTime = '2006-12-20 06:37:09', AcctSessionTime = '2040', AcctInputOctets = '2371574', AcctOutputOctets = '93521334', AcctTerminateCause = 'User-Request', AcctStopDelay = '0', ConnectInfo_stop = 'AutoShapedVC' WHERE AcctSessionId = '7/0/3/5.117_18EB0F13' AND UserName = 'scubaw(a)pop.co.za' AND NASIPAddress = '196.43.27.19'; The Acct-Delay-Time is different for each record - increases by 5 seconds - etc. So what do people do to maintain accurate accounting records? For example - to get the current monthly data-in+out total for an individual - I run.... MBYTE=$(mysql -u$USR -p$SQLPASS -h$DBHOST -B --skip-column-names $DB -e \ "SELECT ROUND(SUM((AcctInputOctets + AcctOutputOctets)/1048576)) FROM radacct WHERE AcctStartTime > '$monthstart' AND username = '$username@$realm'") Currently - this is quite quick - but would give me the wrong results for the above! What about making the column "AcctSessionId" unique? (somehow) - or maybe a new column which contains AcctSessionId, UserName and NASIPAddress - or what do people suggest? -- . . ___. .__ Posix Systems - Sth Africa /| /| / /__ mje(a)posix.co.za - Mark J Elkins, SCO ACE, Cisco CCIE / |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496

1 0

PEAP+PAM or MySQL possible?
by Matt Goebel 28 Dec '06

28 Dec '06

I'd like to move to WPA Enterprise EAP/PEAP from EAP/TLS. That way there are no client certificates to deal with and I can instead just use usernames/passwords. I don't, however, want either OpenLDAP or AD to do it. Both would be overkill for my needs and just add an extra layer to maintain. Instead I want to use either PAM or MySQL in their place. Is this even possible? If so how? I haven't seen any documentation that definitively explains this one way or another. Testing I'm able to successfully authenticate using the radtest program. From a client (both Windows and Linux) I get invalid username/password errors. Debug mode I see the username being passed correctly along with other information but no password, encrypted or otherwise. Maybe this is by design? I haven't seen what working PEAP debug messages look like so I have no frame of reference.

2 1

RE: FreeRadius overview
by David Covert 28 Dec '06

28 Dec '06

Ok, I almost get it... question: The mssql.conf file has an entry like this: #Database table configutation radius_db = "radius" I assume that "radius" refers to a pre-determined schema that freeRadius assumes the auth db is going to use. Thing is, I want to reference a different schema, one that is used by my current customer care system (Platypus). Can I refer to a different schema? If so, where do I go to define it? Does anyone happen to know if a Platypus schema already exists? Or would I be better off to write triggers in my current Platypus db to copy data from various tables to the radius tables every time a customer record is updated? Thank you for your time, Dave Covert I am serious... and don't call me Shirley! _____ From: freeradius-users-bounces+dcovert=erfwireless.com(a)lists.freeradius.org [mailto:freeradius-users-bounces+dcovert=erfwireless.com@lists.freeradiu s.org] On Behalf Of Hernan Antolini Sent: Thursday, December 28, 2006 11:29 AM To: FreeRadius users mailing list Subject: Re: FreeRadius overview David, start with "rlm_sql" at source_directory/doc, check also your sql.conf in your installation etc/raddb directory....it help me also this one to start http://www.frontios.com/freeradius.html (for mysql). I hope it helps....if you're about using ms-sql check also "mssql" at source_directory/doc. Regards freeradius-users-bounces+antolini=ar.ibm.com(a)lists.freeradius.org wrote on 12/28/2006 01:37:51 PM: > > I have gotten freeRadius working with a mikrotik NAS using the users > flat file (hooray for me! <g>). Now I need to have the freeRadius server > reference a MS-SQL db server rather than the users file on the > freeRadius server. I am quite fuzzy about the process tho. I am hoping > for some basic edukashun. > > How do you tell freeRadius to reference an external MS-SQL db rather > than the local users flat file? > How do you issue the query to the db? (what file contains the connection > string and query) > How do the returning fields get mapped into RADIUS attributes? (what do > you do when the db calls the field "uname" and RADIUS calls it > "user-name") > > Thank you for your time, > Dave Covert > > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

2 1

Very big user database (solved)
by Felipe Neuwald 28 Dec '06

28 Dec '06

Oops, sorry.. solved: [root@mercury /usr/local/etc/raddb]# cat sql.conf | grep num_sql_socks num_sql_socks = 15 Increased from 5 to 15. Thanks, Felipe. ------------------- Hi folks, I put to work our new freeradius server, and I'm getting this message on my /var/log/radius.log: Thu Dec 28 15:47:15 2006 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Thu Dec 28 15:47:15 2006 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Thu Dec 28 15:47:15 2006 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Thu Dec 28 15:47:15 2006 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Thu Dec 28 15:47:15 2006 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Thu Dec 28 15:47:15 2006 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Our user database is big, something about 120k users. Does anybody knows how to solve this problem? Thank you, Felipe Neuwald.

1 0

NAS support for disconnect methods
by Daniel Lark 28 Dec '06

28 Dec '06

I am in the process of developing a generalized disconnect methodology for disconnects. While I know definitely that Cisco NAS with an IOS => 12.1 will support POD, does anyone out there know what equipment supports disconnects through SNMP or POD. I have equipment all over the map and need to plan accordingly. Obviously radkill is my ultimate back-up ;-) Thanks! _ _____ Daniel Lark Applications Developer Spirit Telecommunications 1500 Hampton St., Suite 101 Columbia, SC 29201 (803) 726-4755

2 1

Very big user database
by Felipe Neuwald 28 Dec '06

28 Dec '06

Hi folks, I put to work our new freeradius server, and I'm getting this message on my /var/log/radius.log: Thu Dec 28 15:47:15 2006 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Thu Dec 28 15:47:15 2006 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Thu Dec 28 15:47:15 2006 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Thu Dec 28 15:47:15 2006 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Thu Dec 28 15:47:15 2006 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Thu Dec 28 15:47:15 2006 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Our user database is big, something about 120k users. Does anybody knows how to solve this problem? Thank you, Felipe Neuwald.

1 0

FreeRadius overview
by David Covert 28 Dec '06

28 Dec '06

I have gotten freeRadius working with a mikrotik NAS using the users flat file (hooray for me! <g>). Now I need to have the freeRadius server reference a MS-SQL db server rather than the users file on the freeRadius server. I am quite fuzzy about the process tho. I am hoping for some basic edukashun. How do you tell freeRadius to reference an external MS-SQL db rather than the local users flat file? How do you issue the query to the db? (what file contains the connection string and query) How do the returning fields get mapped into RADIUS attributes? (what do you do when the db calls the field "uname" and RADIUS calls it "user-name") Thank you for your time, Dave Covert

5 5

Re: problem with NT-Password and LDAP
by Ana Gallardo Gómez 28 Dec '06

28 Dec '06

>Ana Gallardo Gómez wrote: >> Hello, my problem is, i have a Freeradius server that retrieves the >> authentication information from an OpenLDAP server; in this server the >> NT-Password atributte has 252 characters (32 characters from NT-hash + >> white spaces) >Why not just update the entries in LDAP to remove the space? >Alan DeKok. Well, I can not modify the LDAP server, I only can use it, so, while I ask the LDAP administrator to modify this atribute, I would like to modify in Freeradius server. I have read that there is a module to rewrite the value of an atribute on fly, but I can´t find how to use it. I have a regular expression to modify... I have a ssript perl to modify... but I don´t know how to use it. Thank you _________________________________________________________________ Consigue el nuevo Windows Live Messenger http://get.live.com/messenger/overview

2 1

RE: proxy authentication
by Dubi Lego 28 Dec '06

28 Dec '06

Our solution is to provide lawful information, according to a Court request on user's activity on the net. In order to do that we need to intercept their AAA activity and the actual data activity. The problem we are having is to collect the AAA activity for Authentication. We have found the way to forward the accounting data from the RADIUS to our RADIUS proxy and so we are notified on Session Start and Session End, but we need also to be notified on any attempt of a user to authenticate through the RADIUS. Are you familiar with any solution to do that? Thanks in advance for your help, Dubi -----Original Message----- From: freeradius-users-bounces+dlego=allot.com(a)lists.freeradius.org [mailto:freeradius-users-bounces+dlego=allot.com@lists.freeradius.org] On Behalf Of Alan DeKok Sent: Wednesday, December 27, 2006 9:33 PM To: FreeRadius users mailing list Subject: Re: proxy authentication Dubi Lego wrote: > I will clarify my question. > Is it possible to forward all AAA messages including replies to another > server which will only listen to messages and won't be active. The short answer is that RADIUS doesn't do that. I'm not aware of *any* server, WWW, FTP, DNS, or otherwise that can do this. What you can do is to log all of the requests & responses to a "detail" file, and then copy that file somewhere else. Perhaps you might try explaining *why* you want this solution. What problem are you trying to solve? Why do you believe that this solution is the best one for the problem? Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

4 3

Deploying Radius Blog site
by King, Michael 28 Dec '06

28 Dec '06

Alan, Your deploying radius blog site is showing: MySQL error! Error establishing a database connection! (Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)) 1. Are you sure you have typed the correct user/password? 2. Are you sure that you have typed the correct hostname? 3. Are you sure that the database server is running?

3 2