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