Freeradius won't start

[Grogorio]

Trying to set up freeradius2 on pfsense 2.2.5 but the service won't start.

If I try to connect to an internet address, upon filling in the captive portal form, I get error: 'Error sending request: No valid RADIUS responses received'

The mysql server is on another box on the lan. No problem connecting via putty, ssh, or http (phpMyAdmin). Only pfsense won't connect.

The database is set up correctly and works when hosted in the cloud (hostgator server).

Seems like pfsense can't connect to the mysql database for some reason. Here's part of the log:

radiusd[24144]: rim_sql (sql): Attempting to connect to MySQL server admin@192.168.225.2:radius
radiusd[24144]: rim_sql (sql): Attempting to connect to rim_sql_mysql #0
radiusd[24144]: rim_sql_mysql: Starting connect to MySQL server for #0
radiusd[24144]: rim_sql_mysql: Coudn't connect socket to MySQL server admin@192.168.225.2:radius
radiusd[24144]: rim_sql_mysql: Mysql error 'Can't connect to MySQL server on '192.168.225.2' (61)'
radiusd[24144]: rim_sql (sql): Failed to connect DB handle #0

etc etc

here's my xml dump for captive portal and freeradius:

Captive Portal:

 <captiveportal><guests>guests
			 <descr><localauth_priv><zoneid>2</zoneid>
			<interface>lan</interface>
			 <maxproc><maxprocperip>4</maxprocperip>
			 <timeout><idletimeout>30</idletimeout>
			 <freelogins_count><freelogins_resettimeout><auth_method>radius</auth_method>
			 <reauthenticateacct><httpsname><preauthurl><blockedmacsurl><bwdefaultdn><bwdefaultup><certref>563962283f7cb</certref>
			<radius_protocol>PAP</radius_protocol>
			 <redirurl><radiusip>192.168.225.1</radiusip>
			 <radiusip2><radiusip3><radiusip4><radiusport><radiusport2><radiusport3><radiusport4><radiusacctport><radiuskey>secretwordhere</radiuskey>
			 <radiuskey2><radiuskey3><radiuskey4><radiusvendor>default</radiusvendor>
			<radiussrcip_attribute>wan</radiussrcip_attribute>
			<radmac_format>default</radmac_format>
			 <radiusnasid><page><enable></enable></page></radiusnasid></radiuskey4></radiuskey3></radiuskey2></radiusacctport></radiusport4></radiusport3></radiusport2></radiusport></radiusip4></radiusip3></radiusip2></redirurl></bwdefaultup></bwdefaultdn></blockedmacsurl></preauthurl></httpsname></reauthenticateacct></freelogins_resettimeout></freelogins_count></timeout></maxproc></localauth_priv></descr></guests></captiveportal> 

freeRadius:

 <freeradiussqlconf><config><varsqlconfincludeenable>on</varsqlconfincludeenable>
				<varsqlconfenableauthorize>Enable</varsqlconfenableauthorize>
				<varsqlconfenableaccounting>Enable</varsqlconfenableaccounting>
				<varsqlconfenablesession>Enable</varsqlconfenablesession>
				<varsqlconfenablepostauth>Enable</varsqlconfenablepostauth>
				<varsqlconfdatabase>mysql</varsqlconfdatabase>
				<varsqlconfserver>192.168.225.2</varsqlconfserver>
				<varsqlconfport>3306</varsqlconfport>
				<varsqlconflogin>usernamehere</varsqlconflogin>
				<varsqlconfpassword>passwordhere</varsqlconfpassword>
				<varsqlconfradiusdb>radius</varsqlconfradiusdb>
				<varsqlconfaccttable1>radacct</varsqlconfaccttable1>
				<varsqlconfaccttable2>radacct</varsqlconfaccttable2>
				<varsqlconfpostauthtable>radpostauth</varsqlconfpostauthtable>
				<varsqlconfauthchecktable>radcheck</varsqlconfauthchecktable>
				<varsqlconfauthreplytable>radreply</varsqlconfauthreplytable>
				<varsqlconfgroupchecktable>radgroupcheck</varsqlconfgroupchecktable>
				<varsqlconfgroupreplytable>radgroupreply</varsqlconfgroupreplytable>
				<varsqlconfusergrouptable>radusergroup</varsqlconfusergrouptable>
				<varsqlconfreadgroups>yes</varsqlconfreadgroups>
				<varsqlconfdeletestalesessions>yes</varsqlconfdeletestalesessions>
				<varsqlconfsqltrace>no</varsqlconfsqltrace>
				<varsqlconfnumsqlsocks>5</varsqlconfnumsqlsocks>
				<varsqlconfconnectfailureretrydelay>60</varsqlconfconnectfailureretrydelay>
				<varsqlconflifetime>0</varsqlconflifetime>
				<varsqlconfmaxqueries>0</varsqlconfmaxqueries>
				<varsqlconfreadclients>yes</varsqlconfreadclients>
				<varsqlconfnastable>nas</varsqlconfnastable>
				<varsqlconf2failover>redundant</varsqlconf2failover>
				 <varsqlconf2includeenable><varsqlconf2enableauthorize>Disable</varsqlconf2enableauthorize>
				<varsqlconf2enableaccounting>Disable</varsqlconf2enableaccounting>
				<varsqlconf2enablesession>Disable</varsqlconf2enablesession>
				<varsqlconf2enablepostauth>Disable</varsqlconf2enablepostauth>
				<varsqlconf2database>mysql</varsqlconf2database>
				 <varsqlconf2server><varsqlconf2port><varsqlconf2login><varsqlconf2password><varsqlconf2radiusdb><varsqlconf2accttable1><varsqlconf2accttable2><varsqlconf2postauthtable><varsqlconf2authchecktable><varsqlconf2authreplytable><varsqlconf2groupchecktable><varsqlconf2groupreplytable><varsqlconf2usergrouptable><varsqlconf2readgroups>yes</varsqlconf2readgroups>
				<varsqlconf2deletestalesessions>yes</varsqlconf2deletestalesessions>
				<varsqlconf2sqltrace>no</varsqlconf2sqltrace>
				 <varsqlconf2numsqlsocks><varsqlconf2connectfailureretrydelay><varsqlconf2lifetime><varsqlconf2maxqueries><varsqlconf2readclients>yes</varsqlconf2readclients>
				 <varsqlconf2nastable></varsqlconf2nastable></varsqlconf2maxqueries></varsqlconf2lifetime></varsqlconf2connectfailureretrydelay></varsqlconf2numsqlsocks></varsqlconf2usergrouptable></varsqlconf2groupreplytable></varsqlconf2groupchecktable></varsqlconf2authreplytable></varsqlconf2authchecktable></varsqlconf2postauthtable></varsqlconf2accttable2></varsqlconf2accttable1></varsqlconf2radiusdb></varsqlconf2password></varsqlconf2login></varsqlconf2port></varsqlconf2server></varsqlconf2includeenable></config></freeradiussqlconf> 
		 <freeradiusclients><config><varclientip>192.168.225.1</varclientip>
				<varclientipversion>ipaddr</varclientipversion>
				<varclientshortname>pfsense</varclientshortname>
				<varclientsharedsecret>secretwordhere</varclientsharedsecret>
				<varclientproto>udp</varclientproto>
				<varclientnastype>other</varclientnastype>
				<varrequiremessageauthenticator>no</varrequiremessageauthenticator>
				<varclientmaxconnections>16</varclientmaxconnections>
				 <varclientlogininput><varclientpasswordinput></varclientpasswordinput></varclientlogininput></config></freeradiusclients> 
		 <freeradius><config><sortable><varusersusername>usernamehere</varusersusername>
				<varuserspassword>passwordhere</varuserspassword>
				<varuserspasswordencryption>Cleartext-Password</varuserspasswordencryption>
				 <varusersmotpenable><varusersmotpinitsecret><varusersmotppin><varusersmotpoffset><varuserssimultaneousconnect><varuserswisprredirectionurl><description><varusersframedipaddress><varusersframedipnetmask><varusersframedroute><varusersvlanid><varusersexpiration><varuserssessiontimeout><varuserslogintime><varusersamountoftime><varuserspointoftime>Daily</varuserspointoftime>
				 <varusersmaxtotaloctets><varusersmaxtotaloctetstimerange>daily</varusersmaxtotaloctetstimerange>
				 <varusersmaxbandwidthdown><varusersmaxbandwidthup><varusersacctinteriminterval><varuserstopadditionaloptions><varuserscheckitemsadditionaloptions><varusersreplyitemsadditionaloptions></varusersreplyitemsadditionaloptions></varuserscheckitemsadditionaloptions></varuserstopadditionaloptions></varusersacctinteriminterval></varusersmaxbandwidthup></varusersmaxbandwidthdown></varusersmaxtotaloctets></varusersamountoftime></varuserslogintime></varuserssessiontimeout></varusersexpiration></varusersvlanid></varusersframedroute></varusersframedipnetmask></varusersframedipaddress></description></varuserswisprredirectionurl></varuserssimultaneousconnect></varusersmotpoffset></varusersmotppin></varusersmotpinitsecret></varusersmotpenable></sortable></config></freeradius> 
		 <freeradiusinterfaces><config><varinterfaceip>192.168.225.1</varinterfaceip>
				<varinterfaceport>1812</varinterfaceport>
				<varinterfacetype>auth</varinterfacetype>
				<varinterfaceipversion>ipaddr</varinterfaceipversion></config></freeradiusinterfaces> 
		 <freeradiuseapconf><config><vareapconfdisableweakeaptypes><vareapconfdefaulteaptype>md5</vareapconfdefaulteaptype>
				<vareapconftimerexpire>60</vareapconftimerexpire>
				<vareapconfignoreunknowneaptypes>no</vareapconfignoreunknowneaptypes>
				<vareapconfciscoaccountingusernamebug>no</vareapconfciscoaccountingusernamebug>
				<vareapconfmaxsessions>4096</vareapconfmaxsessions>
				 <vareapconfchoosecertmanager><vareapconfprivatekeypassword>whatever</vareapconfprivatekeypassword>
				<ssl_ca_cert>none</ssl_ca_cert>
				<ssl_ca_crl>none</ssl_ca_crl>
				<ssl_server_cert>none</ssl_server_cert>
				<vareapconfincludelength>yes</vareapconfincludelength>
				<vareapconffragmentsize>1024</vareapconffragmentsize>
				 <vareapconfenablecheckcertissuer><vareapconfcountry><vareapconfstate><vareapconfcity><vareapconforganization><vareapconfemail><vareapconfcommonname><vareapconfenablecheckcertcn><vareapconfcacheenablecache>no</vareapconfcacheenablecache>
				<vareapconfcachelifetime>24</vareapconfcachelifetime>
				<vareapconfcachemaxentries>255</vareapconfcachemaxentries>
				<vareapconfocspenable>no</vareapconfocspenable>
				<vareapconfocspoverridecerturl>no</vareapconfocspoverridecerturl>
				<vareapconfocspurl>http://127.0.0.1/ocsp/</vareapconfocspurl>
				<vareapconfttlsdefaulteaptype>md5</vareapconfttlsdefaulteaptype>
				<vareapconfttlscopyrequesttotunnel>no</vareapconfttlscopyrequesttotunnel>
				<vareapconfttlsusetunneledreply>no</vareapconfttlsusetunneledreply>
				<vareapconfttlsincludelength>yes</vareapconfttlsincludelength>
				<vareapconfpeapdefaulteaptype>mschapv2</vareapconfpeapdefaulteaptype>
				<vareapconfpeapcopyrequesttotunnel>no</vareapconfpeapcopyrequesttotunnel>
				<vareapconfpeapusetunneledreply>no</vareapconfpeapusetunneledreply>
				<vareapconfpeapsohenable>Disable</vareapconfpeapsohenable></vareapconfenablecheckcertcn></vareapconfcommonname></vareapconfemail></vareapconforganization></vareapconfcity></vareapconfstate></vareapconfcountry></vareapconfenablecheckcertissuer></vareapconfchoosecertmanager></vareapconfdisableweakeaptypes></config></freeradiuseapconf> 
		 <package><name>freeradius2</name>
			<website>http://www.freeradius.org/</website>
			 <descr>Support: MySQL, PostgreSQL, LDAP, Kerberos.<br />
			FreeRADIUS and FreeRADIUS2 settings are not compatible so don't use them together or try to update.<br />
			On pfSense docs there is a how-to which could help you on porting users.]]></descr>
			<pkginfolink>https://doc.pfsense.org/index.php/FreeRADIUS_2.x_package</pkginfolink>
			<category>System</category>
			<version>1.6.17</version>
			<status>RC1</status>
			<required_version>2.2</required_version>
			<maintainer>nachtfalkeaw@web.de</maintainer>
			<depends_on_package_pbi>freeradius-2.2.6_3-i386.pbi</depends_on_package_pbi>
			<config_file>https://packages.pfsense.org/packages/config/freeradius2/freeradius.xml</config_file>
			<configurationfile>freeradius.xml</configurationfile>
			<after_install_info>Please visit Services: FreeRADIUS.</after_install_info>
			<port_category>net</port_category>
			<run_depends>sbin/radiusd:net/freeradius2 bin/bash:shells/bash</run_depends>
			 <build_pbi><ports_before>security/krb5</ports_before>
				<port>net/freeradius2</port>
				<ports_after>shells/bash</ports_after></build_pbi> 
			<build_options>freeradius_SET_FORCE=KERBEROS MYSQL PGSQL PERL PYTHON LDAP SSL_PORT</build_options>
			<depends_on_package_base_url>https://files.pfsense.org/packages/10/All/</depends_on_package_base_url></package> 

<menu>
			<name>FreeRADIUS</name>
			<tooltiptext>Modify FreeRADIUS users, clients, and settings.</tooltiptext>
			Services
			<url>/pkg.php?xml=freeradius.xml</url>
		</menu>

		 <tab><text>Users</text>
			<url>/pkg.php?xml=freeradius.xml</url>
			 <active></active></tab> 
		 <service><name>radiusd</name>
			<rcfile>radiusd.sh</rcfile>
			<executable>radiusd</executable></service> 

can anyone spot anything wrong with all that? I'm stumped….

[doktornotor]

No problem connecting via putty, ssh, or http (phpMyAdmin). Only pfsense won't connect.

And, how's ANY of this relevant to the inability to connect to MySQL database?!

[Grogorio]

simply as background information to show that the mysql database exists, and that it is possible to connect with the appropriate credentials.

I thought (perhaps naively) that this would minimise unnecessary lines of investigation  :o

[doktornotor]

None of that shows that it's possible to connect to the database via TCP remotely. Seriously, you need to check that network connections are allowed in my.cnf, it's NOT the case by default.

http://www.cyberciti.biz/tips/how-do-i-enable-remote-access-to-mysql-database-server.html

[Grogorio]

good pointer drnotor, thank you it works now.

For those who are as network-challenged as I am, in plain noob parlance the problem lay not with pfSense but rather with the access permissions on the mysql server.

I will summarize the steps in the link provided by doktornotor in case the page disappears in the future:

1. Open a terminal in your mysql server box
2.  Edit the MySQL server configuration file my.cnf (located at /etc/mysql/my.cnf for debian but can vary with other distros)
3. Make sure line skip-networking is commented (or remove line) and add the following line

bind-address=YOUR-SERVER-IP

OR you may find a comment that instead of skip-networking the default is to listen only on localhost. In this case just change the bind address
4. Save and close the file and restart the service with /etc/init.d/mysql restart (debian)
5. Login to mysql and grant access to remote IP address

$ mysql -u root -p mysql
mysql> update db set Host='202.54.10.20' where Db='webdb';
mysql> update user set Host='202.54.10.20' where user='webadmin';

6. Logout of mysql
7. Open port 3306 (several different methods but I used allow remote connection from your lan subnet 192.168.225.%/24) where % is a wildcard

/sbin/iptables -A INPUT -i eth0 -s 192.168.1.%/24 -p tcp --destination-port 3306 -j ACCEPT

[doktornotor]

Thanks for reporting back.