[Serusers] running ser+freeradius with mysql - sr-users

9 Jun 2005


      Hi list,
I have a big problem with this. I have ser_head working with 
freeradius 0.9.1 and radiusclint-ng. Everything looks like 
working, ser start doesn´t give me any problem, I have 
create a database with ser_mysql.sh create, and I have 
configured a softphone X-Lite to check it all. But when it 
makes the register, radius doesn´t look like accessing to 
MySQL.
Please any help will be genial!!!
Gerard Sanz.
This is the ser response:
sip:~ # ser start 
Listening on 
             udp: sip.mars.sol [192.168.10.2]:5060 
             tcp: sip.mars.sol [192.168.10.2]:5060 
Aliases: 
             *: mars.sol:*
WARNING: no fork mode 
stateless - initializing 
 0(0) Maxfwd module- initializing 
textops - initializing 
 0(0) INFO: udp_init: SO_RCVBUF is initially 111616 
 0(0) INFO: udp_init: SO_RCVBUF is finally 223232 
 2(0) INFO: fifo process starting: 4630 
 2(4630) SER: open_uac_fifo: fifo server up 
at /tmp/ser_fifo... 
 0(4625) REGISTER sip:sip.mars.sol 
From:sip:laptop@sip.mars.sol To:sip:laptop@sip.mars.sol 
 0(4625) REGISTER: Authenticating user 
 0(4625) REGISTER: challenging user
This is the radius response:
rad_recv: Access-Request packet from host 127.0.0.1:32854, 
id=99, length=195 
        User-Name = "laptop@mars.sol" 
        Digest-Attributes = "\n\010laptop" 
        Digest-Attributes = "\001\nmars.sol" 
        Digest-Attributes = 
"\002*42adc7dfc79504c6f4aae461eb995f8889b49658" 
        Digest-Attributes = "\004\022sip:sip.mars.sol" 
        Digest-Attributes = "\003\nREGISTER" 
        Digest-Response = "4fcfd64bf5b67211395a084e4c978eba" 
        Service-Type = Sip-Session 
        Sip-Uri-User = "laptop" 
        NAS-IP-Address = 127.0.0.1 
        NAS-Port = 5060 
rlm_eap: EAP-Message not found 
    rlm_digest: Converting Digest-Attributes to something 
sane... 
        Digest-User-Name = "laptop" 
        Digest-Realm = "mars.sol" 
        Digest-Nonce = 
"42adc7dfc79504c6f4aae461eb995f8889b49658" 
        Digest-URI = "sip:sip.mars.sol" 
        Digest-Method = "REGISTER" 
rlm_digest: Adding Auth-Type = DIGEST 
rlm_sql (sql): Reserving sql socket id: 1 
rlm_sql_mysql: MYSQL check_error: 1146 received 
rlm_sql_getvpdata: database query error 
rlm_sql (sql): SQL query error; rejecting user 
rlm_sql (sql): Released sql socket id: 1
this is my sql.conf from the radius server:
# 
#  Configuration for the SQL module, when using MySQL. 
# 
#  The database schema is available at: 
# 
#   
src/radiusd/src/modules/rlm_sql/drivers/rlm_sql_mysql/db_mysql.sql 
# 
#  If you are using PostgreSQL, please use 
'postgresql.conf', instead. 
#  If you are using Oracle, please use 'oracle.conf', 
instead. 
#  If you are using MS-SQL, please use 'mssql.conf', 
instead. 
# 
#	$Id: sql.conf,v 1.26.4.1 2003/08/26 12:26:57 
phampson Exp $ 
# 
sql {
# Database type 
    # Current supported are: rlm_sql_mysql, 
rlm_sql_postgresql, 
    # rlm_sql_iodbc, rlm_sql_oracle, rlm_sql_unixodbc, 
rlm_sql_freetds 
    driver = "rlm_sql_mysql"
# Connect info 
    server = "localhost" 
    login = "ser" 
    password = "heslo" 
     
    # Database table configuration 
    radius_db = "ser"
and finally, this is my ser.cfg:
# ----------- global configuration parameters 
------------------------
debug=3         	# debug level (cmd line: 
-dddddddddd) 
fork=no 
log_stderror=yes	# (cmd line: -E)
/* Uncomment these lines to enter debugging mode  
#debug=7 
#fork=no 
#log_stderror=yes 
*/
listen=sip.mars.sol	# IP address(es) or hostnames where 
SER will listen for messages 
port=5060		# IP port where SER will listen for 
messages 
alias=mars.sol		# additional IP address(es) or 
hostnames to satisfy the condition "uri==myself"
check_via=no	# (cmd. line: -v) 
dns=no           # (cmd. line: -r) 
rev_dns=no      # (cmd. line: -R) 
children=4 
fifo="/tmp/ser_fifo" 
fifo_db_url="mysql://ser:heslo@localhost/ser" 
fifo_mode=0666
#
# ------------------ module loading 
----------------------------------
# Uncomment this if you want to use SQL database 
loadmodule "/usr/local/ser_radius/lib/ser/modules/mysql.so"
loadmodule "/usr/local/ser_radius/lib/ser/modules/sl.so" 
loadmodule "/usr/local/ser_radius/lib/ser/modules/tm.so" 
loadmodule "/usr/local/ser_radius/lib/ser/modules/rr.so" 
loadmodule "/usr/local/ser_radius/lib/ser/modules/maxfwd.so" 
loadmodule "/usr/local/ser_radius/lib/ser/modules/usrloc.so" 
loadmodule 
"/usr/local/ser_radius/lib/ser/modules/registrar.so" 
loadmodule 
"/usr/local/ser_radius/lib/ser/modules/textops.so" 
loadmodule "/usr/local/ser_radius/lib/ser/modules/uri.so"
# Uncomment this if you want digest authentication 
# mysql.so must be loaded ! 
loadmodule "/usr/local/ser_radius/lib/ser/modules/auth.so" 
loadmodule 
"/usr/local/ser_radius/lib/ser/modules/auth_db.so" 
loadmodule 
"/usr/local/ser_radius/lib/ser/modules/auth_radius.so" 
loadmodule "/usr/local/ser_radius/lib/ser/modules/xlog.so"
#loadmodule "/usr/local/ser_radius/lib/ser/modules/acc.so"
# ----------------- setting module-specific parameters 
---------------
# -- usrloc params --
# Uncomment this if you want to use SQL database  
# for persistent storage and comment the previous line 
modparam("usrloc", "db_mode", 2)
# -- auth params -- 
# Uncomment if you are using auth module 
# 
modparam("auth_radius", "radius_config", 
"/usr/local/etc/radiusclient-ng/radiusclient.conf")
# -- rr params -- 
# add value to ;lr param to make some broken UAs happy 
modparam("rr", "enable_full_lr", 1)
# -- acc params -- 
#modparam("acc", "log_level", 1) 
#modparam("acc", "radius_flag", 1)
# -------------------------  request routing logic 
-------------------
# main routing logic
route{
xlog("L_INFO","%rm %ru From:%fu To:%tu\n");
# initial sanity checks -- messages with 
    # max_forwards==0, or excessively long requests 
    if (!mf_process_maxfwd_header("10")) { 
    	sl_send_reply("483","Too Many Hops"); 
    	break; 
    }; 
    if (msg:len >=  max_len ) { 
    	sl_send_reply("513", "Message too big"); 
    	break; 
    }; 
     
    # loose-route processing (grant Route routing if 
route headers present) 
    if (loose_route()) 
    { 
    	xlog("L_INFO","     -->Request 
loose_routed\n"); 
    	append_hf("P-hint: loose_routed by 
biloxi-proxy \r\n"); 
    	t_relay(); 
    	break; 
    }; 
     
    # record route if its a session (INVITE method) 
    if (method=="INVITE") 
    { 
    	record_route(); 
    }; 
     
    #check if request-URI is an alias 
    lookup ("aliases"); 
     
    #if request URI is numeric and starting with 1 
forward to sip.mars.sol 
    if (uri=~"^sip:1[0-9]*") 
    { 
    	forward("sip.mars.sol"); 
    	break; 
    }; 
     
     
    # we record-route all messages -- to make sure that 
    # subsequent messages will go through our proxy; 
that's 
    # particularly good if upstream and downstream 
entities 
    # use different transport protocol 
    if (!method=="REGISTER") record_route();
# subsequent messages withing a dialog should take 
the 
    # path determined by record-routing 
    if (loose_route()) { 
    	# mark routing logic in request 
    	append_hf("P-hint: rr-enforced\r\n");  
    	route(1); 
    	break; 
    };
if (!uri==myself) { 
    	# mark routing logic in request 
    	append_hf("P-hint: outbound\r\n");  
    	route(1); 
    	break; 
    };
# if the request is for other domain use UsrLoc 
    # (in case, it does not work, use the following 
command 
    # with proper names and addresses in it) 
    if (uri==myself) {
if (method=="REGISTER") {
log(1, "REGISTER: Authenticating 
user\n");	 
    	 
# Uncomment this if you want to use digest authentication 
    		if 
(!radius_www_authorize("mars.sol")) { 
    			log(1, "REGISTER: 
challenging user\n"); 
    			www_challenge("mars.sol", 
"0"); 
    			break; 
    		};
save("location"); 
    		break; 
    	}; 
    	 
    	if (method=="INVITE") {
log(1, "INVITE\n"); 
                	setflag(1);  
    		/* set for accounting (the same 
value as in log_flag!) */ 
        	}; 
    	 
    	if (method=="MESSAGE") { 
                	log(1, "MESSAGE\n"); 
                	setflag(1);  
    		/* set for accounting (the same 
value as in log_flag!) */ 
        	}; 
    	 
    	if (method=="BYE" || method=="CANCEL") { 
                	log (1, "BYE or CANCEL\n"); 
                	setflag(1); 
        	}; 
    	 
    	lookup("aliases"); 
    	if (!uri==myself) { 
    		append_hf("P-hint: outbound 
alias\r\n");  
    		route(1); 
    		break; 
    	};
# native SIP destinations are handled using 
our USRLOC DB 
    	if (!lookup("location")) { 
    		sl_send_reply("404", "Not Found"); 
    		break; 
    	}; 
    }; 
    append_hf("P-hint: usrloc applied\r\n");  
    route(1); 
}
route[1]  
{ 
    # send it out now; use stateful forwarding as it 
works reliably 
    # even for UDP2TCP 
    if (!t_relay()) { 
    	sl_reply_error(); 
    }; 
}
Thank you very much. I hope somebody has had the same 
problem before and could solve it.
Gerard