Freeradius2 & Mysql

Thu Sep 9 08:47:27 CEST 2010

thanks for that, it's done the job.

Now my second problem is dialup admin. I can access it using http://(IP address)/dialup, however when I click on the left hand side menu options, for example accounting or statistic, I receive the following error "DEBUG(SQL,MYSQL DRIVER): Connect: User=(root),Password=(mypassword)

If i turn off sql debug, I receive a plain white page instead. The only options which I can select and view options are find user, show group, check server, help and about. Listed below are my config files. I am using php5

Admin.conf

#
# Main Configuration File
#
# it can be default or whatever language. Only greek are supported
# from non latin alphabet languages
# These attribute only apply for ldap not for sql
#
general_prefered_lang: en
general_prefered_lang_name: English
#
# The charset which will be added as a meta tag in all pages
#
general_charset: iso-8859-1
#
# Uncomment this if normal attributes (not the ;lang-xx ones) in ldap
# are utf8 encoded.
#
#general_decode_normal_attributes: yes
#
# The directory where dialupadmin is installed
#
general_base_dir: /usr/local/dialup_admin
#
# The base directory of the freeradius radius installation
#
general_radiusd_base_dir: /usr/sbin
general_domain: (company).net.au
#
# Set it to yes to use sessions and cache the various mappings
# You can also set use_session = 1 in config.php3 to also cache
# the admin.conf
#
# ---- IMPORTANT -- IMPORTANT -- IMPORTANT ----
#Remember to use the 'Clear Cache' page if you use sessions and do any changes
#in any of the configuration files.
#
general_use_session: no
#
# This is used by the failed logins page. It states the default back time
# in minutes.
#
general_most_recent_fl: 30

#
# Realm setup
#
# Set general_strip_realms to yes in order  to stip realms from usernames.
# By default realms are not striped
general_strip_realms: yes
#
# The delimiter used  in realms. Default is @
#
general_realm_delimiter: @
#
# The format of the realms. Can be either suffix (realm is after the username)
# or prefix (realm is before the username). Default is suffix
#
#
general_realm_format: suffix
#

#
# Determines if the administrator will be able to see and change the user password through
# the user edit page
general_show_user_password: yes

general_raddb_dir: %{general_radiusd_base_dir}/etc/raddb
general_ldap_attrmap: %{general_raddb_dir}/ldap.attrmap
# Need to fix admin.conf file parser
#general_clients_conf: %{general_raddb_dir}/clients.conf
general_clients_conf: etc/raddb/clients.conf
general_sql_attrmap: %{general_base_dir}/conf/sql.attrmap
general_accounting_attrs_file: %{general_base_dir}/conf/accounting.attrs
general_extra_ldap_attrmap: %{general_base_dir}/conf/extra.ldap-attrmap
general_username_mappings_file: %{general_base_dir}/conf/username.mappings
#
# it can be either ldap or sql
# This affects the user base not accounting. Accounting is always in sql
#
general_lib_type: sql
#
# Define which attributes will be visible in the user edit page
#
general_user_edit_attrs_file: %{general_base_dir}/conf/user_edit.attrs
#
# Used by the Accounting Report Generator
#
general_sql_attrs_file: %{general_base_dir}/conf/sql.attrs
#
# Set default values for various attributes
#
general_default_file: %{general_base_dir}/conf/default.vals
#general_ld_library_path: /usr/local/snmpd/lib
#
# can be 'snmp' (for snmpfinger) or empty to query the radacct table without first
# querying the nas
# This is used by the online users page
#
general_finger_type: snmp
#
# Defines the nas type. This is only used by snmpfinger
# cisco, usrhiper and lucent are supported for now
#
general_nas_type: cisco
general_snmpfinger_bin: %{general_base_dir}/bin/snmpfinger
#
# Used by the 'Disconnect User' button in the Clear Open Sessions page
# Uses the Cisco AAA Session MIB or a telnet session
#
general_sessionclear_bin: %{general_base_dir}/bin/clearsession
#
# Can be one of telnet or snmp
#
general_sessionclear_method: snmp
general_radclient_bin: %{general_radiusd_base_dir}/bin/radclient
#
# this information is used from the server check page
#
general_test_account_login: test
general_test_account_password: testpass
#
# These are used as default values for the user test page
#
general_radius_server: localhost
general_radius_server_port: 1812
#
# can be either pap or chap
#
general_radius_server_auth_proto: chap
#
# sorry, single valued for now. Should become something like
# password[server-name]: xxxxx
#
general_radius_server_secret: XXXXXX
general_auth_request_file: %{general_base_dir}/conf/auth.request
#
# can be one of crypt,md5,clear
#
general_encryption_method: clear
#
# can be either asc (older dates first) or desc (recent dates first)
# This is used in the user accounting and badusers pages
#
general_accounting_info_order: desc
#
# Use the totacct table in the user statistics page instead of the radacct
# table. That will make the page run quicker. totacct should have data for
# this to work :-)
#
general_stats_use_totacct: no
#
# If set to yes then we only allow each administrator to examine it's own entries
# in the badusers table
#
general_restrict_badusers_access: no
#
# If set to yes then we restrict access to the nas administration page only to those
# users which are allowed by their username mapping (nasadmin is set to yes)
#
general_restrict_nasadmin_access: no

INCLUDE: %{general_base_dir}/conf/naslist.conf

INCLUDE: %{general_base_dir}/conf/captions.conf

#
# The ldap server to connect to.
# Both ldap_server and ldap_write_server can be a space-separated
# list of ldap hostnames. In that case the library will try to connect
# to the servers in the order that they appear. If the first host is down
# ldap_connect will ask for the second ldap host and so on.
#
ldap_server: ldap.%{general_domain}
#
# There are many cases where we have a small write master and
# a lot of fast read only replicas. If that is the case uncomment
# ldap_write_server and point it to the write master. It will be
# used only when writing to the directory, not when reading
#
#ldap_write_server: master.%{general_domain}
ldap_base: dc=company,dc=com
ldap_binddn: cn=Directory Manager
ldap_bindpw: XXXXXXX
ldap_default_new_entry_suffix: ou=dialup,ou=guests,%{ldap_base}
ldap_default_dn: uid=default-dialup,%{ldap_base}
ldap_regular_profile_attr: dialupregularprofile
#
# If set to yes then the HTTP credentials (http authentication)
# will be used to bind to the ldap server instead of ldap_binddn
# and ldap_bindpw. That way multiple admins with different rights
# on the ldap database can connect through one dialup_admin interface.
# The ldap_binddn and ldap_bindpw are still needed to find the DN
# to bind with (http authentication will only provide us with a
# username). As a result the ldap_binddn should be able to do a search
# with a filter of (uid=<username>). Normally, the anonymous (empty DN)
# user can do that.
#ldap_use_http_credentials: yes
#
# If we are using http credentials we can map a specific username to the
# directory manager (which usually does not correspond to a specific username)
#
#ldap_directory_manager: cn=Directory Manager
#ldap_map_to_directory_manager: admin
#
# Uncomment to enable ldap debug
#
ldap_debug: true
#
# Allow for defining the ldap filter used when searching for a user
# Variables supported:
# %u: username
# %U: username provided though http authentication
# %mu: mappings for userdb
# %ma: mappings for accounting
# %mn: mappings for nasdb
# %mN: mappings for nas administration
#
# One use of this would be to restrict access to only the user's belonging to
# a specific administrator like this:
# ldap_filter: (&(uid=%u)(manager=uid=%U,ou=admins,o=company,c=com))
#
#ldap_filter: (uid=%u)
#
# If ldap_userdn is set then we use that for user dns, we don't perform an ldap
# search. This can be somewhat faster. The variables supported for ldap_filter
# are also supported here
#
#ldap_userdn: uid=%u,%{ldap_base}

#
# can be one of mysql,pg,oracle,sqlrelay where:
# mysq: MySQL database (port 3306)
# pg: PostgreSQL database (port 5432)
# oracle: Oracle database (port 1521)
# sqlrelay: SQL Relay
#
sql_type: mysql
sql_server: localhost
sql_port: 3306
sql_username: root
sql_password: (sqlpassword)
sql_database: radius
sql_accounting_table: radacct
sql_badusers_table: badusers
sql_check_table: radcheck
sql_reply_table: radreply
sql_user_info_table: userinfo
sql_groupcheck_table: radgroupcheck
sql_groupreply_table: radgroupreply
sql_usergroup_table: usergroup
sql_total_accounting_table: totacct
sql_nas_table: nas
#
# If set to true then we show all the available groups with the groups
# that the user is a member of highlighted in the user edit page.
# Otherwise we only show the groups he is a member of.
sql_show_all_groups: true
#
# This variable is used by the scripts in the bin folder
# It should contain the path to the sql binary used to run
# sql commands (mysql, psql, oracle and sqlrelay are only supported for now)
sql_command: /usr/bin/mysql
#sql_command: /usr/bin/psql
#sql_command: /usr/bin/sqlplus
#
# This variable is used by the scripts in the bin folder
# It should contain the snmp type and  path to the binary
# used to run snmp commands.
# (ucd = UCD-Snmp and net = Net-Snmp are only supported for now)
general_snmp_type: net
general_snmpwalk_command: /usr/local/bin/snmpwalk
general_snmpget_command: /usr/local/bin/snmpget
#
# Uncomment to enable sql debug
#
sql_debug: true
#
# If set to yes then the HTTP credentials (http authentication)
# will be used to connect to the sql server instead of sql_username
# and sql_password. That way multiple admins with different rights
# on the sql database can connect through one dialup_admin interface.
sql_use_http_credentials: no
#
# If set the query will be added to all of the queries on the accounting
# table
# Variables supported:
# %u: username
# %U: username provided though http authentication
# %mu: mappings for userdb
# %ma: mappings for accounting
# %mn: mappings for nasdb
# %mN: mappings for nas administration
#sql_accounting_extra_query: %ma

#
# true or false
#
sql_use_user_info_table: true
sql_use_operators: true
#
# Set this to the value of the default_user_profile in your
# sql.conf if that one is set. If it is not set leave blank
# or commented out
#sql_default_user_profile: DEFAULT
#
#
sql_password_attribute: User-Password
sql_date_format: Y-m-d
sql_full_date_format: Y-m-d H:i:s
#
# Used in the accounting report generator so that we
# don't return too many results
#
sql_row_limit: 40
#
# These options are used by the log_badlogins script and by the
# mysql driver
#
# Set the sql connect timeout (secs)
sql_connect_timeout: 3
# Give a space separated list of extra mysql servers to connect to when
# logging bad logins or adding users in the badusers table
#sql_extra_servers: sql2.company.com sql3.company.com

#
# Default values for the various user limits in case the counter module
# is used to impose such limits.
# The value should be the user limit in seconds or none for nothing
# Check out conf/sql.attrmap or extra.ldap-attrmap (depending on if you are
# using sql or ldap) for per user attributes. The mapping should be made to
# the attributes configured in the counter module. The attributes used by
# dialupadmin will always be the ones appearing in the attribute mapping files
# so you should make sure they are mapped to the correct attributes
#
#counter_default_daily: 14400
#counter_default_weekly: 72000
counter_default_daily: none
counter_default_weekly: none
counter_default_monthly: none
#
# Since calculating monthly usage can be quite expensive we make
# it configurable
# This is not needed if the monthly limit is not none
#counter_monthly_calculate_usage: true

I have inputted the following in httpd.conf

LoadModule php5_module modules/libphp5.so
#AddModule mod_php5.c - do I need to add this? currently commented out, because if it's uncommented and i restart httpd, I receive an error saying  "Apache 1.3 configuration directives found please read /usr/share/doc/httpd-2.2.3/migration.html"
AddType application/x-httpd-php .php
AddType application/x-httpd-php .php3

Let me know if you need more info.

Thanks,

Shawky

From: tim.sylvester at networkradius.com
To: freeradius-users at lists.freeradius.org
Subject: RE: Freeradius2 & Mysql
Date: Wed, 8 Sep 2010 22:14:30 -0700

 [sql]   expand: %{User-Name}
-> fredf

[sql] sql_set_user escaped user --> 'fredf'

rlm_sql (sql): Reserving sql socket id: 4

[sql]   expand: SELECT id, username, attribute, value,
op           FROM
radcheck           WHERE
username = '%{SQL-User-Name}'          
ORDER BY id -> SELECT id, username, attribute, value,
op           FROM
radcheck           WHERE
username = 'fredf'          
ORDER BY id

[sql] User found in radcheck table

[sql]   expand: SELECT id, username, attribute, value,
op           FROM
radreply           WHERE
username = '%{SQL-User-Name}'          
ORDER BY id -> SELECT id, username, attribute, value,
op           FROM
radreply           WHERE
username = 'fredf'          
ORDER BY id

[sql]   expand: SELECT
groupname           FROM
radusergroup           WHERE
username = '%{SQL-User-Name}'          
ORDER BY priority -> SELECT
groupname           FROM
radusergroup           WHERE
username = 'fredf'          
ORDER BY priority

[sql]   expand: SELECT id, groupname,
attribute,           Value,
op           FROM
radgroupcheck           WHERE
groupname =
'%{Sql-Group}'          
ORDER BY id -> SELECT id, groupname,
attribute,           Value,
op           FROM
radgroupcheck           WHERE
groupname =
'dynamic'           ORDER BY
id

rlm_sql_mysql: MYSQL check_error: 1146 received

rlm_sql_getvpdata: database query error

[sql] Error retrieving check pairs for group dynamic

[sql] Error processing groups; rejecting user

rlm_sql (sql): Released sql socket id: 4

++[sql] returns fail

<tim>

From
the MySQL documentation:

Error: 1146 SQLSTATE:
42S02 (ER_NO_SUCH_TABLE)
 Message: Table '%s.%s' doesn't exist 

Use the MySQL client with the same user that FreeRADIUS
uses to connect to MySQL. Then try the query that FR sent to MySQL:

SELECT id, groupname, attribute, Value, op  FROM
radgroupcheck WHERE groupname = 'dynamic' ORDER BY id;

You should get the same error – 1146 and message with
more information on the error.

Tim

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html 		 	   		  
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20100909/c3715345/attachment.html>