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
- Removing the password and failed-login limits from the
DEFAULTprofile weakens the protection against brute-force attacks. Keep this change temporary and document it. ALLOWED_LOGON_VERSION_SERVER=8and=11accept older protocols: enable only the minimum needed.- User names, host names and passwords are placeholders.