Re: SQL Database

Hi.

The error you received indicates your radius server cannot connect to any d=
atabase.
You can use this database for your setup:
http://www.yazzy.org/configs/linux/f...ius/radius.sql

Cheers,
Marcin Jessa

On Fri, 20 May 2005 16:40:28 -0400
Software Development Group <software@hollyworldgroup.com> wrote:

> Hi,
>=20
> I want to have FreeRadius work with an SQL for user validation, time=20
> discounting... I've configured ./configure with experimental-module, make=
=20
> and make install.
>=20
> I've entered an sql entry under accounting { } section of radiusd.conf,=20
> I=B4ve created a file called sqlcounter.conf with:
>=20
> sqlcounter noresetcounter {...}
> sqlcounter dailycounter {...}
> sqlcounter monthlycounter {...}
>=20
> I've included an $INCLUDE ${confdir}/sqlcounter.conf line on the modules =
{=20
> } section of radiusd.conf.
>=20
> I've included the sqlcounter names under autorize { } section of radiusd.=
conf.
>=20
> I run radiusd -X and I am getting the followin error log:
>=20
> --------
> root@ubuntu:/usr/local/etc/raddb # radiusd -X
> Starting - reading configuration files ...
> reread_config: reading radiusd.conf
> Config: including file: /usr/local/etc/raddb/clients.conf
> Config: including file: /usr/local/etc/raddb/sql.conf
> Config: including file: /usr/local/etc/raddb/sqlcounter.conf
> Config: including file: /usr/local/etc/raddb/sqlcounter.conf
> main: prefix =3D "/usr/local"
> main: localstatedir =3D "/usr/local/var"
> main: logdir =3D "/usr/local/var/log/radius"
> main: libdir =3D "/usr/local/lib"
> main: radacctdir =3D "/usr/local/var/log/radius/radacct"
> main: hostname_lookups =3D no
> main: max_request_time =3D 30
> main: cleanup_delay =3D 5
> main: max_requests =3D 1024
> main: delete_blocked_requests =3D 0
> main: port =3D 0
> main: allow_core_dumps =3D no
> main: log_stripped_names =3D no
> main: log_file =3D "/usr/local/var/log/radius/radius.log"
> main: log_auth =3D yes
> main: log_auth_badpass =3D yes
> main: log_auth_goodpass =3D yes
> main: pidfile =3D "/usr/local/var/run/radiusd/radiusd.pid"
> main: user =3D "root"
> main: group =3D "root"
> main: usercollide =3D no
> main: lower_user =3D "no"
> main: lower_pass =3D "no"
> main: nospace_user =3D "no"
> main: nospace_pass =3D "no"
> main: checkrad =3D "/usr/local/sbin/checkrad"
> main: proxy_requests =3D no
> security: max_attributes =3D 200
> security: reject_delay =3D 1
> security: status_server =3D no
> main: debug_level =3D 0
> read_config_files: reading dictionary
> read_config_files: reading naslist
> Using deprecated naslist file. Support for this will go away soon.
> read_config_files: reading clients
> read_config_files: reading realms
> radiusd: entering modules setup
> Module: Library search path is /usr/local/lib
> Module: Loaded expr
> Module: Instantiated expr (expr)
> Module: Loaded SQL Counter
> sqlcounter: counter-name =3D "Max-All-Session-Time"
> sqlcounter: check-name =3D "Max-All-Session"
> sqlcounter: key =3D "User-Name"
> sqlcounter: sqlmod-inst =3D "sql"
> sqlcounter: query =3D "SELECT SUM(AcctSessionTime) FROM radacct WHERE=20
> UserName=3D'%{%k}'"
> sqlcounter: reset =3D "never"
> rlm_sqlcounter: Counter attribute Max-All-Session-Time is number 1671
> rlm_sqlcounter: Check attribute Max-All-Session is number 1672
> rlm_sqlcounter: Current Time: 1116621277 [2005-05-20 16:34:37], Next rese=
t=20
> 0 [2005-05-20 16:00:00]
> rlm_sqlcounter: Current Time: 1116621277 [2005-05-20 16:34:37], Prev rese=
t=20
> 0 [2005-05-20 16:00:00]
> Module: Instantiated sqlcounter (noresetcounter)
> sqlcounter: counter-name =3D "Daily-Session-Time"
> sqlcounter: check-name =3D "Max-Daily-Session"
> sqlcounter: key =3D "User-Name"
> sqlcounter: sqlmod-inst =3D "sql"
> sqlcounter: query =3D "SELECT SUM(AcctSessionTime - GREATEST((%b -=20
> UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName=3D'%{%k}'=
AND=20
> UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
> sqlcounter: reset =3D "daily"
> rlm_sqlcounter: Counter attribute Daily-Session-Time is number 1673
> rlm_sqlcounter: Check attribute Max-Daily-Session is number 1674
> rlm_sqlcounter: Current Time: 1116621277 [2005-05-20 16:34:37], Next rese=
t=20
> 1116648000 [2005-05-21 00:00:00]
> rlm_sqlcounter: Current Time: 1116621277 [2005-05-20 16:34:37], Prev rese=
t=20
> 1116561600 [2005-05-20 00:00:00]
> Module: Instantiated sqlcounter (dailycounter)
> sqlcounter: counter-name =3D "Weekly-Session-Time"
> sqlcounter: check-name =3D "Max-Weekly-Session"
> sqlcounter: key =3D "User-Name"
> sqlcounter: sqlmod-inst =3D "sql"
> sqlcounter: query =3D "SELECT SUM(AcctSessionTime - GREATEST((%b -=20
> UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName=3D'%{%k}'=
AND=20
> UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
> sqlcounter: reset =3D "weekly"
> rlm_sqlcounter: Counter attribute Weekly-Session-Time is number 1675
> rlm_sqlcounter: Check attribute Max-Weekly-Session is number 1676
> rlm_sqlcounter: Current Time: 1116621277 [2005-05-20 16:34:37], Next rese=
t=20
> 1116734400 [2005-05-22 00:00:00]
> rlm_sqlcounter: Current Time: 1116621277 [2005-05-20 16:34:37], Prev rese=
t=20
> 1116388800 [2005-05-18 00:00:00]
> Module: Instantiated sqlcounter (weeklycounter)
> sqlcounter: counter-name =3D "Monthly-Session-Time"
> sqlcounter: check-name =3D "Max-Monthly-Session"
> sqlcounter: key =3D "User-Name"
> sqlcounter: sqlmod-inst =3D "sql"
> sqlcounter: query =3D "SELECT SUM(AcctSessionTime - GREATEST((%b -=20
> UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName=3D'%{%k}'=
AND=20
> UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
> sqlcounter: reset =3D "monthly"
> rlm_sqlcounter: Counter attribute Monthly-Session-Time is number 1677
> rlm_sqlcounter: Check attribute Max-Monthly-Session is number 1678
> rlm_sqlcounter: Current Time: 1116621277 [2005-05-20 16:34:37], Next rese=
t=20
> 1117598400 [2005-06-01 00:00:00]
> rlm_sqlcounter: Current Time: 1116621277 [2005-05-20 16:34:37], Prev rese=
t=20
> 1114920000 [2005-05-01 00:00:00]
> Module: Instantiated sqlcounter (monthlycounter)
> Module: Loaded CHAP
> Module: Instantiated chap (chap)
> Module: Loaded MS-CHAP
> mschap: use_mppe =3D no
> mschap: require_encryption =3D no
> mschap: require_strong =3D no
> mschap: with_ntdomain_hack =3D no
> mschap: passwd =3D "(null)"
> mschap: authtype =3D "MS-CHAP"
> mschap: ntlm_auth =3D "(null)"
> Module: Instantiated mschap (mschap)
> Module: Loaded preprocess
> preprocess: huntgroups =3D "/usr/local/etc/raddb/huntgroups"
> preprocess: hints =3D "/usr/local/etc/raddb/hints"
> preprocess: with_ascend_hack =3D no
> preprocess: ascend_channels_per_line =3D 23
> preprocess: with_ntdomain_hack =3D no
> preprocess: with_specialix_jetstream_hack =3D no
> preprocess: with_cisco_vsa_hack =3D no
> Module: Instantiated preprocess (preprocess)
> Module: Loaded realm
> realm: format =3D "suffix"
> realm: delimiter =3D "@"
> realm: ignore_default =3D no
> realm: ignore_null =3D yes
> Module: Instantiated realm (suffix)
> Module: Loaded SQL
> sql: driver =3D "rlm_sql_mysql"
> sql: server =3D "localhost"
> sql: port =3D ""
> sql: login =3D "root"
> sql: password =3D "rootpass"
> sql: radius_db =3D "radius"
> sql: acct_table =3D "radacct"
> sql: acct_table2 =3D "radacct"
> sql: authcheck_table =3D "radcheck"
> sql: authreply_table =3D "radreply"
> sql: groupcheck_table =3D "radgroupcheck"
> sql: groupreply_table =3D "radgroupreply"
> sql: usergroup_table =3D "usergroup"
> sql: nas_table =3D "nas"
> sql: dict_table =3D "dictionary"
> sql: sqltrace =3D no
> sql: sqltracefile =3D "/usr/local/var/log/radius/sqltrace.sql"
> sql: readclients =3D no
> sql: deletestalesessions =3D yes
> sql: num_sql_socks =3D 5
> sql: sql_user_name =3D "%{User-Name}"
> sql: default_user_profile =3D ""
> sql: query_on_not_found =3D no
> sql: authorize_check_query =3D "SELECT id,UserName,Attribute,Value,op F=
ROM=20
> radcheck WHERE Username =3D '%{SQL-User-Name}' ORDER BY id"
> sql: authorize_reply_query =3D "SELECT id,UserName,Attribute,Value,op F=
ROM=20
> radreply WHERE Username =3D '%{SQL-User-Name}' ORDER BY id"
> sql: authorize_group_check_query =3D "SELECT=20
> radgroupcheck.id,radgroupcheck.GroupName,radgroupc heck.Attribute,radgroup=
check.Value,radgroupcheck.op=20
> FROM radgroupcheck,usergroup WHERE usergroup.Username =3D '%{SQL-User-Nam=
e}'=20
> AND usergroup.GroupName =3D radgroupcheck.GroupName ORDER BY radgroupchec=
k.id"
> sql: authorize_group_reply_query =3D "SELECT=20
> radgroupreply.id,radgroupreply.GroupName,radgroupr eply.Attribute,radgroup=
reply.Value,radgroupreply.op=20
> FROM radgroupreply,usergroup WHERE usergroup.Username =3D '%{SQL-User-Nam=
e}'=20
> AND usergroup.GroupName =3D radgroupreply.GroupName ORDER BY radgrouprepl=
y.id"
> sql: accounting_onoff_query =3D "UPDATE radacct SET AcctStopTime=3D'%S'=
,=20
> AcctSessionTime=3Dunix_timestamp('%S') - unix_timestamp(AcctStartTime),=20
> AcctTerminateCause=3D'%{Acct-Terminate-Cause}', AcctStopDelay =3D=20
> '%{Acct-Delay-Time}' WHERE AcctSessionTime=3D0 AND AcctStopTime=3D0 AND=20
> NASIPAddress=3D '%{NAS-IP-Address}' AND AcctStartTime <=3D '%S'"
> sql: accounting_update_query =3D "UPDATE radacct ? SET FramedIPAddress =
=3D=20
> '%{Framed-IP-Address}', ? AcctSessionTime =3D '%{Acct-Session-Time}', ?=20
> AcctInputOctets =3D '%{Acct-Input-Octets}', ? AcctOutputOctets =3D=20
> '%{Acct-Output-Octets}' ? WHERE AcctSessionId =3D '%{Acct-Session-Id}' ? =
AND=20
> UserName =3D '%{SQL-User-Name}' ? AND NASIPAddress=3D '%{NAS-IP-Address}'"
> sql: accounting_update_query_alt =3D "INSERT into radacct (AcctSessionI=
d,=20
> AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType,=20
> AcctStartTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start,=20
> AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId,=20
> ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay)=20
> values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',=20
> '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}',=20
> '%{NAS-Port-Type}', DATE_SUB('%S',INTERVAL (%{Acct-Session-Time:-0} +=20
> %{Acct-Delay-Time:-0}) SECOND), '%{Acct-Session-Time}',=20
> '%{Acct-Authentic}', '', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}',=
=20
> '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}',=20
> '%{Framed-Protocol}', '%{Framed-IP-Address}', '0')"
> sql: accounting_start_query =3D "INSERT into radacct (AcctSessionId,=20
> AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType,=20
> AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic,=20
> ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets,=20
> CalledStationId, CallingStationId, AcctTerminateCause, ServiceType,=20
> FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay)=20
> values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',=20
> '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}',=20
> '%{NAS-Port-Type}', '%S', '0', '0', '%{Acct-Authentic}', '%{Connect-Info}=
',=20
> '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '',=20
> '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}',=20
> '%{Acct-Delay-Time}', '0')"
> sql: accounting_start_query_alt =3D "UPDATE radacct SET AcctStartTime =
=3D=20
> '%S', AcctStartDelay =3D '%{Acct-Delay-Time}', ConnectInfo_start =3D=20
> '%{Connect-Info}' WHERE AcctSessionId =3D '%{Acct-Session-Id}' AND UserNa=
me =3D=20
> '%{SQL-User-Name}' AND NASIPAddress =3D '%{NAS-IP-Address}'"
> sql: accounting_stop_query =3D "UPDATE radacct SET AcctStopTime =3D '%S=
',=20
> AcctSessionTime =3D '%{Acct-Session-Time}', AcctInputOctets =3D=20
> '%{Acct-Input-Octets}', AcctOutputOctets =3D '%{Acct-Output-Octets}',=20
> AcctTerminateCause =3D '%{Acct-Terminate-Cause}', AcctStopDelay =3D=20
> '%{Acct-Delay-Time}', ConnectInfo_stop =3D '%{Connect-Info}' WHERE=20
> AcctSessionId =3D '%{Acct-Session-Id}' AND UserName =3D '%{SQL-User-Name}=
' AND=20
> NASIPAddress =3D '%{NAS-IP-Address}'"
> sql: accounting_stop_query_alt =3D "INSERT into radacct (AcctSessionId,=
=20
> AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType,=20
> AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic,=20
> ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets,=20
> CalledStationId, CallingStationId, AcctTerminateCause, ServiceType,=20
> FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay)=20
> values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',=20
> '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}',=20
> '%{NAS-Port-Type}', DATE_SUB('%S', INTERVAL (%{Acct-Session-Time:-0} +=20
> %{Acct-Delay-Time:-0}) SECOND), '%S', '%{Acct-Session-Time}',=20
> '%{Acct-Authentic}', '', '%{Connect-Info}', '%{Acct-Input-Octets}',=20
> '%{Acct-Output-Octets}', '%{Called-Station-Id}', '%{Calling-Station-Id}',=
=20
> '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}',=20
> '%{Framed-IP-Address}', '0', '%{Acct-Delay-Time}')"
> sql: group_membership_query =3D "SELECT GroupName FROM usergroup WHERE=
=20
> UserName=3D'%{SQL-User-Name}'"
> sql: connect_failure_retry_delay =3D 60
> sql: simul_count_query =3D ""
> sql: simul_verify_query =3D "SELECT RadAcctId, AcctSessionId, UserName,=
=20
> NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtoco=
l=20
> FROM radacct WHERE UserName=3D'%{SQL-User-Name}' AND AcctStopTime =3D 0"
> sql: postauth_table =3D "radpostauth"
> sql: postauth_query =3D "INSERT into radpostauth (id, user, pass, reply=
,=20
> date) values ('', '%{User-Name}', '%{User-Password:-Chap-Password}',=20
> '%{reply:Packet-Type}', NOW())"
> sql: safe-characters =3D=20
> "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUV WXYZ0123456789.-_: /"
> Violaci=F3n de segmento
> root@ubuntu:/usr/local/etc/raddb #
> --------
>=20
> Would anyone have an empty example of the database that I can just use to=
=20
> start up and test this? (I am not sure I can create an SQL database=20
> structure from scratch).
>=20
> Thanks.
>=20
> Max=20

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html