2013/3/24 Alan DeKok <al...@deployingradius.com>: > Бен Томпсон wrote: >> I began work on testing with Oracle but I have come across a couple of >> issues. Firstly one of my SQL statements is throwing up an error, and >> secondly the server is sending a unicast reply when I need a >> broadcast. I should be able to fix the dodgy SQL but I wondered if >> anyone could help me fix the broadcast/unicast problem. Here is the >> full degug :- > > The debug log > >> Received DHCP-Discover of id 64b2e216 from 0.0.0.0:68 to 0.0.0.0:67 >> DHCP-Opcode = Client-Message >> DHCP-Hardware-Type = Ethernet >> DHCP-Hardware-Address-Length = 6 >> DHCP-Hop-Count = 0 >> DHCP-Transaction-Id = 1689444886 >> DHCP-Number-of-Seconds = 0 >> DHCP-Flags = 0 > > The broadcast flag isn't set. So the client is asking for a unicast > response. > >> (0) dhcp_sqlippool : expand: 'START TRANSACTION' -> 'START TRANSACTION' >> rlm_sql (sql): Executing query: 'START TRANSACTION' >> rlm_sql_oracle: execute query failed in sql_query: ORA-00900: invalid >> SQL statement > > That needs to be fixed. I don't know much about Oracle, and I don't > have an Oracle system running to test it. > >> (0) DHCP: Reply will be sent unicast to your-ip-address >> Sending DHCP-Offer of id 64b2e216 to 10.99.0.11:68 > > You should be able to fix this by doing: > > update reply { > DHCP-Flags = Broadcast > } > > Which will force the server to send a broadcast reply. > > Alan DeKok.
Hi Alan Many thanks for the quick reply. The SQL statement "START TRANSACTION" looks to be hard coded into rlm_sqlippool.c but I don't know enough about Oracle etiher to say why it is flagged as an error. However from looking at the code I assume that it is supposed to signify the begining of a batch of SQL statements which after execution will be be either committed or rolled back. My guess would be that it is a redundant command as according this page: http://stackoverflow.com/questions/1366851/how-do-i-find-out-if-an-oracle-database-is-set-to-autocommit - commit/rollback, is a purely client side thing. So if a client says to Oracle "here are some statements which I will not commit straight away" I guess the server reply's with "why are you telling me?". If I am right then, I guess we can just remove the "START TRANSACTION" statement for Oracle, but unfoturnately I don't know enough myself to be sure. I do have access to and Oracle database though, so I am happy to do any testing, if someone else with more Oracle knowledge can advise what we should do. The broadcast flag did the trick thanks. Here is the DHCP discover section I am using :- dhcp DHCP-Discover { update control { Pool-Name := test_ip_pool } dhcp_sqlippool update reply { DHCP-Subnet-Mask = 255.255.255.0 DHCP-Domain-Name-Server = 192.168.12.1 DHCP-Router-Address = 10.99.0.1 DHCP-IP-Address-Lease-Time = 300 DHCP-DHCP-Server-Identifier = 10.99.0.100 } if (DHCP-Gateway-IP-Address == 0.0.0.0) { update reply { DHCP-Flags = Broadcast } } } However, it seems that none of the options are added to the reply, and for some reason an empty packet is sent to the client :- Received DHCP-Discover of id 3f1a9769 from 0.0.0.0:68 to 0.0.0.0:67 DHCP-Opcode = Client-Message DHCP-Hardware-Type = Ethernet DHCP-Hardware-Address-Length = 6 DHCP-Hop-Count = 0 DHCP-Transaction-Id = 1058707305 DHCP-Number-of-Seconds = 0 DHCP-Flags = 0 DHCP-Client-IP-Address = 0.0.0.0 DHCP-Your-IP-Address = 0.0.0.0 DHCP-Server-IP-Address = 0.0.0.0 DHCP-Gateway-IP-Address = 0.0.0.0 DHCP-Client-Hardware-Address = 00:0c:29:a6:a0:e7 DHCP-Message-Type += DHCP-Discover DHCP-Parameter-Request-List += DHCP-Subnet-Mask DHCP-Parameter-Request-List += DHCP-Broadcast-Address DHCP-Parameter-Request-List += DHCP-Time-Offset DHCP-Parameter-Request-List += DHCP-Router-Address DHCP-Parameter-Request-List += DHCP-Domain-Name DHCP-Parameter-Request-List += DHCP-Domain-Name-Server DHCP-Parameter-Request-List += DHCP-Domain-Search DHCP-Parameter-Request-List += DHCP-Hostname DHCP-Parameter-Request-List += DHCP-NETBIOS-Name-Servers DHCP-Parameter-Request-List += DHCP-NETBIOS DHCP-Parameter-Request-List += DHCP-Interface-MTU-Size DHCP-Parameter-Request-List += DHCP-Classless-Static-Route DHCP-Parameter-Request-List += DHCP-NTP-Servers Trying sub-section dhcp DHCP-Discover {...} (0) group DHCP-Discover { (0) - entering group DHCP-Discover {...} (0) update control { (0) } # update control = noop (0) policy dhcp_sqlippool.post-auth { (0) - entering policy dhcp_sqlippool.post-auth {...} (0) update request { (0) expand: 'DHCP-%{DHCP-Client-Hardware-Address}' -> 'DHCP-00:0c:29:a6:a0:e7' (0) expand: '%{DHCP-Client-Hardware-Address}' -> '00:0c:29:a6:a0:e7' (0) expand: '%{DHCP-Gateway-IP-Address}' -> '0.0.0.0' (0) expand: '%{%{DHCP-Gateway-IP-Address}:-127.0.0.1}' -> '0.0.0.0' (0) } # update request = noop rlm_sql (sql): Reserved connection (4) (0) dhcp_sqlippool : expand: '%{User-Name}' -> 'DHCP-00:0c:29:a6:a0:e7' (0) dhcp_sqlippool : SQL-User-Name updated (0) dhcp_sqlippool : expand: 'START TRANSACTION' -> 'START TRANSACTION' rlm_sql (sql): Executing query: 'START TRANSACTION' rlm_sql_oracle: execute query failed in sql_query: ORA-00900: invalid SQL statement rlm_sql_oracle: OCI_SERVER_NORMAL rlm_sql (sql): Database query error: 'ORA-00900: invalid SQL statement ' sqlippool_command: database query error in: 'START TRANSACTION' (0) dhcp_sqlippool : expand: 'UPDATE radippool SET nasipaddress = '', pool_key = '0', callingstationid = '', username = '', expiry_time = current_timestamp - INTERVAL '1' second(1) WHERE expiry_time <= current_timestamp - INTERVAL '1' second(1)' -> 'UPDATE radippool SET nasipaddress = '', pool_key = '0', callingstationid = '', username = '', expiry_time = current_timestamp - INTERVAL '1' second(1) WHERE expiry_time <= current_timestamp - INTERVAL '1' second(1)' rlm_sql (sql): Executing query: 'UPDATE radippool SET nasipaddress = '', pool_key = '0', callingstationid = '', username = '', expiry_time = current_timestamp - INTERVAL '1' second(1) WHERE expiry_time <= current_timestamp - INTERVAL '1' second(1)' (0) dhcp_sqlippool : escape: 'test_ip_pool' -> 'test_ip_pool' (0) dhcp_sqlippool : escape: 'DHCP-00:0c:29:a6:a0:e7' -> 'DHCP-00:0c:29:a6:a0:e7' (0) dhcp_sqlippool : escape: '00:0c:29:a6:a0:e7' -> '00:0c:29:a6:a0:e7' (0) dhcp_sqlippool : expand: 'SELECT framedipaddress FROM radippool WHERE pool_name = '%{control:Pool-Name}' AND expiry_time < current_timestamp AND rownum <= 1 ORDER BY CASE WHEN username = '%{User-Name}' THEN 0 ELSE 1 END, CASE WHEN callingstationid = '%{Calling-Station-Id}' THEN 0 ELSE 1 END, expiry_time FOR UPDATE' -> 'SELECT framedipaddress FROM radippool WHERE pool_name = 'test_ip_pool' AND expiry_time < current_timestamp AND rownum <= 1 ORDER BY CASE WHEN username = 'DHCP-00:0c:29:a6:a0:e7' THEN 0 ELSE 1 END, CASE WHEN callingstationid = '00:0c:29:a6:a0:e7' THEN 0 ELSE 1 END, expiry_time FOR UPDATE' rlm_sql (sql): Executing query: 'SELECT framedipaddress FROM radippool WHERE pool_name = 'test_ip_pool' AND expiry_time < current_timestamp AND rownum <= 1 ORDER BY CASE WHEN username = 'DHCP-00:0c:29:a6:a0:e7' THEN 0 ELSE 1 END, CASE WHEN callingstationid = '00:0c:29:a6:a0:e7' THEN 0 ELSE 1 END, expiry_time FOR UPDATE' (0) dhcp_sqlippool : escape: '0.0.0.0' -> '0.0.0.0' (0) dhcp_sqlippool : escape: '00:0c:29:a6:a0:e7' -> '00:0c:29:a6:a0:e7' (0) dhcp_sqlippool : escape: '00:0c:29:a6:a0:e7' -> '00:0c:29:a6:a0:e7' (0) dhcp_sqlippool : escape: 'DHCP-00:0c:29:a6:a0:e7' -> 'DHCP-00:0c:29:a6:a0:e7' (0) dhcp_sqlippool : expand: 'UPDATE radippool SET nasipaddress = '%{NAS-IP-Address}', pool_key = '%{DHCP-Client-Hardware-Address}', callingstationid = '%{Calling-Station-Id}', username = '%{User-Name}', expiry_time = current_timestamp + INTERVAL '300' SECOND(1) WHERE framedipaddress = '10.99.0.11' AND expiry_time IS NULL' -> 'UPDATE radippool SET nasipaddress = '0.0.0.0', pool_key = '00:0c:29:a6:a0:e7', callingstationid = '00:0c:29:a6:a0:e7', username = 'DHCP-00:0c:29:a6:a0:e7', expiry_time = current_timestamp + INTERVAL '300' SECOND(1) WHERE framedipaddress = '10.99.0.11' AND expiry_time IS NULL' rlm_sql (sql): Executing query: 'UPDATE radippool SET nasipaddress = '0.0.0.0', pool_key = '00:0c:29:a6:a0:e7', callingstationid = '00:0c:29:a6:a0:e7', username = 'DHCP-00:0c:29:a6:a0:e7', expiry_time = current_timestamp + INTERVAL '300' SECOND(1) WHERE framedipaddress = '10.99.0.11' AND expiry_time IS NULL' (0) dhcp_sqlippool : Allocated IP 10.99.0.11 [0b00630a] (0) dhcp_sqlippool : expand: 'COMMIT' -> 'COMMIT' rlm_sql (sql): Executing query: 'COMMIT' rlm_sql (sql): Released connection (4) rlm_sql (sql): Closing connection (0): Too many free connections (5 > 3) rlm_sql_mysql: Socket destructor called, closing socket (0) dhcp_sqlippool : expand: 'DHCP: Allocated IP: %{reply:Framed-IP-Address} from %{control:Pool-Name} (did %{Called-Station-Id} cli %{Calling-Station-Id} port %{NAS-Port} user %{User-Name})' -> 'DHCP: Allocated IP: 10.99.0.11 from test_ip_pool (did cli 00:0c:29:a6:a0:e7 port user DHCP-00:0c:29:a6:a0:e7)' DHCP: Allocated IP: 10.99.0.11 from test_ip_pool (did cli 00:0c:29:a6:a0:e7 port user DHCP-00:0c:29:a6:a0:e7) (0) [dhcp_sqlippool] = ok (0) ? if (ok) (0) ? Evaluating (ok) -> TRUE (0) ? if (ok) -> TRUE (0) if (ok) { (0) - entering if (ok) {...} (0) update reply { (0) expand: '%{reply:Framed-IP-Address}' -> '10.99.0.11' (0) } # update reply = ok (0) - if (ok) returns ok (0) - policy dhcp_sqlippool.post-auth returns ok (0) update reply { (0) } # update reply = ok (0) ? if (DHCP-Gateway-IP-Address == 0.0.0.0) (0) ? Evaluating (DHCP-Gateway-IP-Address == 0.0.0.0) -> TRUE (0) ? if (DHCP-Gateway-IP-Address == 0.0.0.0) -> TRUE (0) if (DHCP-Gateway-IP-Address == 0.0.0.0) { (0) - entering if (DHCP-Gateway-IP-Address == 0.0.0.0) {...} (0) update reply { (0) } # update reply = ok (0) - if (DHCP-Gateway-IP-Address == 0.0.0.0) returns ok (0) DHCP: Reply will be broadcast Sending DHCP-Offer of id 3f1a9769 to 255.255.255.255:68 (0) Finished request 0. Waking up in 0.2 seconds. Waking up in 4.7 seconds. (0) Cleaning up request packet ID 1058707305 with timestamp +9 Ready to process requests. Signalled to terminate Exiting normally. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html