<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:Tahoma
}
--></style></head>
<body class='hmmessage'><div dir='ltr'>
Hi again people, so a week ago i posted here a problem with 802.1x i had and it turned to be all my users were MD5 password, so that was my problem.<div>Today i created a new DB on a test server, changed on sql.conf and tested.</div><div>Im getting this error, i tried to understand that, BUT im kinda a newbie on freeradius + linux (i started to learn 1 month ago) and couldnt find the error.</div><div>I dont know if the error is on MYSQL or freeradius...</div><div><br></div><div>Appreciate anything you guys can help me!</div><div>Thanks in advance.</div><div><br></div><div><br></div><div><div>nas_query = "SELECT id, nasname, shortname, type, secret, server FROM nas"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>authorize_check_query = "SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' AND ( id_tp_usuario in (1, 2, 3) OR ( id_tp_usuario = 4 AND dt_ingresso <= CURDATE() AND dt_egresso >= CURDATE() ) ) ORDER BY id"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>authorize_reply_query = "SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>authorize_group_check_query = "SELECT id, groupname, attribute, Value, op FROM radgroupcheck WHERE groupname = '%{Sql-Group}' ORDER BY id"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>authorize_group_reply_query = "SELECT id, groupname, attribute, value, op FROM radgroupreply WHERE groupname = '%{Sql-Group}' ORDER BY id"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_onoff_query = " UPDATE radacct SET acctstoptime = '%S', acctsessiontime = unix_timestamp('%S') - unix_timestamp(acctstarttime), acctterminatecause = '%{Acct-Terminate-Cause}', acctstopdelay = %{%{Acct-Delay-Time}:-0} WHERE acctstoptime IS NULL AND nasipaddress = '%{NAS-IP-Address}' AND acctstarttime <= '%S'"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_update_query = " UPDATE radacct SET framedipaddress = '%{Framed-IP-Address}', acctsessiontime = '%{Acct-Session-Time}', acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}' WHERE acctsessionid = '%{Acct-Session-Id}' AND username = '%{SQL-User-Name}' AND nasipaddress = '%{NAS-IP-Address}'"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_update_query_alt = " INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctsessiontime, acctauthentic, connectinfo_start, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, servicetype, framedprotocol, framedipaddress, acctstartdelay, xascendsessionsvrkey) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', DATE_SUB('%S', INTERVAL (%{%{Acct-Session-Time}:-0} + %{%{Acct-Delay-Time}:-0}) SECOND), '%{Acct-Session-Time}', '%{Acct-Authentic}', '', '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0', '%{X-Ascend-Session-Svr-Key}')"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_start_query = " INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress, acctstartdelay, acctstopdelay, xascendsessionsvrkey) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', '%S', NULL, '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '%{%{Acct-Delay-Time}:-0}', '0', '%{X-Ascend-Session-Svr-Key}')"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_start_query_alt = " UPDATE radacct SET acctstarttime = '%S', acctstartdelay = '%{%{Acct-Delay-Time}:-0}', connectinfo_start = '%{Connect-Info}' WHERE acctsessionid = '%{Acct-Session-Id}' AND username = '%{SQL-User-Name}' AND nasipaddress = '%{NAS-IP-Address}'"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_stop_query = " UPDATE radacct SET acctstoptime = '%S', acctsessiontime = '%{Acct-Session-Time}', acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', acctterminatecause = '%{Acct-Terminate-Cause}', acctstopdelay = '%{%{Acct-Delay-Time}:-0}', connectinfo_stop = '%{Connect-Info}' WHERE acctsessionid = '%{Acct-Session-Id}' AND username = '%{SQL-User-Name}' AND nasipaddress = '%{NAS-IP-Address}'"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>accounting_stop_query_alt = " INSERT INTO radacct (acctsessionid, acctuniqueid, username, realm, nasipaddress, nasportid, nasporttype, acctstarttime, acctstoptime, acctsessiontime, acctauthentic, connectinfo_start, connectinfo_stop, acctinputoctets, acctoutputoctets, calledstationid, callingstationid, acctterminatecause, servicetype, framedprotocol, framedipaddress, acctstartdelay, acctstopdelay) VALUES ('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', '%{NAS-Port-Type}', DATE_SUB('%S', INTERVAL (%{%{Acct-Session-Time}:-0} + %{%{Acct-Delay-Time}:-0}) SECOND), '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', '%{Connect-Info}', '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}', '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0', '%{%{Acct-Delay-Time}:-0}')"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>group_membership_query = "SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>connect_failure_retry_delay = 60</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>simul_count_query = ""</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>simul_verify_query = "SELECT radacctid, acctsessionid, username, nasipaddress, nasportid, framedipaddress, callingstationid, framedprotocol FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>postauth_query = "INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S')"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"</div><div> }</div><div>rlm_sql (sql): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked</div><div>rlm_sql (sql): Attempting to connect to root@localhost:/radius</div><div>rlm_sql (sql): starting 0</div><div>rlm_sql (sql): Attempting to connect rlm_sql_mysql #0</div><div>rlm_sql_mysql: Starting connect to MySQL server for #0</div><div>rlm_sql (sql): Connected new DB handle, #0</div><div>rlm_sql (sql): starting 1</div><div>rlm_sql (sql): Attempting to connect rlm_sql_mysql #1</div><div>rlm_sql_mysql: Starting connect to MySQL server for #1</div><div>rlm_sql (sql): Connected new DB handle, #1</div><div>rlm_sql (sql): starting 2</div><div>rlm_sql (sql): Attempting to connect rlm_sql_mysql #2</div><div>rlm_sql_mysql: Starting connect to MySQL server for #2</div><div>rlm_sql (sql): Connected new DB handle, #2</div><div>rlm_sql (sql): starting 3</div><div>rlm_sql (sql): Attempting to connect rlm_sql_mysql #3</div><div>rlm_sql_mysql: Starting connect to MySQL server for #3</div><div>rlm_sql (sql): Connected new DB handle, #3</div><div>rlm_sql (sql): starting 4</div><div>rlm_sql (sql): Attempting to connect rlm_sql_mysql #4</div><div>rlm_sql_mysql: Starting connect to MySQL server for #4</div><div>rlm_sql (sql): Connected new DB handle, #4</div><div>rlm_sql (sql): Processing generate_sql_clients</div><div>rlm_sql (sql) in generate_sql_clients: query is SELECT id, nasname, shortname, type, secret, server FROM nas</div><div>rlm_sql (sql): Reserving sql socket id: 4</div><div>rlm_sql (sql): Read entry nasname=172.23.54.2,shortname=ruckus-controller,secret=t3st3</div><div>rlm_sql (sql): Adding client 172.23.54.2 (ruckus-controller, server=<none>) to clients list</div><div>rlm_sql (sql): Released sql socket id: 4</div><div> Module: Checking session {...} for more modules to load</div><div> Module: Linked to module rlm_radutmp</div><div> Module: Instantiating module "radutmp" from file /etc/freeradius/modules/radutmp</div><div> radutmp {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>filename = "/var/log/freeradius/radutmp"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>username = "%{User-Name}"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>case_sensitive = yes</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>check_with_nas = yes</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>perm = 384</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>callerid = yes</div><div> }</div><div> Module: Checking post-proxy {...} for more modules to load</div><div> Module: Checking post-auth {...} for more modules to load</div><div> Module: Linked to module rlm_attr_filter</div><div> Module: Instantiating module "attr_filter.access_reject" from file /etc/freeradius/modules/attr_filter</div><div> attr_filter attr_filter.access_reject {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>attrsfile = "/etc/freeradius/attrs.access_reject"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>key = "%{User-Name}"</div><div> }</div><div> } # modules</div><div>} # server</div><div>server { # from file /etc/freeradius/radiusd.conf</div><div> modules {</div><div> Module: Checking authenticate {...} for more modules to load</div><div> Module: Linked to module rlm_digest</div><div> Module: Instantiating module "digest" from file /etc/freeradius/modules/digest</div><div> Module: Linked to module rlm_unix</div><div> Module: Instantiating module "unix" from file /etc/freeradius/modules/unix</div><div> unix {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>radwtmp = "/var/log/freeradius/radwtmp"</div><div> }</div><div> Module: Checking authorize {...} for more modules to load</div><div> Module: Linked to module rlm_preprocess</div><div> Module: Instantiating module "preprocess" from file /etc/freeradius/modules/preprocess</div><div> preprocess {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>huntgroups = "/etc/freeradius/huntgroups"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>hints = "/etc/freeradius/hints"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>with_ascend_hack = no</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ascend_channels_per_line = 23</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>with_ntdomain_hack = no</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>with_specialix_jetstream_hack = no</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>with_cisco_vsa_hack = no</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>with_alvarion_vsa_hack = no</div><div> }</div><div> Module: Checking preacct {...} for more modules to load</div><div> Module: Linked to module rlm_acct_unique</div><div> Module: Instantiating module "acct_unique" from file /etc/freeradius/modules/acct_unique</div><div> acct_unique {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>key = "User-Name, Acct-Session-Id, NAS-IP-Address, Client-IP-Address, NAS-Port"</div><div> }</div><div> Module: Checking accounting {...} for more modules to load</div><div> Module: Linked to module rlm_detail</div><div> Module: Instantiating module "detail" from file /etc/freeradius/modules/detail</div><div> detail {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>detailfile = "/var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>header = "%t"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>detailperm = 384</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>dirperm = 493</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>locking = no</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>log_packet_header = no</div><div> }</div><div> Module: Linked to module rlm_sql_log</div><div> Module: Instantiating module "sql_log" from file /etc/freeradius/modules/sql_log</div><div> sql_log {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>path = "/var/log/freeradius/radacct/sql-relay"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Post-Auth = "INSERT INTO radpostauth <span class="Apple-tab-span" style="white-space:pre"> </span> (username, pass, reply, authdate) VALUES <span class="Apple-tab-span" style="white-space:pre"> </span> ('%{User-Name}', '%{User-Password:-Chap-Password}', <span class="Apple-tab-span" style="white-space:pre"> </span> '%{reply:Packet-Type}', '%S');"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>sql_user_name = "%{%{User-Name}:-DEFAULT}"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>utf8 = no</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>safe-characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"</div><div> }</div><div> Module: Instantiating module "attr_filter.accounting_response" from file /etc/freeradius/modules/attr_filter</div><div> attr_filter attr_filter.accounting_response {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>attrsfile = "/etc/freeradius/attrs.accounting_response"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>key = "%{User-Name}"</div><div> }</div><div> Module: Checking session {...} for more modules to load</div><div> Module: Checking post-proxy {...} for more modules to load</div><div> Module: Checking post-auth {...} for more modules to load</div><div> } # modules</div><div>} # server</div><div>radiusd: #### Opening IP addresses and Ports ####</div><div>listen {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>type = "auth"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ipaddr = *</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>port = 0</div><div>}</div><div>listen {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>type = "acct"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ipaddr = *</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>port = 0</div><div>}</div><div>listen {</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>type = "auth"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>ipaddr = 127.0.0.1</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>port = 18120</div><div>}</div><div>Listening on authentication address * port 1812</div><div>Listening on accounting address * port 1813</div><div>Listening on authentication address 127.0.0.1 port 18120 as server inner-tunnel</div><div>Listening on proxy address * port 1814</div><div>Ready to process requests.</div><div>rad_recv: Access-Request packet from host 172.23.54.2 port 32777, id=52, length=206</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>User-Name = "user"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Calling-Station-Id = "00-22-43-09-43-BD"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>NAS-IP-Address = 172.23.54.2</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>NAS-Port = 1</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Called-Station-Id = "68-92-34-91-91-48:UNIFEBE-1X"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Service-Type = Framed-User</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Framed-MTU = 1400</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>NAS-Port-Type = Wireless-802.11</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>NAS-Identifier = "68-92-34-91-91-48"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Connect-Info = "CONNECT 802.11b/g"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>WISPr-Location-Name = "2o-Andar"</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>EAP-Message = 0x020000090175736572</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Vendor-25053-Attr-3 = 0x554e49464542452d3158</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>Message-Authenticator = 0x53dcd42dc46e9bee1e5538df27c8ae45</div><div># Executing section authorize from file /etc/freeradius/sites-enabled/default</div><div>+- entering group authorize {...}</div><div>++[preprocess] returns ok</div><div>++[chap] returns noop</div><div>++[mschap] returns noop</div><div>++[digest] returns noop</div><div>[suffix] No '@' in User-Name = "user", looking up realm NULL</div><div>[suffix] No such realm "NULL"</div><div>++[suffix] returns noop</div><div>[eap] EAP packet type response id 0 length 9</div><div>[eap] No EAP Start, assuming it's an on-going EAP conversation</div><div>++[eap] returns updated</div><div>++[unix] returns notfound</div><div>++[files] returns noop</div><div>[sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{User-Name} -> user</div><div>[sql] sql_set_user escaped user --> 'user'</div><div>rlm_sql (sql): Reserving sql socket id: 3</div><div>[sql] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' AND ( id_tp_usuario in (1, 2, 3) OR ( id_tp_usuario = 4 AND dt_ingresso <= CURDATE() AND dt_egresso >= CURDATE() ) ) ORDER BY id -> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'user' AND ( id_tp_usuario in (1, 2, 3) OR ( id_tp_usuario = 4 AND dt_ingresso <= CURDATE() AND dt_egresso >= CURDATE() ) ) ORDER BY id</div><div>rlm_sql_mysql: MYSQL check_error: 1054 received</div><div>rlm_sql_getvpdata: database query error</div><div>[sql] SQL query error; rejecting user</div><div>rlm_sql (sql): Released sql socket id: 3</div><div>++[sql] returns fail</div><div>Invalid user: [user/<via Auth-Type = EAP>] (from client ruckus-controller port 1 cli 00-22-43-09-43-BD)</div><div>Using Post-Auth-Type Reject</div><div># Executing group from file /etc/freeradius/sites-enabled/default</div><div>+- entering group REJECT {...}</div><div>[attr_filter.access_reject] <span class="Apple-tab-span" style="white-space:pre"> </span>expand: %{User-Name} -> user</div><div> attr_filter: Matched entry DEFAULT at line 11</div><div>++[attr_filter.access_reject] returns updated</div><div>Delaying reject of request 0 for 1 seconds</div><div>Going to the next request</div><div>Waking up in 0.9 seconds.</div><div>Sending delayed reject for request 0</div><div>Sending Access-Reject of id 52 to 172.23.54.2 port 32777</div><div>Waking up in 4.9 seconds.</div><div>Cleaning up request 0 ID 52 with timestamp +205</div><div>Ready to process requests.</div></div> </div></body>
</html>