LLI Wiki — My Digital Knowledge Base

Oracle 19c: legacy clients, SID connections and locked accounts

Fix ORA-12505, ORA-28040, locked or expired accounts and case-sensitive passwords on Oracle 19c.

Common connection and authentication problems on Oracle 19c with older clients or databases: connection by SID (ORA-12505), client too old (ORA-28040), locked or expired accounts, and case-sensitive passwords.

1. Connection by SID (ORA-12505)

When a client connects with a SID rather than a service name, the listener must accept SIDs as services. Add the parameter to listener.ora, then restart the listener:

USE_SID_AS_SERVICE_<listener_name> = ON

Example listener.ora (file generated by the Oracle configuration tools):

# listener.ora Network Configuration File: $ORACLE_HOME/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
USE_SID_AS_SERVICE_LISTENER=on

Reference: Oracle Support note 1644355.1 (ORA-12505).

2. Client too old (ORA-28040)

To accept a client that uses an old authentication protocol, add this to sqlnet.ora on the server side:

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

3. Locked or expired accounts

Check the limits of the DEFAULT profile and the state of the account:

SELECT resource_name, limit
FROM dba_profiles 
WHERE profile = 'DEFAULT'
AND resource_type = 'PASSWORD';

SELECT username, account_status
FROM dba_users
WHERE username = 'APP_USER';

Force a password change and unlock the account:

ALTER USER app_user PASSWORD EXPIRE;
ALTER USER app_user IDENTIFIED BY "<PASSWORD>" ACCOUNT UNLOCK;

ALTER USER monitor_user PASSWORD EXPIRE;
ALTER USER monitor_user IDENTIFIED BY "<PASSWORD>" ACCOUNT UNLOCK;

Unlock an account:

ALTER USER <your_username> ACCOUNT UNLOCK;

Remove the password lifetime and failed-login limits from the DEFAULT profile:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

List all the accounts and their status:

SELECT username, 
       account_status
  FROM dba_users;

4. Case-sensitive passwords

For case-sensitive authentication, the server must accept password versions 11g and later, which is set by SQLNET.ALLOWED_LOGON_VERSION_SERVER=11 in sqlnet.ora. Then check the stored password versions and the parameter:

SELECT USERNAME, PASSWORD_VERSIONS FROM DBA_USERS;

SHOW PARAMETER SEC_CASE_SENSITIVE_LOGON

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = TRUE;

Notes

Mastodon LLIMastodon SocialFosstodon