Analysis and Solutions for ORA-01017 Error When Connecting from Oracle 9i Client to 11g Database

Problem Background and Phenomenon Description

In cross-version connection scenarios between Oracle databases, the ORA-01017 invalid username/password error frequently occurs when connecting from a 9i client (version 9.2.0.1) to an 11g database (version 11.2.0.1.0). Users confirm that credentials are correct, but connections to specific schemas still fail, indicating that the root cause lies in compatibility differences between versions rather than simple credential errors.

Core Cause Analysis: Password Case Sensitivity

Oracle 11g introduced a significant security feature—password case sensitivity. Prior to 11g, Oracle databases treated passwords as case-insensitive, meaning "PASSWORD" and "password" were considered the same. However, starting from 11g, password case sensitivity is enabled by default, creating compatibility issues for older clients.

When a 9i client attempts to connect to an 11g database, the client may not properly handle the case-sensitive password verification process. Even if the credentials sent by the client are logically correct, the server may reject the authentication request due to differences in case handling mechanisms.

Primary Solutions

Disabling Password Case Sensitivity

The most direct solution is to disable the password case sensitivity feature on the database server side. This can be achieved by modifying system parameters:

ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

After executing this command, affected user passwords need to be reset. Below is a complete operational example:

-- Connect to database with SYSDBA privileges
CONNECT / AS SYSDBA

-- Disable password case sensitivity
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

-- Reset user password (ensure no quotes are used)
ALTER USER target_user IDENTIFIED BY new_password;

It's important to note that disabling this security feature may reduce system security, so careful evaluation is required in production environments.

Best Practices for Password Reset

When resetting passwords, double quotes should be avoided as they affect the actual stored value of the password. The following code demonstrates the correct password reset method:

-- Correct password reset method
ALTER USER username IDENTIFIED BY password123;

-- Method to avoid (using double quotes)
ALTER USER username IDENTIFIED BY "password123";

Passwords set with double quotes require quotes during verification, which is often an overlooked detail.

In-depth Analysis: Password Version Compatibility

In more complex scenarios, particularly involving Oracle 12c and later versions, password version compatibility must also be considered. User password versions can be checked by querying the data dictionary:

SELECT USERNAME, ACCOUNT_STATUS, PASSWORD_VERSIONS 
FROM dba_users 
WHERE USERNAME = 'TARGET_USER';

The query result might display something like:

USERNAME          ACCOUNT_STATUS    PASSWORD_VERSIONS 
---------------   --------------    -----------------
target_user       OPEN              11G 12C

If the password version doesn't include 10G, even with SEC_CASE_SENSITIVE_LOGON = FALSE set, 9i clients may still be unable to connect. In this case, the sqlnet.ora configuration file needs modification:

-- Add to sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

After modifying the configuration, the database needs to be restarted, and user passwords must be reset. The password version should then include 10G compatibility.

Practical Cases and Troubleshooting

Special Character Handling

When using variables in connection strings, special characters (such as #) may cause authentication issues. The following example demonstrates the correct method for building connection strings:

-- Hard-coded password (may work)
Data Source=orcl;User Id=username;Password=pass#word;

-- Using variables (requires special handling)
string connectionString = $"Data Source=orcl;User Id={username};Password={EscapePassword(password)};";

Appropriate password escaping functions need to be implemented to handle special characters.

Client Configuration Verification

Ensuring correct client configuration is an important step in resolving connection issues. Check the following aspects:

• Service name configuration in tnsnames.ora file
• ORACLE_HOME environment variable settings
• Network connectivity
• Firewall and port configurations

System-level Authentication Configuration

In some cases, system-level authentication configuration may affect database login. Check authentication service settings in sqlnet.ora:

SQLNET.AUTHENTICATION_SERVICES = (NONE)

In Windows environments, if using operating system authentication, client tools may need to be run with administrator privileges.

Comprehensive Solution Implementation Steps

1. Identify problem scope: Determine if specific users or all users cannot connect
2. Check database version and configuration: Verify SEC_CASE_SENSITIVE_LOGON parameter settings
3. Verify password version compatibility: Query dba_users view to check PASSWORD_VERSIONS
4. Implement fixes: Choose between disabling case sensitivity or adjusting password version based on specific situation
5. Test connections: Retest connections using modified configurations
6. Monitor and validate: Ensure modifications don't affect other system functions

Security Considerations and Best Practices

While resolving compatibility issues, balance between security and usability must be maintained:

• Exercise caution when disabling security features in production environments
• Consider upgrading client versions for better security and compatibility
• Implement appropriate password policies, even in case-insensitive scenarios
• Regularly audit and monitor database access logs

Through systematic analysis and targeted solutions, authentication issues when connecting from Oracle 9i clients to 11g databases can be effectively resolved while ensuring system stability and security.