ORA-28040 No Matching Authentication Protocol - Client Security Guide

ORA-28040: No Matching Authentication Protocol

Error Overview

Error Text: ORA-28040: No matching authentication protocol

This error occurs when Oracle client and server cannot agree on a compatible authentication protocol. It’s commonly seen when connecting from older Oracle clients to newer Oracle Database versions, or when security policies restrict certain authentication methods. This error became more prominent with Oracle’s enhanced security requirements in recent versions.

Understanding Authentication Protocols

Oracle Authentication Methods

Oracle supports several authentication protocols with different security levels:

1. Password-based Authentication

   • O3LOGON (Oracle 10g and later) - More secure
   • O5LOGON (Oracle 11g and later) - Enhanced security
   • Legacy protocols (Pre-10g) - Less secure, often disabled

2. Strong Authentication

   • Kerberos - Network authentication protocol
   • PKI - Public Key Infrastructure
   • RADIUS - Remote Authentication Dial-In User Service

3. Protocol Versions

   • 12c - SQLNET.ALLOWED_LOGON_VERSION_SERVER/CLIENT
   • 11g - SEC_CASE_SENSITIVE_LOGON
   • 10g - Enhanced password verification

Root Causes

1. Client-Server Version Mismatch

• Older Oracle client connecting to newer database
• Database security policies blocking older protocols
• Client doesn’t support newer authentication methods

2. Security Policy Configuration

• SQLNET.ALLOWED_LOGON_VERSION set too restrictively
• SEC_CASE_SENSITIVE_LOGON parameter conflicts
• Authentication protocol explicitly disabled

3. Network Configuration Issues

• sqlnet.ora settings blocking protocols
• Firewall or network security appliances interfering
• Missing or misconfigured authentication adapters

4. Password Policy Conflicts

• Case-sensitive password settings
• Password verification function requirements
• Expired or locked authentication methods

Diagnostic Queries

Check Database Version and Configuration

-- Database version and authentication settings
SELECT * FROM v$version;

-- Check authentication parameters
SHOW PARAMETER sec_case_sensitive_logon;
SHOW PARAMETER sec_max_failed_login_attempts;

-- Check allowed logon versions (12c+)
SELECT name, value, description
FROM v$parameter
WHERE name LIKE '%logon%version%'
   OR name LIKE '%auth%'
ORDER BY name;

-- User authentication information
SELECT username, account_status, authentication_type,
       password_versions, created, expiry_date
FROM dba_users
WHERE username = 'TARGET_USER';

Analyze Connection Attempts

-- Check recent authentication failures
SELECT username, timestamp, action_name, returncode,
       client_program_name, client_identifier
FROM dba_audit_trail
WHERE action_name = 'LOGON'
  AND returncode = 28040
  AND timestamp > SYSDATE - 1
ORDER BY timestamp DESC;

-- Current session authentication info
SELECT username, osuser, program, machine,
       authentication_type, network_protocol
FROM v$session
WHERE username IS NOT NULL
ORDER BY logon_time DESC;

Check Network Configuration

-- Check listener services and protocols
SELECT name, pdb, network_name, con_id
FROM v$services
ORDER BY name;

-- Active listener endpoints
SELECT endpoint, protocol, service_name
FROM v$dispatcher
UNION ALL
SELECT '(local)' as endpoint, 'bequeath' as protocol,
       'local connection' as service_name
FROM dual;

Resolution Strategies

1. Client-Side Solutions

Update sqlnet.ora (Client)

# Client sqlnet.ora location examples:
# $ORACLE_HOME/network/admin/sqlnet.ora
# $TNS_ADMIN/sqlnet.ora

# Allow older authentication (temporary fix)
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 8

# For Oracle 19c clients connecting to older databases
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 10

# Enable specific authentication methods
SQLNET.AUTHENTICATION_SERVICES = (ALL)
SQLNET.FALLBACK_AUTHENTICATION = TRUE

Client Configuration Examples

# For Oracle 12c+ client connecting to 11g database
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 10
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10

# For mixed environment compatibility
SQLNET.AUTHENTICATION_SERVICES = (BEQ, NTS, KERBEROS5)
SQLNET.KERBEROS5_CONF = /etc/krb5.conf
SQLNET.KERBEROS5_REALMS = /etc/krb5.realms

2. Server-Side Solutions

Database Parameter Changes

-- Allow older client versions (use carefully)
ALTER SYSTEM SET sec_case_sensitive_logon = FALSE SCOPE=BOTH;

-- For 12c+ databases, allow older logon versions
ALTER SYSTEM SET sqlnet_allowed_logon_version_server = 10 SCOPE=BOTH;
ALTER SYSTEM SET sqlnet_allowed_logon_version_client = 10 SCOPE=BOTH;

-- Check current settings
SHOW PARAMETER sec_case_sensitive_logon;
SHOW PARAMETER sqlnet_allowed_logon_version;

Server sqlnet.ora Configuration

# Server-side sqlnet.ora ($ORACLE_HOME/network/admin/sqlnet.ora)

# Allow compatible authentication versions
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 10

# Enable multiple authentication methods
SQLNET.AUTHENTICATION_SERVICES = (ALL)

# For debugging authentication issues
DIAG_ADR_ENABLED = OFF
LOG_DIRECTORY_SERVER = /u01/oracle/logs
LOG_FILE_SERVER = server.log
TRACE_DIRECTORY_SERVER = /u01/oracle/traces
TRACE_FILE_SERVER = server.trc
TRACE_LEVEL_SERVER = 16

3. User Account Solutions

Reset User Authentication

-- Check user password versions
SELECT username,
       DECODE(password, 'EXTERNAL', 'EXTERNAL',
              DECODE(password, NULL, 'NONE', 'PASSWORD')) as auth_type,
       password_versions
FROM sys.user$
WHERE name = 'TARGET_USER';

-- Reset password to generate compatible hash
ALTER USER target_user IDENTIFIED BY new_password;

-- For case-sensitive password issues
ALTER USER target_user IDENTIFIED BY "CaseSensitivePassword";

-- Unlock account if locked due to failed attempts
ALTER USER target_user ACCOUNT UNLOCK;

Create Compatible User Profile

-- Create or modify profile for compatibility
CREATE PROFILE compatible_profile LIMIT
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL
  FAILED_LOGIN_ATTEMPTS 10
  PASSWORD_LOCK_TIME 1;

-- Assign profile to user
ALTER USER target_user PROFILE compatible_profile;

4. Network Configuration

Listener Configuration

# listener.ora configuration for compatibility
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb.domain.com)
      (ORACLE_HOME = /u01/app/oracle/product/19.0.0/dbhome_1)
      (SID_NAME = mydb)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server.domain.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

# Enable authentication debugging
LOGGING_LISTENER = ON
LOG_DIRECTORY_LISTENER = /u01/oracle/logs
LOG_FILE_LISTENER = listener.log
TRACE_DIRECTORY_LISTENER = /u01/oracle/traces
TRACE_FILE_LISTENER = listener.trc
TRACE_LEVEL_LISTENER = 16

TNS Configuration

# tnsnames.ora entry with authentication options
MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = mydb.domain.com)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
    (SECURITY =
      (SSL_SERVER_CERT_DN = "CN=server.domain.com,OU=IT,O=Company,C=US")
      (AUTHENTICATION_SERVICES = (TCPS))
    )
  )

Troubleshooting Steps

1. Enable Diagnostic Tracing

# Client-side tracing
export TNS_ADMIN=/path/to/admin
echo "TRACE_LEVEL_CLIENT = 16" >> $TNS_ADMIN/sqlnet.ora
echo "TRACE_DIRECTORY_CLIENT = /tmp/trace" >> $TNS_ADMIN/sqlnet.ora
echo "TRACE_FILE_CLIENT = client.trc" >> $TNS_ADMIN/sqlnet.ora

# Connect and check trace file
sqlplus user/pass@mydb
# Review /tmp/trace/client.trc for authentication details

2. Test Authentication Methods

-- Test connection with different methods
CONNECT user/password@mydb
CONNECT user/"password"@mydb  -- Case-sensitive
CONNECT /@mydb                -- OS authentication
CONNECT user@mydb             -- External authentication

3. Verify Network Connectivity

# Test basic connectivity
telnet server.domain.com 1521
nc -v server.domain.com 1521

# Test TNS resolution
tnsping mydb

# Check listener status
lsnrctl status
lsnrctl services

Security Best Practices

1. Gradual Security Migration

-- Phase 1: Allow both old and new protocols
ALTER SYSTEM SET sqlnet_allowed_logon_version_server = 10;

-- Phase 2: Update all clients
-- Upgrade Oracle client software to compatible versions

-- Phase 3: Tighten security
ALTER SYSTEM SET sqlnet_allowed_logon_version_server = 12;

-- Phase 4: Enable case-sensitive passwords
ALTER SYSTEM SET sec_case_sensitive_logon = TRUE;

2. Authentication Policy Template

-- Create secure password profile
CREATE PROFILE secure_profile LIMIT
  PASSWORD_LIFE_TIME 90
  PASSWORD_GRACE_TIME 7
  PASSWORD_REUSE_TIME 365
  PASSWORD_REUSE_MAX 10
  FAILED_LOGIN_ATTEMPTS 5
  PASSWORD_LOCK_TIME 1
  PASSWORD_VERIFY_FUNCTION ora12c_strong_verify_function;

-- Apply to users
ALTER USER application_user PROFILE secure_profile;

3. Monitoring Authentication

-- Create authentication monitoring view
CREATE OR REPLACE VIEW v_auth_failures AS
SELECT username, timestamp, action_name, returncode,
       client_program_name, client_identifier, os_username,
       userhost, terminal
FROM dba_audit_trail
WHERE action_name = 'LOGON'
  AND returncode IN (1017, 28040, 28000)
  AND timestamp > SYSDATE - 30;

-- Check for authentication patterns
SELECT returncode, COUNT(*) as failure_count,
       MIN(timestamp) as first_failure,
       MAX(timestamp) as last_failure
FROM v_auth_failures
GROUP BY returncode
ORDER BY failure_count DESC;

Version-Specific Solutions

Oracle 19c/21c to 11g

# Client sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 10
SQLNET.AUTHENTICATION_SERVICES = (ALL)

Oracle 12c Client to 19c Database

-- Database parameter
ALTER SYSTEM SET sqlnet_allowed_logon_version_client = 11;

Mixed Environment Configuration

# Flexible sqlnet.ora for mixed environments
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 10
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 10
SQLNET.AUTHENTICATION_SERVICES = (BEQ, NTS, ALL)
SQLNET.FALLBACK_AUTHENTICATION = TRUE

Related Errors

• ORA-01017 - Invalid username/password
• ORA-28000 - Account locked
• ORA-28001 - Password expired
• ORA-12154 - TNS could not resolve name
• ORA-12514 - TNS listener does not know of service

Emergency Response

Quick Workaround

-- Immediate fix (less secure)
ALTER SYSTEM SET sec_case_sensitive_logon = FALSE;
ALTER SYSTEM SET sqlnet_allowed_logon_version_server = 8;

Permanent Solution Steps

1. Identify client versions in environment
2. Plan authentication strategy based on security requirements
3. Test compatibility in non-production environment
4. Implement gradual migration to newer authentication
5. Monitor and adjust settings as needed

Emergency sqlnet.ora

# Temporary compatibility settings
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 8
SQLNET.AUTHENTICATION_SERVICES = (ALL)
SQLNET.FALLBACK_AUTHENTICATION = TRUE

# Enable detailed logging
DIAG_ADR_ENABLED = OFF
LOG_DIRECTORY_CLIENT = /tmp/oracle_logs
LOG_FILE_CLIENT = client.log
TRACE_DIRECTORY_CLIENT = /tmp/oracle_traces
TRACE_FILE_CLIENT = client.trc
TRACE_LEVEL_CLIENT = 16

The key to resolving ORA-28040 is balancing security requirements with client compatibility while planning a migration path to more secure authentication methods.