Problems with SER, MySQL & FreeRADIUS

8 Sep 2006


      Hello!
I'm having some problems when wanting the database of SER to be the one
FreeRADIUS will be checking on, instead of the radius database.
Like that I changed the file /etc/raddb/sql.conf
At the end is the output of the problems.
Hope somebody can help,
Thanks in advance
Roberto

#----------------------------------------------------------------------------------------------------------------------------------------------#
#SQL.conf

sql {
    driver = "rlm_sql_mysql"

    server = "localhost"
    login = "root"
    password = "**********"

    # Database table configuration
    radius_db = "ser"

    # Allow for storing data after authentication
          postauth_table = "authenticate"
          authcheck_table = "subscriber"
          authreply_table = "subscriber2"
###    postauth_table = "radpostauth"
###    authcheck_table = "radcheck"
###    authreply_table = "radreply"

###    groupcheck_table = "radgroupcheck"
###    groupreply_table = "radgroupreply"
###    usergroup_table = "usergroup"
    # Table to keep radius client info
###    nas_table = "nas"

authorize_check_query = "SELECT phplib_id, username, password\
         FROM ${authcheck_table} \
         WHERE LOWER(username) = LOWER('%{SQL-User-Name}') \
         ORDER BY phplib_id"
authorize_reply_query = "SELECT phplib_id, username, password\
         FROM ${authreply_table} \
         WHERE LOWER(username) = LOWER('%{SQL-User-Name}') \
         ORDER BY phplib_id"

authorize_check_query = "SELECT phplib_id, username, password\
          FROM ${authcheck_table} \
          WHERE username = '%{SQL-User-Name}' \
          ORDER BY phplib_id"
authorize_reply_query = "SELECT phplib_id, username, password\
          FROM ${authreply_table} \
          WHERE username = '%{SQL-User-Name}' \
          ORDER BY phplib_id"

postauth_query = "INSERT into ${postauth_table} (phplib_id, user, pass,
reply, date) values ('', '%{User-Name}',
'%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())"

#SQL.conf
#----------------------------------------------------------------------------------------------------------------------------------------------#


I thought that i neede to create some more tables so here they are
Subscriber2 will act like- authcheck_table
Authenticate will actlike - postauth_table

#----------------------------------------------------------------------------------------------------------------------------------------------#
#Tables created in databaseSER

CREATE TABLE authenticate (
  phplib_id varchar(32) not null default '',
  user varchar(64) not null default '',
  pass varchar(25) not null default '',
  reply varchar(25) not null default '',
  date datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY phplib_id(phplib_id));

table subsciber had exactly the same structure of subscriber2

CREATE TABLE subscriber2 (
  phplib_id varchar(32) NOT NULL default '',
  $USERCOL varchar(64) NOT NULL default '',
  domain varchar(128) NOT NULL default '',
  password varchar(25) NOT NULL default '',
  first_name varchar(25) NOT NULL default '',
  last_name varchar(45) NOT NULL default '',
  phone varchar(15) NOT NULL default '',
  email_address varchar(50) NOT NULL default '',
  datetime_created datetime NOT NULL default '0000-00-00 00:00:00',
  datetime_modified datetime NOT NULL default '0000-00-00 00:00:00',
  confirmation varchar(64) NOT NULL default '',
  flag char(1) NOT NULL default 'o',
  sendnotification varchar(50) NOT NULL default '',
  greeting varchar(50) NOT NULL default '',
  ha1 varchar(128) NOT NULL default '',
  ha1b varchar(128) NOT NULL default '',
  allow_find char(1) NOT NULL default '0',
  timezone varchar(128) default NULL,
  rpid varchar(128) default NULL,
  domn int(10) default NULL,
  uuid varchar(64) default NULL,
  UNIQUE KEY phplib_id (phplib_id),
  PRIMARY KEY ($USERCOL, domain),
  KEY user_2 ($USERCOL)
) ;
#Tables created in databaseSER
#----------------------------------------------------------------------------------------------------------------------------------------------#


Output Problems

reread_config:  reading radiusd.conf
Config:   including file: /etc/raddb/proxy.conf
Config:   including file: /etc/raddb/clients.conf
Config:   including file: /etc/raddb/snmp.conf
Config:   including file: /etc/raddb/eap.conf
Config:   including file: /etc/raddb/sql.conf
/etc/raddb/sql.conf[243]: Line is not in 'attribute = value' format
Errors reading radiusd.conf

Problems with SER, MySQL & FreeRADIUS

Roberto Lopes