SQL Logging
Hello again,
I'm still fighting my little battle in copying attributes from the inner
to the outer tunnel etc. I have now gotten as far that logging
access-accepts is working as I want, but I'm now struggling logging
access-rejects. Here's my SQL from dialup.conf:
postauth_query = INSERT INTO ${postauth_table} \
(username, pass, reply, authdate)
VALUES \
('%{reply:SQL-User-Name}',
'%{reply:Packet-Type}', \
'%{reply:Calling-Station-Id}', '%S');
From a rejected session, I get this:
Fri Jan 28 09:48:05 2011 : Info: (5) [ttls] Got tunneled reply code 3
Filter-Id = OBFUSCATED
SQL-User-Name = OBFUSCATED
Calling-Station-Id = OBFUSCATED
MS-CHAP-Error = \226E=691 R=1
Fri Jan 28 09:48:05 2011 : Info: (5) +- entering group REJECT {...}
Fri Jan 28 09:48:05 2011 : Info: (5) [sql] expand:
%{Stripped-User-Name} - {am=1}OBFUSCATED
Fri Jan 28 09:48:05 2011 : Info: (5) [sql] expand:
%{%{Stripped-User-Name}:-%{%{User-Name}:-DEFAULT}} - {am=1}OBFUSCATED
Fri Jan 28 09:48:05 2011 : Info: (5) [sql] sql_set_user escaped user --
'{am=1}OBFUSCATED'
Fri Jan 28 09:48:05 2011 : Info: (5) [sql] expand: INSERT INTO
radpostauth (username, pass, reply,
authdate) VALUES
('%{reply:SQL-User-Name}', '%{reply:Packet-Type}',
'%{reply:Calling-Station-Id}', '%S'); - INSERT INTO
radpostauth (username, pass, reply,
authdate) VALUES ('',
'Access-Reject', '', '2011-01-28 09:48:05');
Fri Jan 28 09:48:05 2011 : Debug: rlm_sql (sql) in sql_postauth: query
is INSERT INTO radpostauth (username,
pass, reply, authdate) VALUES ('',
'Access-Reject', '', '2011-01-28 09:48:05');
From an accepted session, everything works fine and the SQL-User-Name
and Calling-Station-Id are logged as expected. How come the attributes
are empty, even though they are in the reply, only when an access-reject
is given?
- Kristoffer
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL Logging
Kristoffer Milligan wrote: From an accepted session, everything works fine and the SQL-User-Name and Calling-Station-Id are logged as expected. How come the attributes are empty, even though they are in the reply, only when an access-reject is given? The attributes aren't copied on reject. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL Logging
So there is no way to get hold of them ? - Kris On 01/28/2011 10:36 AM, Alan DeKok wrote: Kristoffer Milligan wrote: From an accepted session, everything works fine and the SQL-User-Name and Calling-Station-Id are logged as expected. How come the attributes are empty, even though they are in the reply, only when an access-reject is given? The attributes aren't copied on reject. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL Logging
Kristoffer Milligan wrote: So there is no way to get hold of them ? Edit the source code. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
SQL Logging Access-Reject
Hello again list,
I'm still working on my FreeRADIUS server in connection with 4Motion
equipment from Alvarion. It's getting better and better and more
integrated, but I still have a few quirks I need to work out.
My main problem now is the logging part. In the post-auth section, I
have added some SQL logging. I am logging Access-Accept and
Access-Reject. My problem is that access-rejects are appearing
scrambeled.. Example:
| 50 | us...@mydomain.tld | |
Access-Accept | 2010-09-10 10:53:36 |
| 51 | =7bam=3d1=7d917341235f4283123a58e52b623d2...@mydomain.tld |
| Access-Reject | 2010-09-10 10:53:39 |
| 52 | =7bam=3d1=7ac00fa703f004q25ed1ef4e3dcb5f4...@mydomain.tld |
| Access-Reject | 2010-09-10 10:53:47 |
| 53 | us...@mydomain.tld|
| Access-Accept | 2010-09-10 10:53:58 |
The SQL statement from sql_log module is:
Post-Auth = INSERT INTO ${postauth_table} \
(username, pass, reply, authdate) VALUES\
('%{SQL-User-Name}', '%{User-Password:-Chap-Password}', \
'%{reply:Packet-Type}', '%S');
How can I log the tried username in cleartext?
- Kristoffer Milligan
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
delayed update on sql logging
Hi, I am checking simultaneous-use information, logged users etc on sql server. The problem is, if a user logges in, nearly after a minute later i can see the logged on user on mysql tables. But i can see the same user with radlast command in the same second the user logs in. So it seems like there is a delay between updating the data on sql. And with this, user can log in mutliple times with same username even i set sim-use to 1. Is there any parameter for that??? Or what else can cause that problem. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: delayed update on sql logging
I am checking simultaneous-use information, logged users etc on sql server. The problem is, if a user logges in, nearly after a minute later i can see the logged on user on mysql tables. But i can see the same user with radlast command in the same second the user logs in. So it seems like there is a delay between updating the data on sql. And with this, user can log in mutliple times with same username even i set sim-use to 1. Is there any parameter for that??? Or what else can cause that problem. Are you using buffered-sql accounting? Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: delayed update on sql logging
I am checking simultaneous-use information, logged users etc on sql
server.
The problem is, if a user logges in, nearly after a minute later i can
see
the logged on user on mysql tables. But i can see the same user with
radlast command in the same second the user logs in.
So it seems like there is a delay between updating the data on sql.
And with this, user can log in mutliple times with same username even i
set sim-use to 1.
Is there any parameter for that???
Or what else can cause that problem.
Are you using buffered-sql accounting?
Ivan Kalik
Kalik Informatika ISP
How can i check if i use buffered or not?? I didnt change much in default
settings..
Here is my virtual server configuration on sites-enabled
server wireless {
authorize {
preprocess
files
update control {
Auth-Type := perl
}
perl
}
authenticate {
Auth-Type Perl {
perl
}
}
preacct {
acct_unique
files
}
accounting {
sql_wireless
}
session {
sql_wireless
} ..
And here is the sql.conf file for this sql sttement
sql sql_wireless {
database = mysql
driver = rlm_sql_${database}
# Connection info:
server = 1.1.1.1
login =
password =
# Database table configuration for everything except Oracle
radius_db = wireless
acct_table1 = radacct
acct_table2 = radacct
# Allow for storing data after authentication
postauth_table = radpostauth
authcheck_table = radcheck
authreply_table = radreply
groupcheck_table = radgroupcheck
groupreply_table = radgroupreply
# Table to keep group info
usergroup_table = usergroup
# If set to 'yes' (default) we read the group tables
# If set to 'no' the user MUST have Fall-Through = Yes in the
radreply table
# read_groups = yes
# Remove stale session if checkrad does not see a double login
deletestalesessions = yes
# number of sql connections to make to server
num_sql_socks = 10
# number of seconds to dely retrying on a failed database
# connection (per_socket)
connect_failure_retry_delay = 60
#readclients = yes
# Table to keep radius client info
nas_table = nas
# Read driver-specific configuration
$INCLUDE sql/${database}/dialup.conf
}
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: delayed update on sql logging
-Original Message- From: freeradius-users-bounces+kamil=extendbroadband@lists.freeradius.org [mailto:freeradius-users-bounces+kamil=extendbroadband@lists.freeradius. org] On Behalf Of Ivan Kalik Sent: Wednesday, September 30, 2009 1:07 PM To: FreeRadius users mailing list Subject: Re: delayed update on sql logging I am checking simultaneous-use information, logged users etc on sql server. The problem is, if a user logges in, nearly after a minute later i can see the logged on user on mysql tables. But i can see the same user with radlast command in the same second the user logs in. So it seems like there is a delay between updating the data on sql. And with this, user can log in mutliple times with same username even i set sim-use to 1. Is there any parameter for that??? Or what else can cause that problem. Are you using buffered-sql accounting? How can we use buffered-sql accounting with 1.1.7 ? Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.115/2403 - Release Date: 09/29/09 17:56:00 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: delayed update on sql logging
I am checking simultaneous-use information, logged users etc on sql server. The problem is, if a user logges in, nearly after a minute later i can see the logged on user on mysql tables. But i can see the same user with radlast command in the same second the user logs in. So it seems like there is a delay between updating the data on sql. And with this, user can log in mutliple times with same username even i set sim-use to 1. Is there any parameter for that??? Or what else can cause that problem. Are you using buffered-sql accounting? How can i check if i use buffered or not?? I didnt change much in default settings.. That would mean you don't. Do a debug radiusd -Xx of login and see how much time passes between login and accounting Start packet and is there a delay in inserting data into sql on processing Start packet. Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: delayed update on sql logging
I am checking simultaneous-use information, logged users etc on sql
server.
The problem is, if a user logges in, nearly after a minute later i can
see
the logged on user on mysql tables. But i can see the same user with
radlast command in the same second the user logs in.
So it seems like there is a delay between updating the data on sql.
And with this, user can log in mutliple times with same username even
i
set sim-use to 1.
Is there any parameter for that???
Or what else can cause that problem.
Are you using buffered-sql accounting?
How can i check if i use buffered or not?? I didnt change much in
default
settings..
That would mean you don't.
Do a debug radiusd -Xx of login and see how much time passes between login
and accounting Start packet and is there a delay in inserting data into
sql on processing Start packet.
Ivan Kalik
Kalik Informatika ISP
Here is the debug results..
This is the begining.. starting at 17:34:03
rad_recv: Access-Request packet from host 192.168.16.145 port 2078, id=14,
length=288
Vendor-14559-Attr-8 = 0x312e302e3131
Wed Sep 30 17:34:03 2009 : Debug: server lojnet {
Wed Sep 30 17:34:03 2009 : Debug: +- entering group authorize
...
Wed Sep 30 17:34:03 2009 : Debug: } # server lojnet
Sending Access-Accept of id 14 to 192.168.16.145 port 2078
Acct-Interim-Interval = 60
WISPr-Bandwidth-Max-Up = 2560
WISPr-Bandwidth-Max-Down = 1
Wed Sep 30 17:34:03 2009 : Debug: Finished request 0.
Wed Sep 30 17:34:03 2009 : Debug: Going to the next request
Wed Sep 30 17:34:03 2009 : Debug: Waking up in 4.9 seconds.
...Wed Sep 30 17:34:03 2009 : Debug: rlm_sql (sql_lojnet): sql_set_user
escaped user -- 'a'
Wed Sep 30 17:34:03 2009 : Debug: expand: %{Acct-Delay-Time} -
Wed Sep 30 17:34:03 2009 : Debug: expand:INSERT INTO
radacct (acctsessionid,acctuniqueid, username, $
Wed Sep 30 17:34:03 2009 : Debug: rlm_sql (sql_lojnet): Reserving sql
socket id: 8
Wed Sep 30 17:34:03 2009 : Debug: rlm_sql_mysql: MYSQL check_error: 1048
received
Wed Sep 30 17:34:03 2009 : Error: rlm_sql (sql_lojnet): Couldn't insert
SQL accounting START record - Column 'AcctStopTime' cannot be null
Wed Sep 30 17:34:03 2009 : Debug: expand: %{Acct-Delay-Time} -
Wed Sep 30 17:34:03 2009 : Debug: expand:UPDATE radacct
SET acctstarttime = '%S', acctstartdel$
Wed Sep 30 17:34:03 2009 : Debug: rlm_sql (sql_lojnet): Released sql
socket id: 8
Wed Sep 30 17:34:03 2009 : Debug: modsingle[accounting]: returned from
sql_lojnet (rlm_sql) for request 1
Wed Sep 30 17:34:03 2009 : Debug: ++[sql_lojnet] returns ok
Then at 17:35:03 i can see the user on sql
Wed Sep 30 17:35:03 2009 : Debug: +- entering group accounting
Wed Sep 30 17:35:03 2009 : Debug: modsingle[accounting]: calling detail
(rlm_detail) for request 2
Wed Sep 30 17:35:03 2009 : Debug: expand:
/var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d -
/var/log/freeradius/radacct/$
Wed Sep 30 17:35:03 2009 : Debug: rlm_detail:
/var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to
/var/log/freeradius/ra$
Wed Sep 30 17:35:03 2009 : Debug: expand: %t - Wed Sep 30 17:35:03
2009
Wed Sep 30 17:35:03 2009 : Debug: modsingle[accounting]: returned from
detail (rlm_detail) for request 2
Wed Sep 30 17:35:03 2009 : Debug: ++[detail] returns ok
Wed Sep 30 17:35:03 2009 : Debug: modsingle[accounting]: calling unix
(rlm_unix) for request 2
Wed Sep 30 17:35:03 2009 : Debug: modsingle[accounting]: returned from
unix (rlm_unix) for request 2
Wed Sep 30 17:35:03 2009 : Debug: ++[unix] returns noop
Wed Sep 30 17:35:03 2009 : Debug: modsingle[accounting]: calling
sql_lojnet (rlm_sql) for request 2
Wed Sep 30 17:35:03 2009 : Debug: expand: %{User-Name} - a
Wed Sep 30 17:35:03 2009 : Debug: rlm_sql (sql_lojnet): sql_set_user
escaped user -- 'a'
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Input-Gigawords} - 0
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Input-Octets} -
671161
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Output-Gigawords} - 0
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Output-Octets} -
40281
Wed Sep 30 17:35:03 2009 : Debug: expand:UPDATE radacct
SET framedipaddress = '%{Framed-IP-Address}',$
Wed Sep 30 17:35:03 2009 : Debug: rlm_sql (sql_lojnet): Reserving sql
socket id: 7
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Session-Time} - 61
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Delay-Time} -
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Input-Gigawords} - 0
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Input-Octets} -
671161
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Output-Gigawords} - 0
Wed Sep 30 17:35:03 2009 : Debug: expand: %{Acct-Output-Octets} -
40281
Wed Sep 30 17:35:03 2009 : Debug:
Re: delayed update on sql logging
At the first try of sql i see the error rlm_sql (sql_lojnet): Couldn't insert SQL accounting START record - Column 'AcctStopTime' cannot be null Maybe that might be causing this... So how can i fix that??? Something is wrong with your schema. Default is: acctstoptime datetime NULL default NULL Are you using schema that doesn't belong to this server version? Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: delayed update on sql logging
two options 1. allow null for AcctStopTime field (Alter table radacct ) 2. change queries in dialup.conf file for accounting (start query) and replace NULL with '0' for AcctStopTime Keep in mind that second option is much harder then the first one because you need to change all accounting queries (where ever you see) WHERE AcctStopTime = NULL in WHERE AcctStopTime = 0 Oguzhan Kayhan wrote: At the first try of sql i see the error rlm_sql (sql_lojnet): Couldn't insert SQL accounting START record - Column 'AcctStopTime' cannot be null Maybe that might be causing this... So how can i fix that??? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: delayed update on sql logging
At the first try of sql i see the error
rlm_sql (sql_lojnet): Couldn't insert SQL accounting START record -
Column
'AcctStopTime' cannot be null
Maybe that might be causing this...
So how can i fix that???
Something is wrong with your schema. Default is:
acctstoptime datetime NULL default NULL
Are you using schema that doesn't belong to this server version?
Ivan Kalik
Kalik Informatika ISP
Schema might belong to a previous version of freeradius.
But i solved this issue with changing dialup.conf as follows.Now it works...
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,xascendsessionsvrkey) \
VALUES \
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', \
'%{SQL-User-Name}', \
'%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', \
'%{NAS-Port-Type}', '%S', '-00-00 00:00:00', \
'0', '%{Acct-Authentic}', '%{Connect-Info}', \
'', '0', '0', \
'%{Called-Station-Id}', '%{Calling-Station-Id}', '', \
'%{Service-Type}', '%{Framed-Protocol}',
'%{Framed-IP-Address}', \
'%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: radpostauth sql logging of bad passwords
I am obviously missing something.
I tried commenting out that section and it did not work I then changed
it to :
post-auth {
reply_log
sql
sql_log
exec
Post-Auth-Type REJECT {
sql_log
}
}
Could someone toss me a bone or tell me what document I need to read?
On 2009-Apr-17, at 11:12, Alan DeKok wrote:
Guy Fraser wrote:
I thought this would be enough to make it log failed
authentications :
Yes. But to flat-text files, not to SQL.
post-auth {
reply_log
sql
sql_log
This says log to SQL on success.
exec
Post-Auth-Type REJECT {
attr_filter.access_reject
You could put SQL logging here, too.
The configuration has changed significantly since I last
contributed to
this project.
The main changes are moving text from one file to another. e.g. the
large chunks of authorize, etc. in radiusd.conf have moved to
separate
files.
But the main configuration is still pretty much the same. Older
configuration files can be used *almost* unchanged.
Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
--
Guy Fraser
Network Administrator
The Internet Centre
1-888-450-6787
(780)450-6787
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: radpostauth sql logging of bad passwords
Guy Fraser wrote: I am obviously missing something. I tried commenting out that section and it did not work I then changed it to : So... what happens? Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: radpostauth sql logging of bad passwords
On 2009-Apr-27, at 11:27, Alan DeKok wrote:
Guy Fraser wrote:
I am obviously missing something.
I tried commenting out that section and it did not work I then
changed
it to :
So... what happens?
As far as I could tell nothing changed when I commented out the REJECT
section :
post-auth {
reply_log
sql
sql_log
exec
# Post-Auth-Type REJECT {
# attr_filter.access_reject
# }
}
And I still do not get any failed authentications when I use :
post-auth {
reply_log
sql
sql_log
exec
Post-Auth-Type REJECT {
sql_log
}
}
I did not see any errors in any log files when I see the failed
attempts in the
/var/log/radacct/radiusd-DEFAULT-*.log file and there are no
corresponding
entries in /var/log/radacct/sqltrace.sql.
I was hoping there was an easy answer.
Does it look like something is broken or is this a configuration issue?
Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
--
Guy Fraser
Network Administrator
The Internet Centre
1-888-450-6787
(780)450-6787
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: radpostauth sql logging of bad passwords
On 2009-Apr-27, at 12:44, Ivan Kalik wrote:
On 2009-Apr-27, at 11:27, Alan DeKok wrote:
Guy Fraser wrote:
I am obviously missing something.
Ahem, did you read what sql_log does?
Yes it says :
modules {
...
sql_log {
path = ${radacctdir}/sql-relay
acct_table = radacct
postauth_table = radpostauth
sql_user_name = %{%{User-Name}:-DEFAULT}
Start = INSERT INTO ${acct_table} ...
Stop = UPDATE ${acct_table} SET ...
Alive = UPDATE ${acct_table} SET ...
Post-Auth = INSERT INTO ${postauth_table} ...
}
...
}
accounting {
...
sql_log
...
}
post-auth {
...
sql_log
...
}
And that my friend does not help me.
I tried commenting out that section and it did not work I then
changed
it to :
So... what happens?
As far as I could tell nothing changed when I commented out the
REJECT
section :
post-auth {
reply_log
sql
sql_log
exec
# Post-Auth-Type REJECT {
# attr_filter.access_reject
# }
}
Leave reject filter alone.
And I still do not get any failed authentications when I use :
post-auth {
reply_log
sql
sql_log
exec
Post-Auth-Type REJECT {
sql_log
}
}
List sql instead of sql_log. And put the filter back.
Are you saying this will work ?
post-auth {
reply_log
sql
sql_log
exec
Post-Auth-Type REJECT {
attr_filter.access_reject
sql
}
}
I have put it in an restarted the server.
Ivan Kalik
Kalik Informatika ISP
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
--
Guy Fraser
Network Administrator
The Internet Centre
1-888-450-6787
(780)450-6787
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: radpostauth sql logging of bad passwords
Guy Fraser wrote: I have installed : radiusd: FreeRADIUS Version 2.1.3, for host i386-portbld-freebsd7.1, built on Feb 26 2009 at 15:47:46 I have not been able figure out how to get it to log failed authentication attempts into the radpostauth sql table, like I had it working in Version 1. What do you mean by that? Q: I tried to do stuff, but it didn't work. A: Huh? Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: radpostauth sql logging of bad passwords
On 2009-Apr-17, at 03:08, Alan DeKok wrote:
Guy Fraser wrote:
I have installed :
radiusd: FreeRADIUS Version 2.1.3, for host i386-portbld-freebsd7.1,
built on Feb 26 2009 at 15:47:46
I have not been able figure out how to get it to log failed
authentication attempts
into the radpostauth sql table, like I had it working in Version 1.
What do you mean by that?
Q: I tried to do stuff, but it didn't work.
A: Huh?
I thought this would be enough to make it log failed authentications :
log {
destination = files
file = ${logdir}/radius.log
requests = ${logdir}/radacct/radiusd-%{%{Virtual-Server}:-DEFAULT}-%Y
%m%d.log
syslog_facility = daemon
stripped_names = no
auth = yes
auth_badpass = yes
auth_goodpass = no
}
Here is the recursive, uncommented and redacted configuration :
---
prefix = /usr/local
exec_prefix = ${prefix}
sysconfdir = ${prefix}/etc
localstatedir = /var
sbindir = ${exec_prefix}/sbin
logdir = /var/log
raddbdir = ${sysconfdir}/raddb
radacctdir = ${logdir}/radacct
name = radiusd
confdir = ${raddbdir}
run_dir = ${localstatedir}/run/radiusd
db_dir = ${raddbdir}
libdir = /usr/local/lib/freeradius-2.1.3
pidfile = ${run_dir}/${name}.pid
user = freeradius
group = freeradius
max_request_time = 30
cleanup_delay = 5
max_requests = 1024
listen {
type = auth
ipaddr = *
port = 1645
}
listen {
ipaddr = *
port = 1646
type = acct
}
hostname_lookups = no
allow_core_dumps = no
regular_expressions = yes
extended_expressions= yes
log {
destination = files
file = ${logdir}/radius.log
requests = ${logdir}/radacct/radiusd-%{%{Virtual-Server}:-DEFAULT}-%Y
%m%d.log
syslog_facility = daemon
stripped_names = no
auth = yes
auth_badpass = yes
auth_goodpass = no
}
checkrad = ${sbindir}/checkrad
security {
max_attributes = 200
reject_delay = 1
status_server = yes
}
proxy_requests = yes
$INCLUDE proxy.conf
#start : proxy.conf#
proxy server {
default_fallback = no
}
home_server localhost {
type = auth
ipaddr = 127.0.0.1
port = 1645
secret = XXX
response_window = 20
zombie_period = 40
revive_interval = 120
status_check = status-server
check_interval = 30
num_answers_to_alive = 3
}
home_server_pool my_auth_failover {
type = fail-over
home_server = localhost
}
realm LOCAL {
type= radius
authhost= LOCAL
accthost= LOCAL
}
realm domain.net {
type= radius
authhost= LOCAL
accthost= LOCAL
}
realm customer.com {
type= radius
authhost= x.x.x.x:1645
accthost= x.x.x.x:1646
secret = XXX
nostrip
}
...
#end#
$INCLUDE clients.conf
#start : clients.conf#
client localhost {
ipaddr = 127.0.0.1
secret = XXX
require_message_authenticator = no
nastype = other
}
#end#
thread pool {
start_servers = 5
max_servers = 32
min_spare_servers = 3
max_spare_servers = 10
max_requests_per_server = 0
}
modules {
$INCLUDE ${confdir}/modules/
#start : modules/*#
acct_unique {
key = User-Name, Acct-Session-Id, NAS-IP-Address, Client-IP-Address,
NAS-Port
}
always fail {
rcode = fail
}
always reject {
rcode = reject
}
always noop {
rcode = noop
}
always handled {
rcode = handled
}
always updated {
rcode = updated
}
always notfound {
rcode = notfound
}
always ok {
rcode = ok
simulcount = 0
mpp = no
}
attr_filter attr_filter.post-proxy {
attrsfile = ${confdir}/attrs
}
attr_filter attr_filter.pre-proxy {
attrsfile = ${confdir}/attrs.pre-proxy
}
attr_filter attr_filter.access_reject {
key = %{User-Name}
attrsfile = ${confdir}/attrs.access_reject
}
attr_filter attr_filter.accounting_response {
key = %{User-Name}
attrsfile = ${confdir}/attrs.accounting_response
}
attr_rewrite sanecallerid {
attribute = Called-Station-Id
searchin = packet
searchfor = [+ ]
replacewith =
ignore_case = no
new_attribute = no
max_matches = 10
append = no
}
chap {
}
checkval {
item-name = Calling-Station-Id
check-name = Calling-Station-Id
data-type = string
}
counter daily {
filename = ${db_dir}/db.daily
key = User-Name
count-attribute = Acct-Session-Time
reset = daily
counter-name = Daily-Session-Time
check-name = Max-Daily-Session
reply-name = Session-Timeout
allowed-servicetype = Framed-User
cache-size = 5000
}
detail {
detailfile = ${radacctdir}/%{Client-IP-Address}/detail-%Y%m%d
detailperm = 0600
Re: radpostauth sql logging of bad passwords
Guy Fraser wrote:
I thought this would be enough to make it log failed authentications :
Yes. But to flat-text files, not to SQL.
post-auth {
reply_log
sql
sql_log
This says log to SQL on success.
exec
Post-Auth-Type REJECT {
attr_filter.access_reject
You could put SQL logging here, too.
The configuration has changed significantly since I last contributed to
this project.
The main changes are moving text from one file to another. e.g. the
large chunks of authorize, etc. in radiusd.conf have moved to separate
files.
But the main configuration is still pretty much the same. Older
configuration files can be used *almost* unchanged.
Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
radpostauth sql logging of bad passwords
I have installed : radiusd: FreeRADIUS Version 2.1.3, for host i386-portbld-freebsd7.1, built on Feb 26 2009 at 15:47:46 I have not been able figure out how to get it to log failed authentication attempts into the radpostauth sql table, like I had it working in Version 1. -- Guy Fraser Network Administrator The Internet Centre 1-888-450-6787 (780)450-6787 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
postauth sql logging
Hi all, I have just recently migrated from 1.1.7 to 2.0.5. In 1.1.7 I had the postauth sql logging turned on to log successful and failed auth attempts. I not able to find where I would add it in 2.0.5 to enable this feature. I see the sql statement in the dialup.conf config file but I am unsure on how to invoke the sql query. Any pointers would be great. Thanks, Jeff. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: postauth sql logging
Jeff Crowe wrote: I have just recently migrated from 1.1.7 to 2.0.5. In 1.1.7 I had the postauth sql logging turned on to log successful and failed auth attempts. I not able to find where I would add it in 2.0.5 to enable this feature. Read radiusd.conf, especially the last few lines. It explains where the configuration has moved to. See raddb/sites-available/default. I see the sql statement in the dialup.conf config file but I am unsure on how to invoke the sql query. This is documented... Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: detail sql logging problem
[EMAIL PROTECTED] wrote: a further question on this one - as the detail relay virtual server buffered-sql is only supposed to run when the main thread isnt busy...and is only supposed to read detail file, log to SQL then 'be quiet' why, when it encounters such an issue does the main authentication/accounting etc thread not process anything? I'm not sure I haven't been able to test it myself, so I don't really know what's going on in that situation. I'd have thought that the virtual server would be moaning and complaining as much as it wants, but the main core functionality would just keep on going... I would think so, too. Maybe the detail file reader is re-queuing failed requests too quickly, and starving other threads from CPU... Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
detail sql logging problem
hi, further to last email, heres example packet: Tue Apr 15 12:20:56 2008 User-Name = x NAS-Port = 29 NAS-IP-Address = 192.168.1.28 Framed-IP-Address = 192.168.0.3 NAS-Identifier = wism Airespace-Wlan-Id = 1 Acct-Session-Id = 48048f97/00:11:12:12:14:11/8514 Acct-Authentic = RADIUS Tunnel-Type:0 = VLAN Tunnel-Medium-Type:0 = IEEE-802 Tunnel-Private-Group-Id:0 = 222 Acct-Status-Type = Stop Acct-Input-Octets = 1942107 Acct-Output-Octets = 5085070 Acct-Input-Packets = 9162 Acct-Output-Packets = 8299 Acct-Terminate-Cause = Lost-Service Acct-Session-Time = 0 Acct-Delay-Time = 0 Calling-Station-Id = 192.168.0.3 Called-Station-Id = 192.168.1.28 Acct-Unique-Session-Id = f7ebd89424c03437 Timestamp = 1208258456 Request-Authenticator = Verified as you can see, Stop request, due to lost service. however, sessio-time is 0 - i suspect this is because of mobility. they've moved from one wism controller to another or from one AP to another and then left the network altogther. either way, kit is reporting the value. in our SQL logging we look for the Acct-Session-Id, and the Timestamp and then use those to create the session time due to wierdnesses (see the example UPDATE comand in sql/postgresl/dialup.conf to get what I mean) so hope we dont actually care about what the kit tells us(!) alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: detail sql logging problem
[EMAIL PROTECTED] wrote:
further to last email, heres example packet:
...
Acct-Session-Time = 0
unlang. :)
accounting {
...
if (Acct-Sesion-Time != 0) {
sql
}
else {
ok
}
...
}
i.e. bypass the module that gets upset over 0 session time.
Alan DeKok.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: detail sql logging problem
Hi, unlang. :) yes - i was pondering that one. okay. and even better, use eg sql_log for the ones that are session-time = 0 so that i can capture them, know them, and see when the issue is fixed etc... alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
PostAUth SQL logging
I have freeradius logging failed login attempts to the postauth table, what in the sql syntax do I need to change to log the reason for the access-reject (ie password invalid, account expired, or session limit reached)? Cory - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL logging options
That's exactly what I was after, Thanks alot. Ben -- Original Message -- Date: Sat, 3 Sep 2005 20:18:19 +0200 From: Nicolas Baradakis [EMAIL PROTECTED] To: FreeRadius users mailing list freeradius-users@lists.freeradius.org Subject: Re: SQL logging options Reply-To: FreeRadius users mailing list freeradius-users@lists.freeradius.org [EMAIL PROTECTED] wrote: I currently have freeradius 1.04 working with mysql. It logs successful connections to the 'radpostauth' table, and accouting information to the 'radacct' table. Is it possible to configure freeradius to log more data to the mysql database, such as unsuccessful connections with bad passwords/certificates etc? You can run a SQL query for a failed connection by adding the module sql in the stanza Post-Auth-Type REJECT. See http://freeradius.org/radiusd/doc/Post-Auth-Type -- Nicolas Baradakis - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html __ 1Mb Tiscali Broadband for £14.99. Offer ends 30th June 2005 http://www.tiscali.co.uk/products/broadband - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
SQL logging options
Hi, I currently have freeradius 1.04 working with mysql. It logs successful connections to the 'radpostauth' table, and accouting information to the 'radacct' table. Is it possible to configure freeradius to log more data to the mysql database, such as unsuccessful connections with bad passwords/certificates etc? I would basically like to configure it to log *everything* to a mysql database. Thanks in advance, Ben Dowling __ 1Mb Tiscali Broadband for £14.99. Offer ends 30th June 2005 http://www.tiscali.co.uk/products/broadband - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL logging options
[EMAIL PROTECTED] wrote: I currently have freeradius 1.04 working with mysql. It logs successful connections to the 'radpostauth' table, and accouting information to the 'radacct' table. Is it possible to configure freeradius to log more data to the mysql database, such as unsuccessful connections with bad passwords/certificates etc? You can run a SQL query for a failed connection by adding the module sql in the stanza Post-Auth-Type REJECT. See http://freeradius.org/radiusd/doc/Post-Auth-Type -- Nicolas Baradakis - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
SQL logging delay issue.
We are running freeradius 0.9.3.1 on RH ES3. CDR accounting records from a Cisco AS5350 are logged to both a detail file and to Postgres SQL running on the same box. The issue appears to be the following: For some calls, our PRI will terminate the call immediately because of unknown number, busy line, etc. So immediate, that freeradius receives both the start, start update, and stop records at basically the same time. The problem this creates is that it appears the insertion of the start record has not completed when the update for the start and then the stop record occurs (multiple handles to the database). This causes the update and stop records to “fall-thru” the update process and do an insertion of a full record for both. Thus I have instances of one CDR record that has three entries, (2 partial and 1 full) in SQL instead of the single entry that 99% of the other CDR record do. I haven’t decided if I should approach this from the Cisco side or from the freeradius side in the form of some type of delay or retry for SQL accounting records. I haven’t been able to find a freeradius configuration parameter that does this. Any ideas? I can provide more info if needed. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: SQL logging delay issue.
Greg Stooksberry [EMAIL PROTECTED] wrote: We are running freeradius 0.9.3.1 You should upgrade to 1.0.2. For some calls, our PRI will terminate the call immediately because of unknown number, busy line, etc. So immediate, that freeradius receives both the start, start update, and stop records at basically the same time. That's fairly dumb... I haven't decided if I should approach this from the Cisco side or from the freeradius side in the form of some type of delay or retry for SQL accounting records. I haven't been able to find a freeradius configuration parameter that does this. Any ideas? I can provide more info if needed. There's no configuration parameter to control this, because I've never heard of this problem before. And I'm not sure what can be done to fix it, either. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: more detailed sql logging
Kris Efland [EMAIL PROTECTED] wrote: Clearly... As I said in my first email. There are no insert statements that coincide to what I am looking for and thus my original question about crafting my own sql statements. It's not just a matter of crafting your own SQL statements. The module is not generic, in that it expects to do certain queries in a certain order. So there is NO statement you can add to sql.conf to make the module do things in a different order. _I would like this information logged to sql instead, how do I do that?_ Source code modifications. The your NAS isn't sending accounting requests to the server. See the FAQ. The information is already at my disposal, hence the log file. Um... Access-Request packets are NOT accounting packets. You said that you listed sql in accounting, but the table had nothing in it. This is because the server is not receiving Accounting-Request packets. There is NO other explanation. I dont want to rely on the NAS to send the request or have to manage that in any way. Can I force the logging to SQL? I want to log ALL authentication requests to SQL, this seems like a pretty primitive feature. Thanks for the help. Source code modifications. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: more detailed sql logging
Score! Exactly the information I needed. Thank you. For clarification,sinceI havent written a module before...For example:
If i had the following block in radiusd.conf
post-auth{
Post-Auth-Type REJECT {
log_rejected_users
}
}
In the modules block in radiusd.conf... Can I enter raw sql syntax similar to those found in the sql.conf file? (blah = "INSERT INTO ...") Granted this isnt the greatest place to put this, but as long as the sql.conf include is before my module def all of the sql server information should already be in-line... do I have that right? Or can you do something clever in the sql.conf file?
Thanks again,
Kris
Nicolas Baradakis [EMAIL PROTECTED] wrote:
Kris Efland wrote: Packet-Type = Access-Request Sat Mar 5 15:04:02 2005 User-Name = "user" User-Password = "password" NAS-IP-Address = 1.2.3.4 Client-IP-Address = 1.3.4.5 Module-Failure-Message = "rlm_ldap: User not found" _I would like this information logged to sql instead, how do I do that?_See http://www.freeradius.org/cgi-bin/cvsweb.cgi/radiusd/doc/Post-Auth-Type?rev=1.4You can run a postauth query before the server sends an Accept-Reject,too. Modify the "radpostauth" table and the "postauth query" to log asmany attributes as you wish.-- Nicolas Baradakis
Re: more detailed sql logging
Kris Efland [EMAIL PROTECTED] wrote: I am simply trying to log who is trying to auth against the rad server, valid or not. Right now only postauth is being logged to sql and I'm trying to rectify that. That's fine. I would assume that someone trying to check authentic credentials would be logged to the 'authcheck_table' but feel free to correct that assumption. No... the radcheck table contains information that tells the server how to check authentication for the user. sql.conf clearly shows that the only authenticating logging query is the post-auth one. It also clearly shows that the authcheck_table compares the check items for the user. This is all documented in the comments in the file. I already have a 'sql' directive under the accounting block in radiusd.conf (line 1906). Currently the only table that has _anything_ is radpostauth. The your NAS isn't sending accounting requests to the server. See the FAQ. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: more detailed sql logging
On Thu, 2005-03-03 at 13:54 -0800, Kris Efland wrote: Excuse me if this has already been discussed but I couldnt find anything after searching... I have FR successfully up and running. I have it logging through sql, and authenticating through ldap. How do I go about getting more detailed logs out of FR through sql? Currently it is only logging to postauth_table and is therefore only showing successful logins. I need it to log to authcheck_table (radcheck) to see if people are failing to login properly. These are being sent to the logfile but not to sql. I have broken down the sql config files but they dont seem to have any insert statements into the radcheck sql table. Do I have to write these myself? or is it more simplistic than that? Can you do this when seperating the authentication mechanism from sql? Thanks in advance. Kris I am not exactly sure what you are trying to do, but you should never need to have the server write to radcheck. The table you are probably looking for is radacct. radcheck is equivalent to the first line of the users file. radacct is equivalent to the detail file. You likely want to add sql to the accounting section. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
more detailed sql logging
Excuse me if this has already been discussed but I couldnt find anything after searching... I have FR successfully up and running. I have it"logging" through sql, and authenticating through ldap. How do I go about getting more detailed logs out of FR through sql? Currently it is only logging to postauth_table and is therefore only showing successful logins. I need it to log to authcheck_table (radcheck) to see if people are failing to login properly. These are being sent to the logfile but not to sql. I have broken down the sql config files but they dont seem to have any insert statements into the radcheck sql table. Do I have to write these myself? or is it more simplistic than that? Can you do this when seperating the authentication mechanism from sql? Thanks in advance. Kris
