sql: ERROR: rlm_sql_oracle: ORA-01403: no data found

[Vincent MARCEL]

No, sorry.

There is no connection problem and radius server get the same connection string as with sqlplus :
rlm_sql (sql): Attempting to connect to database "(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.22.97.147)(PORT=1533))(CONNECT_DATA=(SID=MEMAD)))"
rlm_sql (sql): Initialising connection pool
   pool {
        start = 4
        min = 3
        max = 32
        spare = 10
        uses = 0
        lifetime = 0
        cleanup_interval = 30
        idle_timeout = 60
        retry_delay = 1
        spread = no
   }

If radius server really got no data, how do you want it accepts known users in my authcheck_table and rejects unknown users from my authcheck_table ?
Sql is my only authorization method :
authorize {
        filter_username
        preprocess
        sql
        if(ok) {
                update control {
                        Auth-Type := Accept
                }
        } else {
                update control {
                        Auth-Type := Reject
                }
        }
        expiration
        logintime
}

And here is the result in debug mode :
(0) sql: EXPAND SELECT distinct 1,NUM_SERIE,'Cleartext-Password',NUM_SERIE,':=' FROM E_MEMA_TPE WHERE NUM_SERIE = '%{User-Name}'
(0) sql:    --> SELECT distinct 1,NUM_SERIE,'Cleartext-Password',NUM_SERIE,':=' FROM E_MEMA_TPE WHERE NUM_SERIE = 'FT10895748'
(0) sql: Executing select query: SELECT distinct 1,NUM_SERIE,'Cleartext-Password',NUM_SERIE,':=' FROM E_MEMA_TPE WHERE NUM_SERIE = 'FT10895748'
(0) sql: ERROR: Error fetching row
(0) sql: ERROR: rlm_sql_oracle: ORA-01403: no data found
(0) sql: User found in radcheck table
(0) sql: Conditional check items matched, merging assignment check items
(0) sql:   Cleartext-Password := "FT10895748"
rlm_sql (sql): Released connection (4)
rlm_sql (sql): Need 2 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (5), 1 of 31 pending slots used
(0)     [sql] = ok
(0)     if (ok) {
(0)     if (ok)  -> TRUE
(0)     if (ok)  {
(0)       update control {
(0)         Auth-Type := Accept
(0)       } # update control = noop
(0)     } # if (ok)  = noop
(0)     ... skipping else for request 0: Preceding "if" was taken
(0)     [expiration] = noop
(0)     [logintime] = noop
(0)   } # authorize = ok
(0) Found Auth-Type = Accept
(0) Auth-Type = Accept, accepting the user

Anyway :
- this error appear whatever query I put in authorize_check_query, for example "SELECT 1,'FT10895748','User-Password','FT10895748',':=' FROM DUAL" that returns a fixed row
- even in the case where data is not found (unkown user), it is not normal that radius provokes this Oracle ORA-01403 error since it is not forbidden to retrieve no data from authorize_check_query. It may say "no data found" for information but not produce an error...

Cordialement,
Vincent.

De : Alan Buxey [mailto:A.L.M.Buxey at lboro.ac.uk] Envoyé : jeudi 11 février 2016 23:29 À : FreeRadius users mailing list; Vincent MARCEL Objet : RE: sql: ERROR: rlm_sql_oracle: ORA-01403: no data found

>>Your query doesn't return data from >>the database

>False : here is the query (debug mode) >for an accepted user :

No. Sorry. It's true. Look:

(1) sql: ERROR: Error fetching row
(1) sql: ERROR: rlm_sql_oracle: ORA-01403: no data found

See. Error fetching row and no data found. Ensure your sql config had suitable access user/pass and host has required access

alan