SQL log accounting and post_auth
Guillaume Chartrand
guillaume.chartrand at collanaud.qc.ca
Wed Apr 16 20:32:12 CEST 2008
I resolved some part of my question.
The post_auth part is now inserted in my sql database. What I modify is
In mssql.conf add these line
postauth_table = "radpostauth"
postauth_query = "INSERT INTO ${postauth_table} (username, pass, reply) VALUES ('%{User-Name}', '%{User-Password:-Chap-Password}','%{reply:Packet-Type}')"
Before that I also created a table who's not in the mssql/schema.sql
It's a table named radpostauth with this command
CREATE TABLE [radpostauth] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[userName] [varchar] (64) NOT NULL ,
[pass] [varchar] (64) NOT NULL ,
[reply] [varchar] (32) NOT NULL ,
[authdate] [datetime] NOT NULL
)
GO
ALTER TABLE [radpostauth] WITH NOCHECK ADD
CONSTRAINT [DF_radpostauth_userName] DEFAULT ('') FOR [userName],
CONSTRAINT [DF_radpostauth_pass] DEFAULT ('') FOR [pass],
CONSTRAINT [DF_radpostauth_reply] DEFAULT ('') FOR [reply],
CONSTRAINT [DF_radpostauth_authdate] DEFAULT (getdate()) FOR [authdate],
CONSTRAINT [PK_radpostauth] PRIMARY KEY NONCLUSTERED
(
[id]
) ON [PRIMARY]
With theses lines when an INSERT is made to the table, the table automaticly add the date in the authdate.
So for this, now it's working. But I have nothing in radacct table and even if I only keep the sql_log in the accounting section, i have nothing in my sql-relay file.
For the post_auth it can be userful to other person if it's integrated to the next update of freeradius.
When I write this email I've received the response from A L M Buxey who wrote this
>if you want to use the sql_logging function, ONLY uncomment the sql_log and configure the sql_log{} section as required. >if you activate sql as well, then it will attempt live SQL insertion into the database for incoming accounting packets.
So now I comment the sql_log for the post_Auth and leave the sql to make insert in my database and
I comment sql in accouting section and leave uncomment sql_log but I have nothing in sql-relay file
>which version of FR are you running?
>ideally you'd be with 2.x and then just activate the buffered-sql virtual server
I run 2.0.3
----------------------------
Guillaume Chartrand
Technicien informatique
Cégep régional de Lanaudière
Centre administratif, Repentigny
(450) 470-0911 poste 7218
-----Message d'origine-----
De : freeradius-users-bounces+guillaume.chartrand=collanaud.qc.ca at lists.freeradius.org [mailto:freeradius-users-bounces+guillaume.chartrand=collanaud.qc.ca at lists.freeradius.org] De la part de Guillaume Chartrand
Envoyé : 16 avril 2008 10:54
À : FreeRadius users mailing list
Objet : SQL log accounting and post_auth
Hi,
I want to log accounting information and post-auth information in my sql
database. I have an MSSQL database. In my accounting section I uncomment
sql and sql_log. In post_auth section I uncomment sql and sql_log too.
Here is the result I receive with debug mode
Login OK: [guillaume\000/<via Auth-Type = EAP>] (from client AP1 port 1
cli 00-0E-35-99-F3-E9)
+- entering group post-auth
rlm_sql (sql): Processing sql_postauth
expand: %{User-Name} -> guillaume
rlm_sql (sql): sql_set_user escaped user --> 'guillaume'
++[sql] returns noop
rlm_sql_log (sql_log): Processing sql_log_postauth
expand: %{User-Name} -> guillaume
expand: %{%{User-Name}:-DEFAULT} -> guillaume
rlm_sql_log (sql_log): sql_set_user escaped user --> 'guillaume'
WARNING: Deprecated conditional expansion ":-". See "man unlang" for
details
expand: INSERT INTO radpostauth
(username, pass, reply, authdate) VALUES
('%{User-Name}', '%{User-Password:-Chap-Password}',
'%{reply:Packet-Type}', '%S'); -> INSERT INTO radpostauth
(username, pass, reply, authdate) VALUES
('guillaume', 'Chap-Password', 'Access-Accept',
'2008-04-16 09:40:46');
expand: /usr/local/var/log/radius/radacct/sql-relay ->
/usr/local/var/log/radius/radacct/sql-relay
++[sql_log] returns ok
MS-MPPE-Recv-Key =
0xddbdd27124caa81a4d0abacd8aa22d99cff95b591717efff32054bbeec88959c
MS-MPPE-Send-Key =
0x1326576688892a9369c4e6f3246aca4a65b572b1767232847b10a93935535b70
EAP-Message = 0x034f0004
Message-Authenticator = 0x00000000000000000000000000000000
User-Name = "guillaume"
Finished request 9.
So why the sql module return noop... And didn't insert anything in my
table.
With the sql_log module, I've just insert the post_auth command, not the
other, but in my sql_log section I have other thing like that.
sql_log {
path = "${radacctdir}/sql-relay"
acct_table = "radacct"
postauth_table = "radpostauth"
sql_user_name = "%{%{User-Name}:-DEFAULT}"
Start = "INSERT INTO ${acct_table} (AcctSessionId,
UserName, \
NASIPAddress, FramedIPAddress, AcctStartTime,
AcctStopTime, \
AcctSessionTime, AcctTerminateCause) VALUES
\
('%{Acct-Session-Id}', '%{User-Name}',
'%{NAS-IP-Address}', \
'%{Framed-IP-Address}', '%S', '0', '0', '');"
Stop = "INSERT INTO ${acct_table} (AcctSessionId,
UserName, \
NASIPAddress, FramedIPAddress, AcctStartTime,
AcctStopTime, \
AcctSessionTime, AcctTerminateCause) VALUES
\
('%{Acct-Session-Id}', '%{User-Name}',
'%{NAS-IP-Address}', \
'%{Framed-IP-Address}', '0', '%S',
'%{Acct-Session-Time}', \
'%{Acct-Terminate-Cause}');"
Alive = "INSERT INTO ${acct_table} (AcctSessionId,
UserName, \
NASIPAddress, FramedIPAddress, AcctStartTime,
AcctStopTime, \
AcctSessionTime, AcctTerminateCause) VALUES
\
('%{Acct-Session-Id}', '%{User-Name}',
'%{NAS-IP-Address}', \
'%{Framed-IP-Address}', '0', '0',
'%{Acct-Session-Time}','');"
Post-Auth = "INSERT INTO ${postauth_table}
\
(username, pass, reply, authdate) VALUES
\
('%{User-Name}', '%{User-Password:-Chap-Password}',
\
'%{reply:Packet-Type}', '%S');"
}
And for the warning for := I look in man unlang but I didn't find where
to change the := in the sql_log module
The sql_relay file contains this line
INSERT INTO radpostauth (username, pass, reply, authdate) VALUES
('guillaume', 'Chap-Password','Access-Accept', '2008-04-16 10:04:59');
And if I take that line and put in my sql query, it's work and
succcesfully insert the info
Thanks
----------------------------
Guillaume
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
More information about the Freeradius-Users
mailing list