SQL log accounting and post_auth

16 Apr 2008

      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

SQL log accounting and post_auth

Guillaume Chartrand