Mysql, Accounting and DialupAdmin
Hi Folks I succeeded to get my set up running with FR 2.2.0 and Mysql, e.g. I can connect through a ZyXEL NWA 3160 using credentials in the MySQL database using a M$ Windows 7 client. Everything is still quite raw and blurry to me. Could someone point me to the right dos for the following? 1) I had to enter cleartext password into the mysql database, apparently other formats were not accepted 2) I could see login and logout information, but no data usage, e.g. dowload and upload sizes appear to be zeroes. mysql> select username,acctstarttime,acctstoptime,acctoutputoctets,acctoutputoctets from radacct; +----------+---------------------+---------------------+------------------+------------------+ | username | acctstarttime | acctstoptime | acctoutputoctets | acctoutputoctets | +----------+---------------------+---------------------+------------------+------------------+ | test | 2012-11-07 15:09:47 | 2012-11-07 15:15:48 | 0 | 0 | | test | 2012-11-07 15:15:48 | 2012-11-07 15:25:02 | 0 | 0 | | test | 2012-11-07 15:25:32 | 2012-11-07 15:41:52 | 0 | 0 | +----------+---------------------+---------------------+------------------+------------------+ Thanks for hints Erich Titl
On Wed, Nov 7, 2012 at 10:16 PM, Erich Titl <erich.titl@think.ch> wrote:
Hi Folks
I succeeded to get my set up running with FR 2.2.0 and Mysql, e.g. I can connect through a ZyXEL NWA 3160 using credentials in the MySQL database using a M$ Windows 7 client.
Everything is still quite raw and blurry to me. Could someone point me to the right dos for the following?
1) I had to enter cleartext password into the mysql database, apparently other formats were not accepted
Because you use Windows client, which defaults to EAP-MSCHAPv2. See http://deployingradius.com/documents/protocols/compatibility.html If your main concern is "I don't want to store cleartext password in db", you should be able to use NT-Password. Search the list archive, there's a recent thread about this.
2) I could see login and logout information, but no data usage, e.g. dowload and upload sizes appear to be zeroes.
Some NAS (e.g. AP's flashed with dd-wrt) simply doesn't send accounting packets. Blame your NAS :P ... or to be more acccurate, look at your NAS documentation (or ask the vendor) how to get it to send accounting packets. -- Fajar
Hi Fajar on 08.11.2012 03:35, Fajar A. Nugraha wrote:
On Wed, Nov 7, 2012 at 10:16 PM, Erich Titl <erich.titl@think.ch> wrote:
Hi Folks
I succeeded to get my set up running with FR 2.2.0 and Mysql, e.g. I can connect through a ZyXEL NWA 3160 using credentials in the MySQL database using a M$ Windows 7 client.
Everything is still quite raw and blurry to me. Could someone point me to the right dos for the following?
1) I had to enter cleartext password into the mysql database, apparently other formats were not accepted
Because you use Windows client, which defaults to EAP-MSCHAPv2. See http://deployingradius.com/documents/protocols/compatibility.html If your main concern is "I don't want to store cleartext password in db", you should be able to use NT-Password. Search the list archive, there's a recent thread about this.
Thanks, I read that URL, actually that one guided me to enter a Cleartext Password at all. mysql> select * from radcheck; +----+----------+--------------------+----+----------------------------------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------------------------------+ | 1 | test | MD5-Password | := | 81dc9bdb52d04dc20036dbd8313ed055 | | 2 | test | NT-Password | := | 7CE21F17C0AEE7FB9CEBA532D0546AD6 | | 3 | test | Cleartext-Password | := | 1234 | +----+----------+--------------------+----+----------------------------------+
2) I could see login and logout information, but no data usage, e.g. dowload and upload sizes appear to be zeroes.
Some NAS (e.g. AP's flashed with dd-wrt) simply doesn't send accounting packets. Blame your NAS :P
:-( Do you have a recommendation for AP's that pass this information?
... or to be more acccurate, look at your NAS documentation (or ask the vendor) how to get it to send accounting packets.
It is a ZyXEL, so basically a black box, even to the local vendor. Thanks Erich
On Thu, Nov 8, 2012 at 2:08 PM, Erich Titl <erich.titl@think.ch> wrote:
Thanks, I read that URL, actually that one guided me to enter a Cleartext Password at all.
See the column labeled "NT hash"?
mysql> select * from radcheck; +----+----------+--------------------+----+----------------------------------+ | id | username | attribute | op | value | +----+----------+--------------------+----+----------------------------------+ | 1 | test | MD5-Password | := | 81dc9bdb52d04dc20036dbd8313ed055 | | 2 | test | NT-Password | := | 7CE21F17C0AEE7FB9CEBA532D0546AD6 | | 3 | test | Cleartext-Password | := | 1234 | +----+----------+--------------------+----+----------------------------------+
IIRC only one of them will be used. I suggest you dop MD5 (since it's useless for your purpose) and Cleartext (you don't want that, right?) and verify you use the correct NT-Password (use "smbencrypt" if you haven't already done so)
2) I could see login and logout information, but no data usage, e.g. dowload and upload sizes appear to be zeroes.
Some NAS (e.g. AP's flashed with dd-wrt) simply doesn't send accounting packets. Blame your NAS :P
:-(
Do you have a recommendation for AP's that pass this information?
Nope. Sorry. Try looking at the archives, I think Cisco boxes sends them. As an alternative, if you're fine with captive-portal setup, chillispot sends accounting packets just fine.
... or to be more acccurate, look at your NAS documentation (or ask the vendor) how to get it to send accounting packets.
It is a ZyXEL, so basically a black box, even to the local vendor.
Then blame the vendor. Seriously. Why would you want to use something that even the local vendor can't support? -- Fajar
Hi Fajar on 08.11.2012 08:16, Fajar A. Nugraha wrote: ...
IIRC only one of them will be used. I suggest you dop MD5 (since it's useless for your purpose) and Cleartext (you don't want that, right?) and verify you use the correct NT-Password (use "smbencrypt" if you haven't already done so)
Yes, it appears that authentication using NT-Password hash works fine for M$. What would be the least common setting in a multi vendor environment. I guess, OSX, for example, is using a different protocol.
2) I could see login and logout information, but no data usage, e.g. dowload and upload sizes appear to be zeroes.
...
It is a ZyXEL, so basically a black box, even to the local vendor.
Then blame the vendor. Seriously.
Why would you want to use something that even the local vendor can't support?
I am in an evaluation phase and this is a vendor with widespread acceptance here. Finding such a weakness is important as we will probably drop the product then. Unfortunately not everyone is really comfortable with open source products. This is just the kind of reality the vendors try to lock us in. Thanks Erich
On Thu, Nov 8, 2012 at 2:43 PM, Erich Titl <erich.titl@think.ch> wrote:
Hi Fajar
on 08.11.2012 08:16, Fajar A. Nugraha wrote: ...
IIRC only one of them will be used. I suggest you dop MD5 (since it's useless for your purpose) and Cleartext (you don't want that, right?) and verify you use the correct NT-Password (use "smbencrypt" if you haven't already done so)
Yes, it appears that authentication using NT-Password hash works fine for M$. What would be the least common setting in a multi vendor environment. I guess, OSX, for example, is using a different protocol.
Most other supplicants can use EAP-MSCHAPv2 just fine, so you shouldn't have any problems with other OS. NT-Password should work with PAP as well, so PAP and TTLS-PAP should also work, if you need to choose that for some reason. Also note that storing NT-Passwords should be considered as insecure as storing cleartext password (since "cracking" MD4 hash is easy-enough), but at least you won't see the cleartext password in the database. -- Fajar
Hi gents FR 2.0 I added a user to my datebase using the dialup_admin interface. The radcheck table shows the following mysql> select * from radcheck -> ; +----+----------+---------------+----+------------------------------------+ | id | username | attribute | op | value | +----+----------+---------------+----+------------------------------------+ | 2 | test | NT-Password | := | 7CE21F17C0AEE7FB9CEBA532D0546AD6 | | 4 | test1 | User-Password | := | $1$SQZqMcWE$doZxYeK1Sb24QQJvmYpYm0 | +----+----------+---------------+----+------------------------------------+ Now this is interesting. I can log in using the test account with the NT-Password attribute. The one created by dialup_admin with the name of test1 and the attribute User-Password cannot be used from the same M$ Windows 7 PC, as was to be expected from the compatibility table. I looked into admin.conf and found # # can be one of crypt,md5,clear # general_encryption_method: crypt this appears to be used by the GUI Now with MSCHAP this appears not to work simply out of the box. Does one need to hack that code or is there a canonical way to be used for M$ W7 (P)EAP authentication? Thanks Erich
Erich Titl wrote:
# # can be one of crypt,md5,clear # general_encryption_method: crypt
this appears to be used by the GUI
Now with MSCHAP this appears not to work simply out of the box. Does one need to hack that code or is there a canonical way to be used for M$ W7 (P)EAP authentication?
Change that from "crypt" to "clear". Then PEAP will work. Alan DeKok.
Alan on 08.11.2012 19:10, Alan DeKok wrote:
Erich Titl wrote:
# # can be one of crypt,md5,clear # general_encryption_method: crypt
this appears to be used by the GUI
Now with MSCHAP this appears not to work simply out of the box. Does one need to hack that code or is there a canonical way to be used for M$ W7 (P)EAP authentication?
Change that from "crypt" to "clear". Then PEAP will work.
Yes, I know if I also change the attribute to Cleartext-Password. Any plans to support NT-Password hashes? Thanks Erich Titl
Hi Alan on 08.11.2012 21:06, Alan DeKok wrote:
Erich Titl wrote:
Yes, I know if I also change the attribute to Cleartext-Password. Any plans to support NT-Password hashes?
In dialup_admin? Send a patch.
This works for me diff -urN freeradius-server-2.2.0.orig/dialup_admin/conf/admin.conf freeradius-server-2.2.0/dialup_admin/conf/admin.conf --- freeradius-server-2.2.0.orig/dialup_admin/conf/admin.conf 2012-11-09 07:30:40.000000000 +0100 +++ freeradius-server-2.2.0/dialup_admin/conf/admin.conf 2012-11-09 07:44:28.000000000 +0100 @@ -133,7 +133,7 @@ general_radius_server_secret: XXXXXX general_auth_request_file: %{general_base_dir}/conf/auth.request # -# can be one of crypt,md5,clear +# can be one of crypt,md5,clear,smbpass # general_encryption_method: crypt # diff -urN freeradius-server-2.2.0.orig/dialup_admin/lib/crypt/smbpass.php freeradius-server-2.2.0/dialup_admin/lib/crypt/smbpass.php --- freeradius-server-2.2.0.orig/dialup_admin/lib/crypt/smbpass.php 1970-01-01 01:00:00.000000000 +0100 +++ freeradius-server-2.2.0/dialup_admin/lib/crypt/smbpass.php 2012-11-09 07:43:43.000000000 +0100 @@ -0,0 +1,6 @@ +<?php +function da_encrypt($Input) { + // shamelessly taken from php.net + return(strtoupper(hash('md4',iconv('UTF-8','UTF-16LE',$Input)))); +} +?> cheers Erich Titl
On Thu, Nov 8, 2012 at 2:08 PM, Erich Titl <erich.titl@think.ch> wrote:
2) I could see login and logout information, but no data usage, e.g. dowload and upload sizes appear to be zeroes.
Some NAS (e.g. AP's flashed with dd-wrt) simply doesn't send accounting packets. Blame your NAS :P
:-(
Do you have a recommendation for AP's that pass this information?
... or to be more acccurate, look at your NAS documentation (or ask the vendor) how to get it to send accounting packets.
It is a ZyXEL, so basically a black box, even to the local vendor.
Just to be sure, you HAVE enabled sql in accounting section, right? If you want to be extra sure, run FR in debug mode, and do a login-logout using a client (e.g. notebook) to the NAS (i.e. AP). FR should print out what packets it received. If it DOESN'T show any accounting packets, then your NAS doesn't send them, or hasn't been configured to do so. -- Fajar
on 08.11.2012 09:01, Fajar A. Nugraha wrote: ...
It is a ZyXEL, so basically a black box, even to the local vendor.
Just to be sure, you HAVE enabled sql in accounting section, right?
I guess the fact that I have entries in the radacct table which correspond to actual connection attempts should prove that. mysql> select username,acctstarttime,acctstoptime,acctinputoctets from radacct; +----------+---------------------+---------------------+-----------------+ | username | acctstarttime | acctstoptime | acctinputoctets | +----------+---------------------+---------------------+-----------------+ | test | 2012-11-07 15:09:47 | 2012-11-07 15:15:48 | 0 | | test | 2012-11-07 15:15:48 | 2012-11-07 15:25:02 | 0 | | test | 2012-11-07 15:25:32 | 2012-11-07 15:41:52 | 0 | | test | 2012-11-07 21:20:53 | 2012-11-07 21:24:13 | 0 | | test | 2012-11-07 21:41:50 | 2012-11-07 21:42:13 | 0 | | test | 2012-11-07 21:42:43 | 2012-11-07 21:47:14 | 0 | | test | 2012-11-08 07:52:42 | 2012-11-08 07:55:45 | 0 | | test | 2012-11-08 08:35:15 | 2012-11-08 08:50:22 | 0 | | test | 2012-11-08 09:56:24 | 2012-11-08 10:02:28 | 0 | | test | 2012-11-08 10:06:58 | 2012-11-08 10:07:23 | 0 | | test | 2012-11-08 10:11:31 | 2012-11-08 10:12:06 | 0 | | test | 2012-11-08 10:12:20 | 2012-11-08 10:12:35 | 0 | | test | 2012-11-08 10:12:42 | 2012-11-08 10:13:11 | 0 | | test | 2012-11-08 10:13:27 | 2012-11-08 10:14:38 | 0 | | test | 2012-11-08 10:14:51 | NULL | 0 | +----------+---------------------+---------------------+-----------------+
If you want to be extra sure, run FR in debug mode, and do a login-logout using a client (e.g. notebook) to the NAS (i.e. AP). FR should print out what packets it received. If it DOESN'T show any accounting packets, then your NAS doesn't send them, or hasn't been configured to do so.
I _guess_ it shows some accounting rad_recv: Accounting-Request packet from host 194.124.158.62 port 47037, id=165, length=135 Acct-Session-Id = "509ACAB9-0000000F" Acct-Status-Type = Start Acct-Authentic = RADIUS User-Name = "test" NAS-Port = 0 Called-Station-Id = "50-67-F0-38-A9-E5:ZyXEL" Calling-Station-Id = "74-F0-6D-07-9B-91" NAS-Port-Type = Wireless-802.11 Connect-Info = "CONNECT 0Mbps 802.11" # Executing section preacct from file /usr/local/etc/raddb/sites-enabled/default +- entering group preacct {...} ++[preprocess] returns ok [acct_unique] WARNING: Attribute NAS-Identifier was not found in request, unique ID MAY be inconsistent [acct_unique] Hashing 'NAS-Port = 0,,NAS-IP-Address = 194.124.158.62,Acct-Session-Id = "509ACAB9-0000000F",User-Name = "test"' [acct_unique] Acct-Unique-Session-ID = "de12b16f3f8a6cf8". ++[acct_unique] returns ok ++[files] returns noop # Executing section accounting from file /usr/local/etc/raddb/sites-enabled/default +- entering group accounting {...} [detail] expand: %{Packet-Src-IP-Address} -> 194.124.158.62 [detail] expand: /usr/local/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d -> /usr/local/var/log/radius/radacct/194.124.158.62/detail-20121108 [detail] /usr/local/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /usr/local/var/log/radius/radacct/194.124.158.62/detail-20121108 [detail] expand: %t -> Thu Nov 8 10:22:38 2012 ++[detail] returns ok [sql] expand: %{User-Name} -> test [sql] sql_set_user escaped user --> 'test' [sql] expand: %{Acct-Delay-Time} -> [sql] ... expanding second conditional [sql] expand: INSERT INTO radacct (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, xascendsessionsvrkey) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', '%S', NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', rlm_sql (sql): Reserving sql socket id: 1 rlm_sql (sql): Released sql socket id: 1 ++[sql] returns ok Erich
On Thu, Nov 8, 2012 at 4:27 PM, Erich Titl <erich.titl@think.ch> wrote:
I _guess_ it shows some accounting
rad_recv: Accounting-Request packet from host 194.124.158.62 port 47037, id=165, length=135 Acct-Session-Id = "509ACAB9-0000000F" Acct-Status-Type = Start
Do some stuff first with the client (e.g. browsing), then disconnect. Look for accounting stop packet. If it doesn't show Acct-In-Octets and friends, then your AP is seriously broken. -- Fajar
participants (3)
-
Alan DeKok -
Erich Titl -
Fajar A. Nugraha