What causes "[username] not found with SQL query" in apereo/cas?

Verify the username exists: run the configured SQL manually against the target datasource.

apereo/cas · error · AccountNotFoundException

[username] not found with SQL query

Error message

[username] not found with SQL query

What it means

Spring's IncorrectResultSizeDataAccessException with actualSize 0 means the SQL query (sqlFindByUsername) matched no rows. The handler translates that into AccountNotFoundException, i.e. the username does not exist in the database according to the configured query.

Solutions

Verify the username exists: run the configured SQL manually against the target datasource.
Check cas.authn.jdbc.query[].sql selects the correct table and username column; fix typos.
Confirm datasource URL/credentials point to the intended database and schema (dev vs prod).
Handle case sensitivity: use LOWER(username)=LOWER(?) in the SQL or normalize input.

Example fix

// before
cas.authn.jdbc.query[0].sql=SELECT * FROM users WHERE usr=?
// after
cas.authn.jdbc.query[0].sql=SELECT * FROM users WHERE username=?

Defensive patterns

Strategy: try-catch

Validate before calling

// pre-check user existence
Integer n = jdbcTemplate.queryForObject(
    "SELECT COUNT(*) FROM users WHERE username=?", Integer.class, username);
if (n == null || n == 0) { throw new AccountNotFoundException(username); }

Try / catch

try { result = handler.authenticate(credential); }
catch (AccountNotFoundException e) { log.warn("Unknown user: {}", username); return 401_unknown_user; }

Prevention

Always smoke-test the configured SQL against the actual datasource.
Keep username comparisons case-normalized on both sides.
Validate datasource URLs per environment before startup.

When it happens

Trigger: authenticateUsernamePasswordInternal calls query(credential); getJdbcTemplate().queryForMap throws IncorrectResultSizeDataAccessException with getActualSize()==0 because the WHERE clause (username column) matched nothing.

Common situations: Typo in username; wrong table/column names in cas.authn.jdbc.query[].sql; connecting to wrong database/schema/environment; case-sensitive username comparison.

Understand the failure class

Background: "User not found", "Invalid user", and "does not exist": what missing-user lookup errors mean across Rocket.Chat, LiteLLM, Phabricator, rustfs, and pnpm — this error's family across 10 libraries.

Related errors

AI-assisted analysis of apereo/cas@e7288fc434 (2026-09-08). Data as JSON: /api/errors/ac4df585d19f027f. Report an issue: GitHub.

Appendix: source

Thrown at support/cas-server-support-jdbc-authentication/src/main/java/org/apereo/cas/jdbc/QueryDatabaseAuthenticationHandler.java:102

                val dbDisabled = dbFields.get(properties.getFieldDisabled()).toString();
                if (BooleanUtils.toBoolean(dbDisabled) || "1".equals(dbDisabled)) {
                    throw new AccountDisabledException("Account has been disabled");
                }
            }
            if (StringUtils.isNotBlank(properties.getFieldExpired()) && dbFields.containsKey(properties.getFieldExpired())) {
                val dbExpired = dbFields.get(properties.getFieldExpired()).toString();
                if (BooleanUtils.toBoolean(dbExpired) || "1".equals(dbExpired)) {
                    throw new AccountPasswordMustChangeException("Password has expired");
                }
            }

            val attributes = collectPrincipalAttributes(dbFields);
            val principal = this.principalFactory.createPrincipal(username, attributes);
            return createHandlerResult(credential, principal, new ArrayList<>());

        } catch (final IncorrectResultSizeDataAccessException e) {
            if (e.getActualSize() == 0) {
                throw new AccountNotFoundException(username + " not found with SQL query");
            }
            throw new FailedLoginException("Multiple records found for " + username);
        } catch (final DataAccessException e) {
            throw new PreventedException(e);
        }
    }

    protected Map<String, Object> query(final UsernamePasswordCredential credential) {
        val sql = SpringExpressionLanguageValueResolver.getInstance().resolve(properties.getSql());
        if (sql.contains("?")) {
            return getJdbcTemplate().queryForMap(sql, credential.getUsername());
        }
        val parameters = new LinkedHashMap<String, Object>();
        parameters.put("username", credential.getUsername());
        parameters.put("password", credential.toPassword());
        return getNamedParameterJdbcTemplate().queryForMap(sql, parameters);
    }
}

View on GitHub (pinned to e7288fc434)