Проблема заключается в том что FreeRADIUS нe сбрасывает данные аккаунтинга в базу ( MySQL 3.23.58 )
----sql.conf-----sql {
# Database type
driver = "rlm_sql_mysql"
# Connect info
server = "localhost"
login = "root"
password = ""
# Database table configuration
radius_db = "base_v06"
# radius_db = "radius"
# Print all SQL statements when in debug mode (-x)
sqltrace = yes
sqltracefile = /var/log/freeradius/sqltrace.sql
# number of sql connections to make to server
num_sql_socks = 5
# number of seconds to dely retrying on a failed database
# connection (per_socket)
connect_failure_retry_delay = 60
sql_user_name = "%{User-Name}"
authorize_check_query = "SELECT users.id, users.login, 'Password', users.password, ':=' FROM users WHERE users.login = '%{SQL-User-Name}' AND users.block = '0
'"
authorize_reply_query = "SELECT users.id, users.login, 'Framed-IP-Address', users.ip, ':=' FROM users WHERE users.login = '%{SQL-User-Name}' AND users.block =
'0'"
accounting_onoff_query = "UPDATE radacct SET AcctStopTime='%S', AcctSessionTime=unix_timestamp('%S') - unix_timestamp(AcctStartTime), AcctTerminateCause='%{Ac
ct-Terminate-Cause}', AcctStopDelay = %{Acct-Delay-Time} WHERE AcctSessionTime=0 AND AcctStopTime=0 AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'"
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"
accounting_start_query = "INSERT into radacct (RadAcctId, AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, A
cctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateC
ause, 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}', '%{Ca
lling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')
accounting_stop_query = "UPDATE radacct SET AcctStopTime = '%S', AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets}', AcctOutpu
tOctets = '%{Acct-Output-Octets}', AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}' WHERE Ac
ctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime = 0"----------------------------
Как видно для укаунтинга я использовал стандартную таблицу базы radiusa' radacct , запросы также стандартны . Никаких ошибок в логах или конфигах нет .
Подскажите в чем может быть проблема ?
Кстати авторизация проходит успешно , без каких-либо ошибок !
1) Включи debug и сконфигурируй в sql.conf следующее:
# Print all SQL statements when in debug mode (-x)
sqltrace = yes
sqltracefile = ${logdir}/sqltrace.sql
Так ты увидишь, как именно обрабатываются запросы к базе.2) Посмотри с помощью ethereal, как от NAS приходят запросы к RADIUS, и посылает ли он запрсы к mysql серверу.
Ну и вообще, живы ли сервера, и те ли порты слушают при тестах?
Не режутся ли порты файерволами?
>1) Включи debug и сконфигурируй в sql.conf следующее:
> # Print all SQL
>statements when in debug mode (-x)
> sqltrace = yes
> sqltracefile = ${logdir}/sqltrace.sql
>Так ты увидишь, как именно обрабатываются запросы к базе.в sqltrace нечего не пишется , хотя запросы на авторизацию работают .
>2) Посмотри с помощью ethereal, как от NAS приходят запросы к RADIUS,
>и посылает ли он запрсы к mysql серверу.Приходят , более того RADIUS успешно их отрабатывает :
rad_recv: Access-Request packet from host 192.168.0.41:1223, id=22, length=166
NAS-Identifier = "stand02.xxxx.xx"
NAS-Port = 0
NAS-Port-Type = Virtual
Service-Type = Framed-User
Framed-Protocol = PPP
Calling-Station-Id = "192.168.0.34"
User-Name = "user1"
MS-CHAP-Challenge = 0xbb1e6886b6e044bde8bea40e90aee628
MS-CHAP2-Response = 0x010036a7d2acb805c307d6ba8e3ecf18f09d0000000000000000fa54d7503bac0317c0a4cc592213c4fe5f17157c529bc400
modcall: entering group authorize for request 0
radius_xlat: 'user1'
rlm_sql (sql): sql_set_user escaped user --> 'user1'
radius_xlat: 'SELECT users.id, users.login, 'Password', users.password, ':=' FROM users WHERE users.login = 'user1' AND users.block = '0''
rlm_sql (sql): Reserving sql socket id: 4
rlm_sql_mysql: query: SELECT users.id, users.login, 'Password', users.password, ':=' FROM users WHERE users.login = 'user1' AND users.block = '0'
radius_xlat: ''
radius_xlat: 'SELECT users.id, users.login, 'Framed-IP-Address', users.ip, ':=' FROM users WHERE users.login = 'user1' AND users.block = '0''
rlm_sql_mysql: query: SELECT users.id, users.login, 'Framed-IP-Address', users.ip, ':=' FROM users WHERE users.login = 'user1' AND users.block = '0'
radius_xlat: ''
rlm_sql (sql): Released sql socket id: 4
modcall[authorize]: module "sql" returns ok for request 0
modcall[authorize]: module "preprocess" returns ok for request 0
modcall[authorize]: module "chap" returns noop for request 0
modcall[authorize]: module "eap" returns noop for request 0
rlm_realm: No '@' in User-Name = "user1", looking up realm NULL
rlm_realm: No such realm "NULL"
modcall[authorize]: module "suffix" returns noop for request 0
modcall[authorize]: module "files" returns notfound for request 0
rlm_mschap: Found MS-CHAP attributes. Setting 'Auth-Type := MS-CHAP'
modcall[authorize]: module "mschap" returns ok for request 0
modcall: group authorize returns ok for request 0
rad_check_password: Found Auth-Type MS-CHAP
auth: type "MS-CHAP"
modcall: entering group Auth-Type for request 0
rlm_mschap: doing MS-CHAPv2 with NT-Password
rlm_mschap: adding MS-CHAPv2 MPPE keys
modcall[authenticate]: module "mschap" returns ok for request 0
modcall: group Auth-Type returns ok for request 0
Sending Access-Accept of id 22 to 192.168.0.41:1223
Framed-IP-Address := 192.168.245.5
MS-CHAP2-Success = 0x01533d46393446313137464542333632343643463238373944333141333831303930363945373838423743
MS-MPPE-Recv-Key = 0x76c343e75fddb7406359dd5e22670f5a
MS-MPPE-Send-Key = 0x17f258a825d308f092770fcd7963ce0b
MS-MPPE-Encryption-Policy = 0x00000001
MS-MPPE-Encryption-Types = 0x00000006
Finished request 0
Going to the next request
--- Walking the entire request list ---
Waking up in 6 seconds...
rad_recv: Accounting-Request packet from host 192.168.0.41:4682, id=92, length=137
NAS-Identifier = "stand02.xxxxx.xx"
NAS-Port = 0
NAS-Port-Type = Virtual
Service-Type = Framed-User
Framed-Protocol = PPP
Calling-Station-Id = "192.168.0.34"
User-Name = "user1"
Framed-IP-Address = 192.168.245.5
Acct-Status-Type = Start
Acct-Session-Id = "3329341-pptp0"
Acct-Multi-Session-Id = "3329341-vpn0"
Acct-Link-Count = 1
Acct-Authentic = RADIUS
modcall: entering group preacct for request 1
modcall[preacct]: module "preprocess" returns noop for request 1
rlm_realm: No '@' in User-Name = "user1", looking up realm NULL
rlm_realm: No such realm "NULL"
modcall[preacct]: module "suffix" returns noop for request 1
modcall[preacct]: module "files" returns noop for request 1
modcall: group preacct returns noop for request 1
modcall: entering group accounting for request 1
rlm_acct_unique: WARNING: Attribute NAS-Port-Id was not found in request, unique ID MAY be inconsistent
rlm_acct_unique: Hashing ',Client-IP-Address = 192.168.0.41,NAS-IP-Address = 192.168.0.41,Acct-Session-Id = "3329341-pptp0",User-Name = "user1"'
rlm_acct_unique: Acct-Unique-Session-ID = "2fe2f49fc24a63fe".
modcall[accounting]: module "acct_unique" returns ok for request 1
radius_xlat: '/var/log/freeradius//radacct/192.168.0.41/detail-20050412'
rlm_detail: /var/log/freeradius//radacct/%{Client-IP-Address}/detail-%Y%m%d expands to /var/log/freeradius//radacct/192.168.0.41/detail-20050412
modcall[accounting]: module "detail" returns ok for request 1
modcall[accounting]: module "unix" returns ok for request 1
radius_xlat: '/var/log/freeradius//radutmp'
radius_xlat: 'user1'
modcall[accounting]: module "radutmp" returns ok for request 1
modcall: group accounting returns ok for request 1
Sending Accounting-Response of id 92 to 192.168.0.41:4682
Finished request 1
Going to the next request
Cleaning up request 1 ID 92 with timestamp 425c0ebd
rl_next: returning NULL
Waking up in 6 seconds...
--- Walking the entire request list ---
Cleaning up request 0 ID 22 with timestamp 425c0ebd
Nothing to do. Sleeping until we see a request.------------------------------------
В качестве NAS использовал mpd-3.17
Проблема решена , в секции radiusd.conf отсутствавала запись для акаунтига sql .