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

    Pfsene captive port + freeradius3 + Mysql integration error

    Scheduled Pinned Locked Moved Captive Portal
    7 Posts 3 Posters 1.1k Views
    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.
    • J
      Jangchu Dorji
      last edited by

      Dear all;
      I am little new to pfsense .therefor i would like request expert suggestion here.
      I am trying freeradius3 integration with Mysql.

      My freeradius3 is running fine i am able fetch my mysql which i have configured pfsense only.

      I did radtest before enabling mysql support.That time my wasnt rejected.
      When i enable mysql support I am getting attached error.

      I have used this link for refrence http://netpower.fr/sites/default/files/soft/html-doc/pfSense-cp-auth-onestep_0.html

      All your help and suggestion will higly apcreciated by me

      Thanking you

      afe1b880-2d13-462a-a0b3-8fefd9c6f12f-image.png

      1 Reply Last reply Reply Quote 0
      • viktor_gV
        viktor_g Netgate
        last edited by

        Which version of FreeRADIUS pkg are you using?
        some issues have been fixed recently:
        https://forum.netgate.com/topic/160549/captive-portal-error

        J 1 Reply Last reply Reply Quote 1
        • J
          Jangchu Dorji @viktor_g
          last edited by

          @viktor_g
          Thank you for your reply.
          I have installed 0.15.7_29 version.Seems like version was not problem.I did radiusd -X
          Failed binding to auth address 172.10.0.1 port 1812 bound to server default: Address already in use
          /usr/local/etc/raddb/sites-enabled/default[2]: Error binding to port for 172.10. 0.1 port 1812
          I check runing prot using netstat...1812 wasnt used my radius only.
          inside this /usr/local/etc/raddb/sites-enabled/ there are two file ( inner-tunnel-peap inner-tunnel-ttls)
          inner-tunnel-ttls has config something like this(server inner-tunnel-ttls {
          listen {
          ipaddr = 127.0.0.1
          port = 18127
          type = auth
          }
          )
          inner-tunnel-peap has config ( listen {
          ipaddr = 127.0.0.1
          port = 18128
          type = auth
          })
          I am little confused..

          your help will be highly appcreciated by me.

          GertjanG 1 Reply Last reply Reply Quote 0
          • GertjanG
            Gertjan @Jangchu Dorji
            last edited by Gertjan

            @jangchu-dorji said in Pfsene captive port + freeradius3 + Mysql integration error:

            I did radiusd -X

            Before you do that, stop the running 'GUI' Radius first.

            This one, like this :

            cbc90559-bf40-4c50-877f-3e5dd39e05a8-image.png

            Now you can start radius in debugging mode on the console/SSH access :

            radiusd -X
            

            It should progress up until it shows :

            Ready to process requests
            

            Some (3 ?) yellow lines will scroll by. These are harmless.
            Red lines means troubles.

            edit :

            ipaddr = 127.0.0.1
            

            The real cause is : FreeRadius is way more complex as what you imagined.

            It listens to a 'NAS', this is the 'client' which is a source that needs authentication.
            Examples are simple pfSense logins, or a Captivve portal.
            These are running locally, on the same machne, pfSense, so it listens - radius is a server here - on localhost, 127.0.0.1.
            The captive portal, as a client, connect also to localhost, 127.0.0.1 - same port.

            FreeRadius can use (locally stored) plain files keep it records (data) up to date.
            It can also use several types of databases, MySQL is just one of them.
            In that case, it should use an Interface and an IP of the MySQL server (running MySQL server on pfSense is also possible but not encouraged/supported).

            Etc.

            No "help me" PM's please. Use the forum, the community will thank you.
            Edit : and where are the logs ??

            1 Reply Last reply Reply Quote 1
            • J
              Jangchu Dorji
              last edited by

              Thank you for suggestion.
              This is the what got after stoping freeradius:There is not red line, whats is peap and ttls is listening to 18128 and 18127...
              Enlighten me :)

              Listening on auth address 172.10.0.1 port 1812 bound to server default
              Listening on acct address 172.10.0.1 port 1813 bound to server default
              Listening on status address 172.10.0.1 port 1816 bound to server default
              Listening on auth address 127.0.0.1 port 18127 bound to server inner-tunnel-ttls
              Listening on auth address 127.0.0.1 port 18128 bound to server inner-tunnel-peap
              Ready to process requests

              GertjanG 1 Reply Last reply Reply Quote 0
              • GertjanG
                Gertjan @Jangchu Dorji
                last edited by

                @jangchu-dorji said in Pfsene captive port + freeradius3 + Mysql integration error:

                Ready to process requests

                Now you login with some some capitve portal ..... and see what happens on the screen.
                This is what happens :

                (15) Received Access-Request Id 147 from 192.168.2.1:45466 to 192.168.2.1:1812 length 161
                (15)   Service-Type = Login-User
                (15)   User-Name = "x"
                (15)   User-Password = "x"
                (15)   NAS-IP-Address = 192.168.2.1
                (15)   NAS-Identifier = "CaptivePortal-cpzone1"
                (15)   Calling-Station-Id = "f6:f2:a2:34:a8:53"
                (15)   Called-Station-Id = "00:15:17:77:d1:1b:pfsense.brit-hotel-fumel.net"
                (15)   NAS-Port-Type = Ethernet
                (15)   NAS-Port = 2018
                (15)   Framed-IP-Address = 192.168.2.102
                (15) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/default
                (15)   authorize {
                (15)     [preprocess] = ok
                (15)     [chap] = noop
                (15)     [mschap] = noop
                (15)     [digest] = noop
                (15) suffix: Checking for suffix after "@"
                (15) suffix: No '@' in User-Name = "x", skipping NULL due to config.
                (15)     [suffix] = noop
                (15) ntdomain: Checking for prefix before "\"
                (15) ntdomain: No '\' in User-Name = "x", skipping NULL due to config.
                (15)     [ntdomain] = noop
                (15) eap: No EAP-Message, not doing EAP
                (15)     [eap] = noop
                (15) files: users: Matched entry x at line 386
                (15)     [files] = ok
                (15)     if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept")) {
                (15)     if ((notfound || noop) && ("%{%{Control:Auth-Type}:-No-Accept}" != "Accept"))  -> FALSE
                (15) dailycounter: WARNING: Couldn't find check attribute, control:Max-Daily-Session, doing nothing...
                (15)     [dailycounter] = noop
                (15) monthlycounter: WARNING: Couldn't find check attribute, control:Max-Monthly-Session, doing nothing...
                (15)     [monthlycounter] = noop
                (15) noresetcounter: WARNING: Couldn't find check attribute, control:Max-All-Session, doing nothing...
                (15)     [noresetcounter] = noop
                (15) expire_on_login: WARNING: Couldn't find check attribute, control:Expire-After, doing nothing...
                (15)     [expire_on_login] = noop
                (15)     if (&request:Calling-Station-Id == &control:Calling-Station-Id) {
                (15)     ERROR: Failed retrieving values required to evaluate condition
                (15)     [expiration] = noop
                (15)     [logintime] = noop
                (15)     [pap] = updated
                (15)   } # authorize = updated
                (15) Found Auth-Type = PAP
                (15) # Executing group from file /usr/local/etc/raddb/sites-enabled/default
                (15)   Auth-Type PAP {
                (15) pap: Login attempt with password
                (15) pap: Comparing with "known good" Cleartext-Password
                (15) pap: User authenticated successfully
                (15)     [pap] = ok
                (15)   } # Auth-Type PAP = ok
                (15) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default
                (15)   post-auth {
                (15)     update {
                (15)       No attributes updated for RHS &session-state:
                (15)     } # update = noop
                (15)     redundant sql {
                (15) sql1: EXPAND .query
                (15) sql1:    --> .query
                (15) sql1: Using query template 'query'
                rlm_sql (sql1): Reserved connection (0)
                (15) sql1: EXPAND %{User-Name}
                (15) sql1:    --> x
                (15) sql1: SQL-User-Name set to 'x'
                (15) sql1: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S.%M')
                (15) sql1:    --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'x', 'x', 'Access-Accept', '2021-03-03 11:21:55.219611')
                (15) sql1: EXPAND /var/log/sqltrace.sql
                (15) sql1:    --> /var/log/sqltrace.sql
                (15) sql1: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'x', 'x', 'Access-Accept', '2021-03-03 11:21:55.219611')
                (15) sql1: SQL query returned: success
                (15) sql1: 1 record(s) updated
                rlm_sql (sql1): Released connection (0)
                (15)       [sql1] = ok
                (15)     } # redundant sql = ok
                (15) exec: Executing: /bin/sh /usr/local/etc/raddb/scripts/datacounter_auth.sh x daily:
                (15) exec: Program returned code (0) and output ''
                (15) exec: Program executed successfully
                (15)     [exec] = ok
                (15)     policy remove_reply_message_if_eap {
                (15)       if (&reply:EAP-Message && &reply:Reply-Message) {
                (15)       if (&reply:EAP-Message && &reply:Reply-Message)  -> FALSE
                (15)       else {
                (15)         [noop] = noop
                (15)       } # else = noop
                (15)     } # policy remove_reply_message_if_eap = noop
                (15)   } # post-auth = ok
                (15) Sent Access-Accept Id 147 from 192.168.2.1:1812 to 192.168.2.1:45466 length 0
                (15)   WISPr-Bandwidth-Max-Up = 1000000
                (15)   WISPr-Bandwidth-Max-Down = 5000000
                (15)   Acct-Interim-Interval = 500
                (15)   WISPr-Redirection-URL = "https://www.google.com/"
                (15)   pfSense-Max-Total-Octets = 0
                (15) Finished request
                Waking up in 4.9 seconds.
                (16) Received Accounting-Request Id 181 from 192.168.2.1:59258 to 192.168.2.1:1813 length 173
                (16)   Service-Type = Login-User
                (16)   User-Name = "x"
                (16)   Acct-Status-Type = Start
                (16)   Acct-Authentic = RADIUS
                (16)   NAS-IP-Address = 192.168.2.1
                (16)   NAS-Identifier = "CaptivePortal-cpzone1"
                (16)   NAS-Port-Type = Ethernet
                (16)   NAS-Port = 2018
                (16)   Acct-Session-Id = "88ffb89ea065ace8"
                (16)   Framed-IP-Address = 192.168.2.102
                (16)   Calling-Station-Id = "f6:f2:a2:34:a8:53"
                (16)   Called-Station-Id = "00:15:17:77:d1:1b:pfsense.brit-hotel-fumel.net"
                (16) # Executing section preacct from file /usr/local/etc/raddb/sites-enabled/default
                (16)   preacct {
                (16)     [preprocess] = ok
                (16)     update request {
                (16)       EXPAND %{expr: %l - %{%{Acct-Session-Time}:-0} - %{%{Acct-Delay-Time}:-0}}
                (16)          --> 1614766915
                (16)       &FreeRADIUS-Acct-Session-Start-Time = Mar  3 2021 11:21:55 CET
                (16)     } # update request = noop
                (16)     policy acct_unique {
                (16)       update request {
                (16)         &Tmp-String-9 := "ai:"
                (16)       } # update request = noop
                (16)       if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) &&      ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i)) {
                (16)       EXPAND %{hex:&Class}
                (16)          -->
                (16)       EXPAND ^%{hex:&Tmp-String-9}
                (16)          --> ^61693a
                (16)       if (("%{hex:&Class}" =~ /^%{hex:&Tmp-String-9}/) &&      ("%{string:&Class}" =~ /^ai:([0-9a-f]{32})/i))  -> FALSE
                (16)       else {
                (16)         update request {
                (16)           EXPAND %{md5:%{User-Name},%{Acct-Session-ID},%{%{NAS-IPv6-Address}:-%{NAS-IP-Address}},%{NAS-Identifier},%{NAS-Port-ID},%{NAS-Port}}
                (16)              --> 5145365f2f035454afc56f02dee08f9a
                (16)           &Acct-Unique-Session-Id := 5145365f2f035454afc56f02dee08f9a
                (16)         } # update request = noop
                (16)       } # else = noop
                (16)     } # policy acct_unique = noop
                (16) suffix: Checking for suffix after "@"
                (16) suffix: No '@' in User-Name = "x", skipping NULL due to config.
                (16)     [suffix] = noop
                (16) ntdomain: Checking for prefix before "\"
                (16) ntdomain: No '\' in User-Name = "x", skipping NULL due to config.
                (16)     [ntdomain] = noop
                (16)     [files] = noop
                (16)   } # preacct = ok
                (16) # Executing section accounting from file /usr/local/etc/raddb/sites-enabled/default
                (16)   accounting {
                (16) detail: EXPAND /var/log/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d
                (16) detail:    --> /var/log/radacct/192.168.2.1/detail-20210303
                (16) detail: /var/log/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d expands to /var/log/radacct/192.168.2.1/detail-20210303
                (16) detail: EXPAND %t
                (16) detail:    --> Wed Mar  3 11:21:55 2021
                (16)     [detail] = ok
                (16)     if ((request:Acct-Status-Type == Stop) || (request:Acct-Status-Type == Interim-Update)) {
                (16)     if ((request:Acct-Status-Type == Stop) || (request:Acct-Status-Type == Interim-Update))  -> FALSE
                (16) radutmp: EXPAND /var/log/radutmp
                (16) radutmp:    --> /var/log/radutmp
                (16) radutmp: EXPAND %{User-Name}
                (16) radutmp:    --> x
                (16)     [radutmp] = ok
                (16)     redundant sql {
                (16) sql1: EXPAND %{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}
                (16) sql1:    --> type.start.query
                (16) sql1: Using query template 'query'
                rlm_sql (sql1): Reserved connection (4)
                (16) sql1: EXPAND %{User-Name}
                (16) sql1:    --> x
                (16) sql1: SQL-User-Name set to 'x'
                (16) sql1: EXPAND INSERT INTO radacct (acctsessionid,           acctuniqueid,           username, realm,                        nasipaddress,           nasportid, nasporttype,        acctstarttime,           acctupdatetime, acctstoptime,           acctsessiontime,        acctauthentic, connectinfo_start,       connectinfo_stop,       acctinputoctets, acctoutputoctets,      calledstationid,        callingstationid, acctterminatecause,   servicetype,            framedprotocol, framedipaddress,        framedipv6address,      framedipv6prefix, framedinterfaceid,    delegatedipv6prefix) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{%{NAS-Port-ID}:-%{NAS-Port}}', '%{NAS-Port-Type}', FROM_UNIXTIME(%{%{integer:Event-Timestamp}:-%l}), FROM_UNIXTIME(%{%{integer:Event-Timestamp}:-%l}), NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Framed-IPv6-Address}', '%{Framed-IPv6-Prefix}', '%{Framed-Interface-Id}', '%{Delegated-IPv6-Prefix}')
                (16) sql1:    --> INSERT INTO radacct (acctsessionid,           acctuniqueid,           username, realm,                        nasipaddress,           nasportid, nasporttype,        acctstarttime,           acctupdatetime, acctstoptime,           acctsessiontime,        acctauthentic, connectinfo_start,       connectinfo_stop,       acctinputoctets, acctoutputoctets,      calledstationid,        callingstationid, acctterminatecause,   servicetype,            framedprotocol, framedipaddress,        framedipv6address,      framedipv6prefix, framedinterfaceid,    delegatedipv6prefix) VALUES ('88ffb89ea065ace8', '5145365f2f035454afc56f02dee08f9a', 'x', '', '192.168.2.1', '2018', 'Ethernet', FROM_UNIXTIME(1614766915), FROM_UNIXTIME(1614766915), NULL, '0', 'RADIUS', '', '', '0', '0', '00:15:17:77:d1:1b:pfsense.brit-hotel-fumel.net', 'f6:f2:a2:34:a8:53', '', 'Login-User', '', '192.168.2.102', '', '', '', '')
                (16) sql1: EXPAND /var/log/sqltrace.sql
                (16) sql1:    --> /var/log/sqltrace.sql
                (16) sql1: Executing query: INSERT INTO radacct (acctsessionid,         acctuniqueid,           username, realm,                        nasipaddress,           nasportid, nasporttype,acctstarttime,           acctupdatetime, acctstoptime,           acctsessiontime,        acctauthentic, connectinfo_start,       connectinfo_stop,       acctinputoctets, acctoutputoctets,      calledstationid,        callingstationid, acctterminatecause,   servicetype,            framedprotocol, framedipaddress,        framedipv6address,      framedipv6prefix, framedinterfaceid,    delegatedipv6prefix) VALUES ('88ffb89ea065ace8', '5145365f2f035454afc56f02dee08f9a', 'x', '', '192.168.2.1', '2018', 'Ethernet', FROM_UNIXTIME(1614766915), FROM_UNIXTIME(1614766915), NULL, '0', 'RADIUS', '', '', '0', '0', '00:15:17:77:d1:1b:pfsense.brit-hotel-fumel.net', 'f6:f2:a2:34:a8:53', '', 'Login-User', '', '192.168.2.102', '', '', '', '')
                (16) sql1: SQL query returned: success
                (16) sql1: 1 record(s) updated
                rlm_sql (sql1): Released connection (4)
                (16)       [sql1] = ok
                (16)     } # redundant sql = ok
                (16)     [exec] = noop
                (16) attr_filter.accounting_response: EXPAND %{User-Name}
                (16) attr_filter.accounting_response:    --> x
                (16) attr_filter.accounting_response: Matched entry DEFAULT at line 12
                (16)     [attr_filter.accounting_response] = updated
                (16)   } # accounting = updated
                (16) Sent Accounting-Response Id 181 from 192.168.2.1:1813 to 192.168.2.1:59258 length 0
                (16) Finished request
                (16) Cleaning up request packet ID 181 with timestamp +159
                Waking up in 4.9 seconds.
                

                As you can see, there are tons of 'SQL' details.

                And again : this is just for simple 'a captive portal login'.
                You didn't even see the interim checkups :

                b3b9cbe4-2299-4c5a-afc1-e68847baa0af-image.png

                These interim connections enforce 'byte counting' and 'timed connections'.

                There is even a red (error) line :

                (45)     ERROR: Failed retrieving values required to evaluate condition
                

                but again : harmless.

                No "help me" PM's please. Use the forum, the community will thank you.
                Edit : and where are the logs ??

                J 1 Reply Last reply Reply Quote 0
                • J
                  Jangchu Dorji @Gertjan
                  last edited by

                  @gertjan
                  Thank you for kind suggestion it had hlep me so much.For now i have intsalled fresh pfsense and upgraded.After that we worked.

                  Thank you Gertan

                  1 Reply Last reply Reply Quote 0
                  • First post
                    Last post
                  Copyright 2025 Rubicon Communications LLC (Netgate). All rights reserved.