usergroup problems with separate auth and accounting databases

[Trey Scarborough]

Alan DeKok wrote:
> Trey Scarborough wrote:
>   
>> Yes I am aware of how it is Documented I followed the documentation but
>> still is not functioning correctly.
>>
>> I have a configuration that is similar to as follows
>>     
>
>   <sigh>  Similar is not the same.
>
>   Perhaps you could explain in *detail* what you are trying to do with
> SQL groups.  Use examples from your cvonfiguration, not invented ones.
>
>   Alan DeKok.
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
>   
All I am trying to do is run the radius auth querys on a database on one 
machine and the accounting on another in another database. The problem I 
am seeing is that when the additional sql configuration is put in for 
the  accounting database it begins to use that configuration for the 
group_membership_query which is not in the accounting database and 
fails. If I remove the sql-auth from the accounting configuration it 
runs fine using the rad-auth sql configuration. Here is the exerts from 
my configuration. I am trying to set some radreply items with sql and 
some by the users file by group. This works fine until I try to seperate 
the databases.

authorize {
   preprocess
   chap
   mschap
   suffix
   sql-auth
   files
}
accounting {
   detail
   radutmp
   sql-acct  #works when this line is commented out
}

#sql.conf file
sql sql-auth {
   driver = "rlm_sql_mysql"
   server = "localhost"
   login = "radius"
   password = "radpass"
   radius_db = "radius"
   postauth_table = "radpostauth"
   authcheck_table = "radcheck"
   authreply_table = "radreply"
   groupcheck_table = "radgroupcheck"
   groupreply_table = "radgroupreply"
   usergroup_table = "usergroup"
   nas_table = "nas"
   deletestalesessions = no
   sqltrace = no
   sqltracefile = ${logdir}/sqltrace.sql
   num_sql_socks = 5
   connect_failure_retry_delay = 60
   sql_user_name = "%{User-Name}"
  
    authorize_check_query = "SELECT id, UserName, Attribute, Value, op \
             FROM ${authcheck_table} \
             WHERE Username = '%{SQL-User-Name}' \
             ORDER BY id"
      authorize_reply_query = "SELECT id, UserName, Attribute, Value, op \
             FROM ${authreply_table} \
             WHERE Username = '%{SQL-User-Name}' \
             ORDER BY id"
   group_membership_query = "SELECT GroupName FROM ${usergroup_table} 
WHERE UserName='%{SQL-User-Name}'"

   #
   # Set to 'yes' to read radius clients from the database ('nas' table)
   readclients = yes
}

sql sql-acct {
   driver = "rlm_sql_mysql"
   server = "192.168.5.84"
   login = "radius"
   password = "radpass"
   radius_db = "radius-acct"
   acct_table1 = "radacct"
   acct_table2 = "radacct"
   accounting_onoff_query = "UPDATE ${acct_table1} SET 
AcctStopTime='%S', AcctSessionTime=unix_timestamp('%S') - 
unix_timestamp(AcctStartTime), 
AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = 
'%{Acct-Delay-Time}' WHERE AcctSessionTime=0 AND AcctStopTime=0 AND 
NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'"
  
   accounting_update_query = "UPDATE ${acct_table1} \
          SET FramedIPAddress = '%{Framed-IP-Address}', \
          AcctSessionTime = '%{Acct-Session-Time}', \
          AcctInputOctets = '%{Acct-Input-Octets}', \
          AcctOutputOctets = '%{Acct-Output-Octets}' \
          WHERE AcctSessionId = '%{Acct-Session-Id}' \
          AND UserName = '%{SQL-User-Name}' \
          AND NASIPAddress= '%{NAS-IP-Address}'"
 
   accounting_update_query_alt = "INSERT into ${acct_table1} 
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, 
NASPortType, AcctStartTime, AcctSessionTime, AcctAuthentic, 
ConnectInfo_start, AcctInputOctets, AcctOutputOctets, CalledStationId, 
CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, 
AcctStartDelay) values('%{Acct-Session-Id}', 
'%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', 
'%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', 
DATE_SUB('%S',INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0}) 
SECOND), '%{Acct-Session-Time}', '%{Acct-Authentic}', '', 
'%{Acct-Input-Octets}', '%{Acct-Output-Octets}', '%{Called-Station-Id}', 
'%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', 
'%{Framed-IP-Address}', '0')"
 
   accounting_start_query = "INSERT into ${acct_table1} (AcctSessionId, 
AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, 
AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, 
ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, 
CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, 
FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay) 
values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', 
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', 
'%{NAS-Port-Type}', '%S', '0', '0', '%{Acct-Authentic}', 
'%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', 
'%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', 
'%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')"
 
   accounting_start_query_alt  = "UPDATE ${acct_table1} SET 
AcctStartTime = '%S', AcctStartDelay = '%{Acct-Delay-Time}', 
ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = 
'%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress 
= '%{NAS-IP-Address}'"
 
   accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime = 
'%S', AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = 
'%{Acct-Input-Octets}', AcctOutputOctets = '%{Acct-Output-Octets}', 
AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = 
'%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}' WHERE 
AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' 
AND NASIPAddress = '%{NAS-IP-Address}'"
 
   accounting_stop_query_alt = "INSERT into ${acct_table2} 
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, 
NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, 
AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, 
AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, 
ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, 
AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', 
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', 
'%{NAS-Port-Type}', DATE_SUB('%S', INTERVAL (%{Acct-Session-Time:-0} + 
%{Acct-Delay-Time:-0}) SECOND), '%S', '%{Acct-Session-Time}', 
'%{Acct-Authentic}', '', '%{Connect-Info}', '%{Acct-Input-Octets}', 
'%{Acct-Output-Octets}', '%{Called-Station-Id}', 
'%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', 
'%{Framed-Protocol}', '%{Framed-IP-Address}', '0', '%{Acct-Delay-Time}')"

}