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