Netgate SG-1000 microFirewall

Author Topic: [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step  (Read 27580 times)

0 Members and 1 Guest are viewing this topic.

Offline nhimcoi30889

  • Newbie
  • *
  • Posts: 23
  • Karma: +0/-0
    • View Profile
Hi Deajan,
I can to create account on Radius with format email address ?
For example : test@abc.com

Offline deajan

  • Full Member
  • ***
  • Posts: 179
  • Karma: +26/-0
    • View Profile
@nhimcoi30889 Sorry but your sentence makes no sense to me. If your question was if radius supports emails as usernames, the answer is yes.
NetPOWER.fr - some opensource stuff for IT people

Offline nhimcoi30889

  • Newbie
  • *
  • Posts: 23
  • Karma: +0/-0
    • View Profile
HI deajan,
today i just upgrade from 2.2.6 to 2.3.1 Release
After upgraded, i start mysql againt but show error :
Code: [Select]
[2.3.1-RELEASE][admin@pfSense.it.mt]/root: service mysql-server.sh start
Starting mysql.
Bad -c option
/usr/local/etc/rc.d/mysql-server.sh: WARNING: failed to start mysql
Can u help me?

Offline deajan

  • Full Member
  • ***
  • Posts: 179
  • Karma: +26/-0
    • View Profile
Upgrades in 2.2.X are destructive for MySQL installation because they basically reinstalls a full OS.
Only 2.3.X upgrade series won't destroy anything because they work fine grained.

In order to get things working again, you have to uninstall and reinstall mysql. Keep in mind that directions to install  mysql for 2.3.x aren't the same as for 2.2.x.
NetPOWER.fr - some opensource stuff for IT people

Offline nhimcoi30889

  • Newbie
  • *
  • Posts: 23
  • Karma: +0/-0
    • View Profile
Hi Deajan,
I tried remove old mysql and reinstall new version but problem the same
Can you give solution ?

Offline nhimcoi30889

  • Newbie
  • *
  • Posts: 23
  • Karma: +0/-0
    • View Profile
Hi deajan,
can you help me ?

Offline deajan

  • Full Member
  • ***
  • Posts: 179
  • Karma: +26/-0
    • View Profile
How am I supposed to know what happens with your setup without even knowing what you did to uninstall / reinstall mysql ?
NetPOWER.fr - some opensource stuff for IT people

Offline nhimcoi30889

  • Newbie
  • *
  • Posts: 23
  • Karma: +0/-0
    • View Profile
Hi Deajan,
Do you try with pfsense 2.3.2 ?
I tried , the first it working normally
But after reboot mysql not working, and FreeRadius2 dont working
This is log when i start FreeRadius2
Code: [Select]
Aug 10 17:14:04 radiusd 57335 rlm_sql (sql): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
Aug 10 17:14:04 radiusd 57335 rlm_sql (sql): Attempting to connect to radius@127.0.0.1:3306/radius
Aug 10 17:14:04 radiusd 57335 rlm_sql (sql): Attempting to connect rlm_sql_mysql #0
Aug 10 17:14:04 radiusd 57335 rlm_sql_mysql: Starting connect to MySQL server for #0
Aug 10 17:15:19 radiusd 57335 rlm_sql_mysql: Couldn't connect socket to MySQL server radius@127.0.0.1:radius
Aug 10 17:15:19 radiusd 57335 rlm_sql_mysql: Mysql error 'Can't connect to MySQL server on '127.0.0.1' (60)'
Aug 10 17:15:19 radiusd 57335 rlm_sql (sql): Failed to connect DB handle #0
Aug 10 17:15:19 radiusd 57335 rlm_sql (sql): There are no DB handles to use! skipped 5, tried to connect 0
Aug 10 17:15:19 radiusd 57335 Failed to load clients from SQL.
Aug 10 17:15:19 radiusd 57335 rlm_sql (sql): Closing sqlsocket 4
Aug 10 17:15:19 radiusd 57335 rlm_sql (sql): Closing sqlsocket 3
Aug 10 17:15:19 radiusd 57335 rlm_sql (sql): Closing sqlsocket 2
Aug 10 17:15:19 radiusd 57335 rlm_sql (sql): Closing sqlsocket 1
Aug 10 17:15:19 radiusd 57335 rlm_sql (sql): Closing sqlsocket 0
Aug 10 17:15:19 radiusd 57335 /usr/local/etc/raddb/sql.conf[2]: Instantiation failed for module "sql"
Aug 10 17:15:19 radiusd 57335 /usr/local/etc/raddb/sites-enabled/default[185]: Failed to find "sql" in the "modules" section.
Aug 10 17:15:19 radiusd 57335 /usr/local/etc/raddb/sites-enabled/default[185]: Failed to parse "sql" entry.
Aug 10 17:15:19 radiusd 57335 /usr/local/etc/raddb/sites-enabled/default[69]: Errors parsing authorize section.
Aug 10 17:15:19 radiusd 57335 Failed to load virtual server <default>

This is error when connect mysql
Code: [Select]
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
I tried add manual file my.cnf to /usr/local/etc/mysql/my.cnf but dont working
Can you give some recomment ?
Tks bro

Offline caraffandee

  • Newbie
  • *
  • Posts: 3
  • Karma: +0/-0
    • View Profile
Hi Deajan.
Firstly a big thank for this great howto. Detailed and comprehensive.
Now, I apologize, I'm a newbee about pfSense (although an experienced Linux/PHP/DB/ecc. programmer), so my question might be trivial.
The network I'm trying to setup with your captive portal feature has a router (Zyxel) with REDIUS support. The router is also configured to act as DHCP server for my local network. Actually my pfSense machine/installation will be used ONLY for RADIUS/CaptivePortal authentication. In other words, my pfSense installation is configured in "appliance" mode, i.e. with the only one eth interface (WAN) enabled. I've configured the router to assign a static IP to my pfSense server.
The point now is this. When I create a CP zone in the WebConfigurator, at the bottom of the page I read the following hint:
Quote
Don't forget to enable the DHCP server on the captive portal interface! Make sure that the default/maximum DHCP lease time is higher than the hard timeout entered on this page. Also, the DNS Forwarder or Resolver must be enabled for DNS lookups by unauthenticated clients to work.
What about the DHCP server on the CP interface? Do I need to disable the router's DHCP server, in order to have pfSense assign all the mess, or is there another way to configure the DNS resolver?
Thanks again for the great howto and any suggestion you'll give.

Offline deajan

  • Full Member
  • ***
  • Posts: 179
  • Karma: +26/-0
    • View Profile
Sorry for the late reply.
As long as the DNS is pfSense, I think you can use whatever DHCP server you like.
NetPOWER.fr - some opensource stuff for IT people

Offline deajan

  • Full Member
  • ***
  • Posts: 179
  • Karma: +26/-0
    • View Profile
@nhimcoi30889

I'm pretty sure that you have two instances of mysql running. Check with ps aux.
NetPOWER.fr - some opensource stuff for IT people

Offline astatine

  • Newbie
  • *
  • Posts: 5
  • Karma: +0/-0
    • View Profile
Hi deajan,

First thank you very much for this perfect HOWTO.

But I got a problem when i import the admin.sql script. Please have a look below:

Code: [Select]
mysql radius < admin.sql
ERROR 1064 (42000) at line 31: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PRIVIELGES' at line 1

Do you have any idea how to fix it? I am running a brand new pfsense 2.3 on a PC. Thank you in advance.

Offline deajan

  • Full Member
  • ***
  • Posts: 179
  • Karma: +26/-0
    • View Profile
There was a typo in admin.sql, should read PRIVILEGES and not PRIVIELGES.
Corrected on github. You may redownload the latest archive at https://github.com/deajan/pfSense-cp-auth-onestep/archive/master.zip
NetPOWER.fr - some opensource stuff for IT people

Offline astatine

  • Newbie
  • *
  • Posts: 5
  • Karma: +0/-0
    • View Profile
There was a typo in admin.sql, should read PRIVILEGES and not PRIVIELGES.
Corrected on github. You may redownload the latest archive at https://github.com/deajan/pfSense-cp-auth-onestep/archive/master.zip

Merci!!! Problem solved!!

Offline xAm56

  • Newbie
  • *
  • Posts: 1
  • Karma: +0/-0
    • View Profile
Hello deajan,

First thank you a lot for your work, the system works great !

I try to use your template only with the "room number" field  to log in on the portal.

I successfully disable the others fields (familyName, surName, emailAddress and newsletter) but when i validate the form, I've a blank page.

However, I pass the portal and I can access to the web but the result is not very clean

Could you please explain me which modifications I have to make in ozy-captive.php and perhaps in captiveportal-config.php .

Thanks in advance !