sql accounting not working for realms
Taylor, Chris
ctaylor at amtelecom.ca
Thu Dec 31 14:19:21 CET 2009
I am running FreeRadius ver 1.1.3, and have LDAP setup for
authentication and MySQL for logging.
I have a few realms setup as we have two NAS devices one sends stripped
usernames (Nortel Shasta), the other sends non-stripped usernames(Cisco
ASR). Anything that comes in with a stripped username will end up in the
NULL realm and be both authenticated via LDAP and logged into MySQL.
Anything that comes in with a username of username at domain.net just gets
authenticated, not logged.
I am sure that it's just a configuration issue but I can figure out
where, and have searched the archive without any luck.
Any help would be appreciated.
Below are my startup debug proxy.conf, and two examples of what is
happening.
Proxy.conf
###########################################
proxy server {
synchronous = no
retry_delay = 5
retry_count = 3
dead_time = 120
default_fallback = yes
post_proxy_authorize = no
}
realm LOCAL {
type = radius
authhost = LOCAL
accthost = LOCAL
}
realm NULL {
type = radius
authhost = LOCAL
accthost = LOCAL
}
realm domain.net {
type = radius
authhost = LOCAL
accthost = LOCAL
strip
}
###########################################
Startup Debug
Radius.conf
###########################################
realm suffix {
format = suffix
delimiter = "@"
ignore_default = no
ignore_null = no
accounting {
acct_unique
#detail
sql
}
###########################################
[root at ns1 ~]# radiusd -X
Starting - reading configuration files ...
reread_config: reading radiusd.conf
Config: including file: /usr/local/etc/raddb/proxy.conf
Config: including file: /usr/local/etc/raddb/clients.conf
Config: including file: /usr/local/etc/raddb/snmp.conf
Config: including file: /usr/local/etc/raddb/sql.conf
Module: Loaded preprocess
preprocess: huntgroups = "/usr/local/etc/raddb/huntgroups"
preprocess: hints = "/usr/local/etc/raddb/hints"
preprocess: with_ascend_hack = no
preprocess: ascend_channels_per_line = 23
preprocess: with_ntdomain_hack = no
preprocess: with_specialix_jetstream_hack = no
preprocess: with_cisco_vsa_hack = no
preprocess: with_alvarion_vsa_hack = no
Module: Instantiated preprocess (preprocess)
Module: Loaded files
files: usersfile = "/usr/local/etc/raddb/users"
files: acctusersfile = "/usr/local/etc/raddb/acct_users"
files: preproxy_usersfile = "/usr/local/etc/raddb/preproxy_users"
files: compat = "no"
Module: Instantiated files (files)
Module: Loaded realm
realm: format = "suffix"
realm: delimiter = "@"
realm: ignore_default = no
realm: ignore_null = no
Module: Instantiated realm (suffix)
Module: Loaded Acct-Unique-Session-Id
acct_unique: key = "User-Name, Acct-Session-Id, NAS-IP-Address,
Client-IP-Address, NAS-Port"
Module: Instantiated acct_unique (acct_unique)
Module: Loaded SQL
sql: driver = "rlm_sql_mysql"
sql: server = "mysql-db.domain.net"
sql: port = ""
sql: login = "XXXX"
sql: password = "XXXX"
sql: radius_db = "radius"
sql: nas_table = "nas"
sql: sqltrace = no
sql: sqltracefile = "/var/log/radius/sqltrace.sql"
sql: readclients = no
sql: deletestalesessions = yes
sql: num_sql_socks = 10
sql: sql_user_name = "%{Stripped-User-Name}"
sql: default_user_profile = ""
sql: query_on_not_found = no
sql: authorize_check_query = "SELECT id,UserName,Attribute,Value,op FROM
radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id"
sql: authorize_reply_query = "SELECT id,UserName,Attribute,Value,op FROM
radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id"
sql: authorize_group_check_query = "SELECT
radgroupcheck.id,radgroupcheck.GroupName,radgroupcheck.Attribute,radgrou
pcheck.Value,radgroupcheck.op FROM radgroupcheck,usergroup WHERE
usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName =
radgroupcheck.GroupName ORDER BY radgroupcheck.id"
sql: authorize_group_reply_query = "SELECT
radgroupreply.id,radgroupreply.GroupName,radgroupreply.Attribute,radgrou
preply.Value,radgroupreply.op FROM radgroupreply,usergroup WHERE
usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName =
radgroupreply.GroupName ORDER BY radgroupreply.id"
sql: accounting_onoff_query = "UPDATE radacct 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'"
sql: accounting_update_query = "UPDATE radacct SET FramedIPAddress =
'%{Framed-IP-Address}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND
UserName = '%{SQL-User-Name}' AND NASIPAddress= '%{NAS-IP-Address}' AND
AcctStopTime = 0"
sql: accounting_update_query_alt = ""
sql: accounting_start_query = "INSERT into radacct (RadAcctId,
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')"
sql: accounting_start_query_alt = "UPDATE radacct 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}' AND
AcctStopTime = 0"
sql: accounting_stop_query = "UPDATE radacct 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}' AND AcctStopTime = 0"
sql: accounting_stop_query_alt = "INSERT into radacct (RadAcctId,
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}', '0', '%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}')"
sql: group_membership_query = "SELECT GroupName FROM usergroup WHERE
UserName='%{SQL-User-Name}'"
sql: connect_failure_retry_delay = 2
sql: simul_count_query = "SELECT COUNT(*) FROM radacct WHERE
UserName='%{SQL-User-Name}' AND AcctStopTime = 0"
sql: simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName,
NASIPAddress, NASPortId, FramedIPAddress, CallingStationId,
FramedProtocol FROM radacct WHERE UserName='%{SQL-User-Name}' AND
AcctStopTime = 0"
sql: postauth_query = ""
sql: safe-characters =
"@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
rlm_sql (sql): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and
linked
rlm_sql (sql): Attempting to connect to XXX at mysql-db.domain.net:/radius
rlm_sql (sql): starting 0
rlm_sql (sql): Attempting to connect rlm_sql_mysql #0
rlm_sql_mysql: Starting connect to MySQL server for #0
rlm_sql (sql): Connected new DB handle, #0
rlm_sql (sql): starting 1
rlm_sql (sql): Attempting to connect rlm_sql_mysql #1
rlm_sql_mysql: Starting connect to MySQL server for #1
rlm_sql (sql): Connected new DB handle, #1
rlm_sql (sql): starting 2
rlm_sql (sql): Attempting to connect rlm_sql_mysql #2
rlm_sql_mysql: Starting connect to MySQL server for #2
rlm_sql (sql): Connected new DB handle, #2
rlm_sql (sql): starting 3
rlm_sql (sql): Attempting to connect rlm_sql_mysql #3
rlm_sql_mysql: Starting connect to MySQL server for #3
rlm_sql (sql): Connected new DB handle, #3
rlm_sql (sql): starting 4
rlm_sql (sql): Attempting to connect rlm_sql_mysql #4
rlm_sql_mysql: Starting connect to MySQL server for #4
rlm_sql (sql): Connected new DB handle, #4
rlm_sql (sql): starting 5
rlm_sql (sql): Attempting to connect rlm_sql_mysql #5
rlm_sql_mysql: Starting connect to MySQL server for #5
rlm_sql (sql): Connected new DB handle, #5
rlm_sql (sql): starting 6
rlm_sql (sql): Attempting to connect rlm_sql_mysql #6
rlm_sql_mysql: Starting connect to MySQL server for #6
rlm_sql (sql): Connected new DB handle, #6
rlm_sql (sql): starting 7
rlm_sql (sql): Attempting to connect rlm_sql_mysql #7
rlm_sql_mysql: Starting connect to MySQL server for #7
rlm_sql (sql): Connected new DB handle, #7
rlm_sql (sql): starting 8
rlm_sql (sql): Attempting to connect rlm_sql_mysql #8
rlm_sql_mysql: Starting connect to MySQL server for #8
rlm_sql (sql): Connected new DB handle, #8
rlm_sql (sql): starting 9
rlm_sql (sql): Attempting to connect rlm_sql_mysql #9
rlm_sql_mysql: Starting connect to MySQL server for #9
rlm_sql (sql): Connected new DB handle, #9
Module: Instantiated sql (sql)
Module: Loaded radutmp
radutmp: filename = "/var/log/radius/radutmp"
radutmp: username = "%{User-Name}"
radutmp: case_sensitive = no
radutmp: check_with_nas = yes
radutmp: perm = 384
radutmp: callerid = yes
Module: Instantiated radutmp (radutmp)
Listening on authentication *:1812
Listening on accounting *:1813
Listening on proxy *:1814
Ready to process requests.
##################################################################
Connection from CISCO with full username
########################################################################
##################
rad_recv: Access-Request packet from host X.X.X.X:1645, id=218,
length=105
Framed-Protocol = PPP
User-Name = "username1 at domain.net"
User-Password = "userpasswd"
NAS-Port-Type = Virtual
NAS-Port = 0
NAS-Port-Id = "1/0/5/241"
Service-Type = Framed-User
NAS-IP-Address = X.X.X.X
rad_lowerpair: User-Name now 'username at domain.net'
rad_rmspace_pair: User-Name now 'username1 at domain.net'
Processing the authorize section of radiusd.conf
modcall: entering group authorize for request 58
modcall[authorize]: module "preprocess" returns ok for request 58
users: Matched entry DEFAULT at line 152
users: Matched entry DEFAULT at line 172
users: Matched entry DEFAULT at line 183
modcall[authorize]: module "files" returns ok for request 58
rlm_realm: Looking up realm "domain.net" for User-Name =
"username1 at domain.net"
rlm_realm: Found realm "domain.net"
rlm_realm: Adding Stripped-User-Name = "username1"
rlm_realm: Proxying request from user username1 to realm domain.net
rlm_realm: Adding Realm = "domain.net"
rlm_realm: Authentication realm is LOCAL.
modcall[authorize]: module "suffix" returns noop for request 58
modcall: entering group redundant for request 58
rlm_ldap: - authorize
rlm_ldap: performing user authorization for username1
radius_xlat: '(&(objectClass=posixAccount)(uid=username1))'
radius_xlat: 'dc=domain, dc=net'
rlm_ldap: ldap_get_conn: Checking Id: 0
rlm_ldap: ldap_get_conn: Got Id: 0
rlm_ldap: performing search in dc=domain, dc=net, with filter
(&(objectClass=posixAccount)(uid=username1))
rlm_ldap: checking if remote access for username1 is allowed by cn
rlm_ldap: Added password 1975 in check items
rlm_ldap: looking for check items in directory...
rlm_ldap: Adding radiusSimultaneousUse as Simultaneous-Use, value 99 &
op=21
rlm_ldap: Adding radiusAuthType as Auth-Type, value local & op=21
rlm_ldap: looking for reply items in directory...
rlm_ldap: user usename1 authorized to use remote access
rlm_ldap: ldap_release_conn: Release Id: 0
modcall[authorize]: module "ldap1" returns ok for request 58
modcall: leaving group redundant (returns ok) for request 58
modcall: leaving group authorize (returns ok) for request 58
rad_check_password: Found Auth-Type local
auth: type Local
auth: user supplied User-Password matches local User-Password
Processing the session section of radiusd.conf
modcall: entering group session for request 58
radius_xlat: '/var/log/radius/radutmp'
modcall[session]: module "radutmp" returns ok for request 58
modcall: leaving group session (returns ok) for request 58
Login OK: [username1] (from client bari-bras1 port 0)
Sending Access-Accept of id 218 to 24.139.16.70 port 1645
Framed-IP-Address = 255.255.255.254
Service-Type = Framed-User
Framed-Protocol = PPP
Framed-Compression = Van-Jacobson-TCP-IP
Finished request 58
########################################################################
######################
Connection from Shasta with stripped username
########################################################################
#######################
Going to the next request
ad_recv: Accounting-Request packet from host X.X.X.X.:49157, id=108,
length=178
Acct-Status-Type = Start
User-Name = "username2"
Event-Timestamp = "Dec 30 2009 09:09:14 EST"
Service-Type = Framed-User
NAS-IP-Address = X.X.X.X
Shasta-Attr-4 =
0x53686173746120353030303a2069534f532028746d292c20352e342e3000
NAS-Port = 721420288
Shasta-Attr-9 = 0x30303a31383a66333a32383a63643a3731
Shasta-Attr-10 = 0x0000a572
Acct-Session-Id = "1d069021"
Acct-Authentic = RADIUS
Framed-IP-Address = 67.58.205.173
Framed-IP-Netmask = 255.255.255.255
Calling-Station-Id = "aylm05300000000"
rad_lowerpair: User-Name now 'username2'
rad_rmspace_pair: User-Name now 'username2'
Processing the preacct section of radiusd.conf
modcall: entering group preacct for request 63
modcall[preacct]: module "preprocess" returns noop for request 63
rlm_realm: No '@' in User-Name = "username2", looking up realm NULL
rlm_realm: Found realm "NULL"
rlm_realm: Adding Stripped-User-Name = "username2"
rlm_realm: Proxying request from user username2 to realm NULL
rlm_realm: Adding Realm = "NULL"
rlm_realm: Accounting realm is LOCAL.
modcall[preacct]: module "suffix" returns noop for request 63
modcall[preacct]: module "files" returns noop for request 63
modcall: leaving group preacct (returns noop) for request 63
Processing the accounting section of radiusd.conf
modcall: entering group accounting for request 63
rlm_acct_unique: Hashing 'NAS-Port = 721420288,Client-IP-Address =
X.X.X.X,NAS-IP-Address = 67.58.194.98,Acct-Session-Id =
"1d069021",User-Name = "username2"'
rlm_acct_unique: Acct-Unique-Session-ID = "e781a2ca4624e026".
modcall[accounting]: module "acct_unique" returns ok for request 63
radius_xlat: 'username2'
rlm_sql (sql): sql_set_user escaped user --> 'username2'
radius_xlat: 'INSERT into radacct (RadAcctId, 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('', '1d069021', 'e781a2ca4624e026', 'username2', 'NULL',
'X.X.X.X', '721420288', '', '2009-12-30 09:08:55', '0', '0', 'RADIUS',
'', '', '0', '0', '', 'aylm05300000000', '', 'Framed-User', '',
'67.58.205.173', '', '0')'
rlm_sql (sql): Reserving sql socket id: 5
rlm_sql (sql): Released sql socket id: 5
modcall[accounting]: module "sql" returns ok for request 63
modcall: leaving group accounting (returns ok) for request 63
Sending Accounting-Response of id 108 to X.X.X.X port 49157
Finished request 63
########################################################################
############################
Thanks,
Chris
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20091231/d55cb459/attachment.html>
More information about the Freeradius-Users
mailing list