Netgate Discussion Forum
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Search
    • Register
    • Login

    [HOWTO] Captive portal + FreeRADIUS + local MySQL user friendly single step

    Scheduled Pinned Locked Moved
    Captive Portal
    47
    154
    99.3k
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • N
      nhimcoi30889
      last edited by

      Hi Deajan,
      I tried remove old mysql and reinstall new version but problem the same
      Can you give solution ?

      1 Reply Last reply Reply Quote 0
      • N
        nhimcoi30889
        last edited by

        Hi deajan,
        can you help me ?

        1 Reply Last reply Reply Quote 0
        • D
          deajan
          last edited by

          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

          1 Reply Last reply Reply Quote 0
          • N
            nhimcoi30889
            last edited by

            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

            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></default>

            This is error when connect mysql

            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

            1 Reply Last reply Reply Quote 0
            • C
              caraffandee
              last edited by

              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:

              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.

              1 Reply Last reply Reply Quote 0
              • D
                deajan
                last edited by

                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

                1 Reply Last reply Reply Quote 0
                • D
                  deajan
                  last edited by

                  @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

                  1 Reply Last reply Reply Quote 0
                  • A
                    astatine
                    last edited by

                    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:

                     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.

                    1 Reply Last reply Reply Quote 0
                    • D
                      deajan
                      last edited by

                      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

                      1 Reply Last reply Reply Quote 0
                      • A
                        astatine
                        last edited by

                        @deajan:

                        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!!

                        1 Reply Last reply Reply Quote 0
                        • X
                          xAm56
                          last edited by

                          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 !

                          1 Reply Last reply Reply Quote 0
                          • D
                            deajan
                            last edited by

                            Hello xAm56,

                            As far as I remember, the username is a combination of emailAdress and roomNumber and password is a combination of familyName and surName, in order to get some degree of randomness.
                            Basically, only using room number would mean that Radius records would not be unique.

                            I'll have a to push an update until monday which adds the language parameter in the url.
                            I'll try to check your usecase in the same time.

                            Regards,
                            Orsiris.

                            NetPOWER.fr - some opensource stuff for IT people

                            1 Reply Last reply Reply Quote 0
                            • D
                              deajan
                              last edited by

                              Have a look at the latest commits on github at https://github.com/deajan/pfSense-cp-auth-onestep/releases/tag/v0.44

                              NetPOWER.fr - some opensource stuff for IT people

                              1 Reply Last reply Reply Quote 0
                              • S
                                saygon
                                last edited by

                                Hi @deajan and thank you for your amazing work!

                                Unfortunately I'm not able to run mysql server.
                                I have made a clean install of pfSense 2.3, configured wan and lan, internet works. I'm also able to install all what do you mentioned on this guide.
                                Each time I try start mysql server it comes some error:

                                
[2.3.2-RELEASE][admin@pfSense.localdomain]/root: service mysql-server.sh start
Starting mysql.
Bad -c option
/usr/local/etc/rc.d/mysql-server.sh: WARNING: failed to start mysql

                                I have already research on Google regarding to this error, but don't found nothing useful.
                                Please let me know if you need some additional info or log output.

                                1 Reply Last reply Reply Quote 0
                                • D
                                  deajan
                                  last edited by

                                  @saygon I remember having dealt with that error while developping this project.
                                  Don't remember the exact root cause… probably a bad mysql version installed.

                                  Remove and reinstall the version corresponding to your setup.
                                  If the problem still occurs, post the content of your mysql_server.sh file, and mysql -v output.

                                  NetPOWER.fr - some opensource stuff for IT people

                                  1 Reply Last reply Reply Quote 0
                                  • A
                                    astatine
                                    last edited by

                                    +–--+----------+-------+---------------+---------------------+
                                    | id | username | pass  | reply        | authdate            |
                                    +----+----------+-------+---------------+---------------------+
                                    |  1 | testu    | testp | Access-Accept | 2015-09-29 15:13:24 |
                                    +----+----------+-------+---------------+---------------------+

                                    Hello deajan,

                                    would you please tell me , how can I set MD5 encrypt here for user password?

                                    1 Reply Last reply Reply Quote 0
                                    • D
                                      deajan
                                      last edited by

                                      @astatine Your question isn't in the scope of this howto. Please make your own question regarding FreeRADIUS + MySQL setup only.

                                      NetPOWER.fr - some opensource stuff for IT people

                                      1 Reply Last reply Reply Quote 0
                                      • J
                                        joel.dq
                                        last edited by

                                        Hello !

                                        I'm new to pfSense and forum. I was tasked to set up this type of access for an organization and I'm having a small trouble that  I probably think it's pretty stupid but can't make it work.

                                        I followed the instructions to the letter and I got the captive portal working as is- No modification of the files except for mysql password etc.

                                        On the LAN PC I get successfully forwarded to the Captive Portal but as soon as I press Connect nothing happens, the form is cleared (as if new connection) and remain there.

                                        I tried to see if the PHP inserted something in the radius DB but I found nothing there and for the life of me I can't find in the logs, or where in the logs, I can tail if there are connectivity errors with mysql +  php etc.

                                        I do want t thank you though, the guide is very detailed and I was able to set everything up quick. I just now need to to make that work.

                                        Thanks !

                                        1 Reply Last reply Reply Quote 0
                                        • D
                                          deajan
                                          last edited by

                                          Hello joel.dq,

                                          Can you activate debug mode in captiveportal-config.php ?

                                          NetPOWER.fr - some opensource stuff for IT people

                                          1 Reply Last reply Reply Quote 0
                                          • J
                                            joel.dq
                                            last edited by

                                            I think I did already. Where should I see that logging?

                                            1 Reply Last reply Reply Quote 0
                                            • First post
                                              Last post