The ORA-01017 error, indicating an invalid username or password, is a common issue encountered when attempting to connect to an Oracle database.

This error can arise in various scenarios, such as when connecting an older client (like Oracle 9i) to a newer database server (like Oracle 11g, 12c, or 19c), or when using different development tools and drivers. Even if credentials seem correct, underlying configuration mismatches or specific database features can trigger this authentication failure.

This article explores the typical causes of the ORA-01017 error and provides a range of solutions to address it.

Understanding the ORA-01017 Error: Key Causes

Several factors can lead to the ORA-01017 error, even when you are confident in the entered username and password. The primary reasons often revolve around:

• Password Case Sensitivity: Oracle Database 11g Release 1 introduced case-sensitive passwords by default. Older clients or connection methods might not handle case sensitivity in the same way, often uppercasing passwords, leading to a mismatch.
• Password Versioning and Logon Protocols: Different Oracle versions use distinct password hashing and encryption mechanisms. A mismatch between the client’s supported logon protocol and the server’s allowed versions can result in connection failures.
• Special Characters in Passwords: Certain special characters within passwords can be misinterpreted by command-line interfaces, connection strings, or drivers if not properly quoted or escaped.
• Client Driver or Tool Configuration: Issues with JDBC/ODBC drivers (e.g., outdated versions), or specific settings within database management tools (like SQL Developer, TOAD) can interfere with the authentication process.
• Account Status: The user account might be locked, expired, or configured in a way that prevents login from a particular client.
• Password File Integrity: In specific contexts, such as RMAN connections, a stale or corrupted Oracle password file can cause authentication failures.
• Connection String Errors: Incorrect service names or other parameters in the TNS connect string can sometimes manifest as a ORA-01017 error.
• System-Level Policies (e.g., FIPS): For certain client versions (like Oracle 12c on Windows), enabled Federal Information Processing Standards (FIPS) can conflict with Oracle’s encryption algorithms, leading to logon denial.

Read: How to Fix ORA-12514: Oracle Listener Service Name Mismatch

Solutions to Resolve ORA-01017 Errors

A variety of approaches can be employed to troubleshoot and fix the ORA-01017 error. These solutions target different aspects of the database and client configuration.

1. Addressing Password Case Sensitivity

Given that Oracle 11g and later versions enforce case-sensitive passwords by default, this is a common culprit, especially when interacting with older clients.

Disable Server-Side Case Sensitivity

You can configure the database to ignore password case, reverting to the behavior of older Oracle versions.

Steps:

1. Connect to the database as a user with SYSDBA or SYSOPER privileges.
2. Execute the following SQL command:

   ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

3. It is crucial to then reset the password for the affected user(s). This ensures the password is stored in a way that aligns with the new case-insensitive setting.

   ALTER USER #your_user_name# IDENTIFIED BY #user_password#;

This system alteration makes the database treat passwords as case-insensitive, accommodating clients that might alter password casing (e.g., by converting to uppercase) before transmission.

Use Quoted Passwords from Clients

If maintaining case sensitivity on the server is preferred or required, ensure the client transmits the password with its exact casing. This often involves enclosing the password in double quotes.

Example (SQL*Plus):

sqlplus #your_user_name#/"YourPasswordWithCase"@#tns_alias#

Example (C# Connection String):

In connection strings, ensure the password part correctly includes quotes if necessary, such as Password=\"MyComplexPwd\";. Some drivers or client libraries might require escaping the quotes, e.g., Password=\\\"MyComplexPwd\\\";.

Quoting the password signals to the client tool or driver to preserve the exact case and any special characters, preventing unintended modifications.

2. Managing Password Versions and Logon Protocols

For compatibility between different Oracle client and server versions, especially when older clients connect to newer databases (e.g., 10g client to 12c/19c database), adjustments to logon version parameters may be necessary.

Adjust SQLNET.ALLOWED_LOGON_VERSION_SERVER

This parameter in the database server’s sqlnet.ora file controls the minimum authentication protocol allowed.

Steps:

1. Locate and edit the sqlnet.ora file in your database server’s $ORACLE_HOME/network/admin (or equivalent) directory.
2. Add or modify the following line. The value 8 is often used for broad compatibility, but you might need 10, 11, or 12 depending on your specific client requirements.

   SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

3. Restart the Oracle database instance and the listener for the changes to take effect.
4. After the restart, it’s recommended to change or expire the password for existing users. This ensures their password verifiers are updated according to the new setting.

   ALTER USER #your_user_name# IDENTIFIED BY #new_user_password#;

   Alternatively, expire the password:

   ALTER USER #your_user_name# PASSWORD EXPIRE;

Verification: You can check the PASSWORD_VERSIONS for a user to see if older versions (like 10G) are now present:

SELECT USERNAME, ACCOUNT_STATUS, PASSWORD_VERSIONS FROM DBA_USERS WHERE USERNAME = '#YOUR_USER_NAME_IN_CAPS#';

The output should reflect the changes. For example:

This setting allows the server to accept connections using older, less secure authentication protocols, which might be necessary for legacy clients that do not support the more modern, secure protocols enforced by newer Oracle databases.

Read: How to Install MongoDB on Ubuntu 22.04

3. Account and Password Management

Resetting User Passwords

Sometimes, simply resetting a user’s password can resolve elusive ORA-01017 errors, even if the password is reset to the same value. This can refresh the password’s internal state.

Command:

ALTER USER #target_user_account# IDENTIFIED BY #fresh_credentials#;

Oracle 11g and 12c may suggest or enforce more complex passwords (alphanumeric with special characters). If you suspect password complexity rules are in play, ensure the new password meets these criteria, for instance:

ALTER USER #target_user_account# IDENTIFIED BY "P@$$wOrd2024!";

This can resolve issues related to password hash mismatches, synchronization problems after system changes (like altering SEC_CASE_SENSITIVE_LOGON), or minor corruptions in the stored password verifier.

Unlocking User Accounts

The target user account might be locked due to multiple failed login attempts or administrative action.

Command (example for HR schema):

ALTER USER HR ACCOUNT UNLOCK;

If you also need to set/reset the password and assign tablespaces simultaneously:

ALTER USER HR IDENTIFIED BY #hr_password# DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;

Why this works: Unlocking the account makes it available for login attempts again.

Handling Usernames Created with Quotes

If a user account was initially created with a quoted identifier (e.g., CREATE USER "myuser" ...), then all subsequent references to this user, including in connection strings or client configurations, must also use the exact cased, quoted identifier.

Example (Connection String):User Id=\"myuser\";...

If this becomes cumbersome, it might be simpler to drop and recreate the user without quotes, using an unquoted, uppercase name by convention.

DROP USER "myuser";
CREATE USER MYUSER IDENTIFIED BY #new_password_value#;
GRANT #necessary_privileges# TO MYUSER;

Oracle treats quoted identifiers as case-sensitive and literal, while unquoted identifiers are typically converted to uppercase. Consistency is key.

4. Client-Side and Connection Configuration

Handling Special Characters in Passwords

If passwords contain special characters (e.g., $, !, @), they might be misinterpreted by the operating system shell or the connection mechanism.

Example (UNIX shell with `isql`):

If a password contains a $, the shell might try to interpret it as a variable. Wrap the entire credential string in single quotes:

isql '#user_name#/pa$$word@#tns_name#'

During troubleshooting, temporarily using a password without special characters can help isolate whether this is the cause.

Proper quoting or escaping prevents the client environment from misinterpreting special characters before they are passed to the Oracle client driver.

Upgrading OJDBC Drivers

For Java applications, an outdated OJDBC driver (e.g., ojdbc14.jar) might be incompatible with newer database versions.

Action: Replace the older OJDBC JAR file (e.g., ojdbc14.jar) with a more recent version compatible with your database (e.g., ojdbc8.jar or ojdbc11.jar for Oracle 12c/19c/21c). Ensure the application’s classpath is updated accordingly.

Why this works: Newer drivers include support for updated authentication protocols and features of more recent Oracle database versions.

Correcting Connection String Service Name

Especially in environments with Pluggable Databases (PDBs), ensure your connection string uses the correct service name for the target PDB, not the Container Database (CDB).

Verification: Check the SERVICE_NAMES parameter on the server or consult your tnsnames.ora file for the correct PDB service name.

Why this works: Connecting to the wrong service, even with valid credentials for that service, will fail if the intended schema/user does not exist or is not accessible there. While typically leading to other errors, it can sometimes present as ORA-01017 if the misdirection causes an authentication protocol issue at a more fundamental level.

Disabling FIPS (for Oracle 12c Client on Windows)

A known issue with some Oracle 12c clients on Windows involves a conflict with enabled FIPS (Federal Information Processing Standards) mode, due to Oracle’s AES encryption algorithm implementation.

Fix:

1. Open the Windows Registry Editor (regedit).
2. Navigate to HKLM\System\CurrentControlSet\Control\Lsa\FIPSAlgorithmPolicy\.
3. Set the Enabled DWORD value to 0.
4. A system reboot might be necessary.

This disables the FIPS enforcement that conflicts with the Oracle 12c client’s handling of password encryption, thereby working around the bug.

5. Tool-Specific Workarounds

Certain database tools have specific settings or behaviors that can lead to ORA-01017.

Oracle SQL Developer: Proxy Authentication

If “Use DB Proxy Authentication” is inadvertently enabled in the connection settings (Proxy User tab) and you are not intending to use proxy authentication, this can cause login failure.

Action: Edit the connection properties in SQL Developer, go to the “Proxy User” tab (or similar, depending on version), and uncheck this option.

TOAD: Password Entry

Some versions of TOAD might handle passwords differently if typed directly into the main connection dialog versus being entered in a subsequent pop-up password prompt. This can be related to case sensitivity or special character handling within TOAD’s interface.

Action: Try connecting without typing the password in the initial connection window. Let TOAD prompt for the password separately and enter it there.

TablePlus (on M1 Mac): Case Handling

A specific behavior noted with TablePlus on M1 Macs is that direct typing of passwords into its fields might result in unintended case conversion (e.g., to lowercase).

Action: Type the password in a separate text editor, copy it, and then paste it into the password field in TablePlus.

DataGrip: Connecting as SYSDBA

When connecting as SYSDBA in DataGrip, ensure the username field is correctly formatted.

Action: Instead of just SYSDBA, use sys as sysdba in the username field.

6. Recreating Oracle Password File (RMAN Context)

If ORA-01017 errors occur specifically when RMAN or other tools try to connect to an auxiliary database, even though sqlplus / as sysdba connections work, the password file might be stale or corrupted.

Solution: Recreate the password file for the target and/or auxiliary database.

Command (example):

orapwd file=orapw#your_sid# password=#secure_password# entries=#max_admin_users# force=y

Replace #your_sid# with the actual System Identifier, #secure_password# with the desired SYS password, and #max_admin_users# with the maximum number of entries.

This refreshes the password file used for privileged remote connections, resolving inconsistencies that might prevent tools like RMAN from authenticating correctly.

Verification After Applying Solutions

After implementing any of the suggested solutions, the primary method of verification is to attempt the connection again using the client, application, or tool that was previously failing. A successful login without the ORA-01017 error indicates the problem has been resolved.

For changes like modifying SQLNET.ALLOWED_LOGON_VERSION_SERVER, you can also query DBA_USERS as shown previously to verify changes in PASSWORD_VERSIONS.

Conclusion

While seemingly straightforward, the ORA-01017 “Invalid Username/Password” error in Oracle can stem from a complex interplay of server settings, client configurations, password characteristics, and version incompatibilities.

We have investigated potential causes such as password case sensitivity, logon protocol versions, special character handling, and tool-specific behaviors.