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
Hi,
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
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. which version of FR are you running? ideally you'd be with 2.x and then just activate the buffered-sql virtual server.
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
hmm, follow what the server says...dont use that expansion check. (i see it in your sql_log section)
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
yep - which is EXACTLY what radsqlrelay will do when you aim it at that file. this isnt live insertion. it creates a log file, which you then use radsqlrelay on. alan
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@lists.freeradius.org [mailto:freeradius-users-bounces+guillaume.chartrand=collanaud.qc.ca@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
Hi,
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.
to get the sql-relay file you will need to call sql_log in the accounting stanza and ensure that the sql_log is configured in the main server.
For the post_auth it can be userful to other person if it's integrated to the next update of freeradius.
noted - fix and updated has been submitted as bug 545 alan
Hi,
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.
to get the sql-relay file you will need to call sql_log in the accounting stanza and ensure that the sql_log is configured in the main server. It's my accounting section accounting { sql_log # Filter attributes from the accounting response. attr_filter.accounting_response } And in radiusd.conf 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');" } So it's defined in radiusd.conf and called in accounting section. What I'm missing?
For the post_auth it can be userful to other person if it's integrated to the next update of freeradius.
noted - fix and updated has been submitted as bug 545 Just for notice, I have again the warning in debug mode when I do the post_auth queries rlm_sql (sql): Processing sql_postauth expand: %{User-Name} -> guillaume rlm_sql (sql): sql_set_user escaped user --> 'guillaume' WARNING: Deprecated conditional expansion ":-". See "man unlang" for details expand: INSERT INTO radpostauth (username, pass, reply) VALUES ('%{User-Name}', '%{User-Password:-Chap-Password}','%{reply:Packet-Type}') -> INSERT INTO radpostauth (username, pass, reply) VALUES ('guillaume', 'Chap-Password','Access-Accept') expand: /usr/local/var/log/radius/sqltrace.sql -> /usr/local/var/log/radius/sqltrace.sql
And I don't know what is it. And it's fot the post_auth. The post_auth have just this post-auth { sql } Thank again
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
I re and reread man unlang And change the insert line with this line postauth_query = "INSERT INTO ${postauth_table} (username, pass, reply) VALUES ('%{User-Name}','%{%{User-Password}:-%{Chap-Password}}','%{reply:Packet- Type}')" It's the value chap-password who's corrected. So for the fix, please update the insert for the correct value Thank
For the post_auth it can be userful to other person if it's integrated to the next update of freeradius.
noted - fix and updated has been submitted as bug 545 Just for notice, I have again the warning in debug mode when I do the post_auth queries rlm_sql (sql): Processing sql_postauth expand: %{User-Name} -> guillaume rlm_sql (sql): sql_set_user escaped user --> 'guillaume' WARNING: Deprecated conditional expansion ":-". See "man unlang" for details expand: INSERT INTO radpostauth (username, pass, reply) VALUES ('%{User-Name}', '%{User-Password:-Chap-Password}','%{reply:Packet-Type}') -> INSERT INTO radpostauth (username, pass, reply) VALUES ('guillaume', 'Chap-Password','Access-Accept') expand: /usr/local/var/log/radius/sqltrace.sql -> /usr/local/var/log/radius/sqltrace.sql
And I don't know what is it. And it's fot the post_auth. The post_auth have just this post-auth { sql } Thank again
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Hi,
And change the insert line with this line postauth_query = "INSERT INTO ${postauth_table} (username, pass, reply) VALUES ('%{User-Name}','%{%{User-Password}:-%{Chap-Password}}','%{reply:Packet- Type}')"
yes. but do you REALLY want to log peoples passwords into a nice database? I've changed/obfuscated ours eg in your case... INSERT INTO ${postauth_table} (username, pass, reply) VALUES\ ('%{User-Name}','password','%{reply:Packet-Type}')" alan
Hi,
And change the insert line with this line postauth_query = "INSERT INTO ${postauth_table} (username, pass, reply) VALUES
('%{User-Name}','%{%{User-Password}:-%{Chap-Password}}','%{reply:Packet-
Type}')"
yes. but do you REALLY want to log peoples passwords into a nice database? I've changed/obfuscated ours eg in your case...
It's okay, but when I query my table, I've just realize that with my line, the password field is empty. And before that, when the Warning appears, the password field is always CHAP-PASSWORD so he doesn't insert the real password.
INSERT INTO ${postauth_table} (username, pass, reply) VALUES\ ('%{User-Name}','password','%{reply:Packet-Type}')"
But you're right, it's better with that. Thanks
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (2)
-
A.L.M.Buxey＠lboro.ac.uk -
Guillaume Chartrand