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