ORA-28547: Connection Failed - Fix Oracle Net Admin Error

ORA-28547: Connection to Server Failed, Probable Oracle Net Admin Error

Error Overview

Error Text: ORA-28547: connection to server failed, probable Oracle Net admin error

ORA-28547 occurs when a client successfully establishes a TCP connection to the Oracle listener and the listener hands off the connection to a database server process, but the authentication or connection negotiation phase fails. The error indicates a misconfiguration in the Oracle Net layer — specifically in how sqlnet.ora or the equivalent network configuration is set up on the server side.

The most common manifestation is on Windows systems where the NTS (Native Authentication) is configured or expected but cannot function correctly in the given environment. It also appears when SQLNET.AUTHENTICATION_SERVICES is set to a value that conflicts with the connection attempt method.

Common Causes

1. NTS Authentication Misconfiguration on Windows

• SQLNET.AUTHENTICATION_SERVICES = (NTS) in sqlnet.ora requires Windows OS authentication
• NTS is configured on the server but the client is not part of the same Windows domain
• Attempting to connect with a database-authenticated account when NTS is enforced

2. SQLNET.AUTHENTICATION_SERVICES Set to an Unsupported Value

• sqlnet.ora specifies an authentication method (e.g., KERBEROS5, RADIUS) that is not properly installed
• Mismatch between client and server SQLNET.AUTHENTICATION_SERVICES settings
• Authentication service plugin files missing from $ORACLE_HOME/lib or $ORACLE_HOME/bin

3. sqlnet.ora Syntax Error or Missing Required Entries

• Malformed sqlnet.ora file causes the Oracle Net layer to reject all connections
• Required parameters are commented out or set to empty values
• File corruption or encoding issue in sqlnet.ora

4. Oracle Net Service Name Resolution Fails at Connection Handoff

• The listener hands off the connection but the server process fails to initialize properly
• ORACLE_HOME on the server points to an invalid or incomplete Oracle installation
• Missing or corrupted Oracle Net libraries in $ORACLE_HOME/network/lib

5. Protocol-Specific Configuration Issues

• SSL/TLS wallet configuration is broken (WALLET_LOCATION in sqlnet.ora points to missing wallet)
• TCP.VALIDNODE_CHECKING enabled with an incomplete allowed nodes list
• SQLNET.INBOUND_CONNECT_TIMEOUT set too low, rejecting slow clients

Diagnostic Queries

Check Oracle Net Configuration Parameters

-- Server-side sqlnet parameters visible from the database:
SELECT name, value
FROM v$parameter
WHERE name IN (
  'os_authent_prefix',
  'remote_os_authent',
  'remote_os_roles'
)
ORDER BY name;

-- Security-related authentication parameters:
SELECT name, value
FROM v$parameter
WHERE name LIKE '%auth%'
   OR name LIKE '%wallet%'
   OR name LIKE '%ssl%'
ORDER BY name;

-- Check if OS authentication is configured:
SELECT
  username,
  TO_CHAR(created, 'YYYY-MM-DD') AS created
FROM dba_users
WHERE username LIKE 'OPS$%'  -- Typical OS-authenticated user prefix
   OR authentication_type = 'EXTERNAL'
ORDER BY username;

Verify Network Configuration Files

-- Find the sqlnet.ora location:
SELECT value FROM v$parameter WHERE name = 'tns_admin';

-- Check Oracle Net trace settings (useful for debugging):
SELECT name, value FROM v$parameter
WHERE name IN (
  'sqlnet.authentication_services',  -- Note: this is a sqlnet.ora param, not init.ora
  'tcp_validnode_checking'
)
ORDER BY name;

Check Listener Handoff Configuration

# On the database server, check listener.ora for security restrictions:
grep -i "secure_register\|valid_node\|ssl\|wallet" $ORACLE_HOME/network/admin/listener.ora

# Check the listener is properly connected to the database:
lsnrctl services LISTENER

# Check for listener-related errors in the listener log:
tail -100 $ORACLE_BASE/diag/tnslsnr/$HOSTNAME/listener/alert/log.xml | grep -i "error\|ora-"

Trace a Failed Connection

-- Enable SQL*Net tracing to capture detailed negotiation errors:
-- Add to $TNS_ADMIN/sqlnet.ora on the CLIENT:
-- TRACE_LEVEL_CLIENT = 16
-- TRACE_FILE_CLIENT = /tmp/sqlnet_client_trace
-- TRACE_DIRECTORY_CLIENT = /tmp

-- Then attempt the failing connection and examine /tmp/sqlnet_client_trace.trc
-- Look for entries containing: ORA-28547, NTS, AUTHENTICATION

Step-by-Step Resolution

1. Identify the sqlnet.ora Location and Contents

# On the database server:
echo "ORACLE_HOME: $ORACLE_HOME"
echo "TNS_ADMIN: $TNS_ADMIN"

# Find sqlnet.ora:
ls -la $ORACLE_HOME/network/admin/sqlnet.ora
# or if TNS_ADMIN is set:
ls -la $TNS_ADMIN/sqlnet.ora

# View the contents:
cat $ORACLE_HOME/network/admin/sqlnet.ora

2. Fix SQLNET.AUTHENTICATION_SERVICES

The most common fix on Unix/Linux systems is to set AUTHENTICATION_SERVICES to NONE or (ALL):

# Edit $ORACLE_HOME/network/admin/sqlnet.ora

# Option A: Allow all authentication methods (most permissive, works for most cases):
SQLNET.AUTHENTICATION_SERVICES = (ALL)

# Option B: Disable special authentication services (database authentication only):
SQLNET.AUTHENTICATION_SERVICES = (NONE)

# Option C: Windows — allow both NTS and database authentication:
# SQLNET.AUTHENTICATION_SERVICES = (NTS)
# Only use NTS if all clients are Windows and on the same domain.

The listener does not need to be restarted for sqlnet.ora changes to take effect — they are read at connection time. However, it is good practice to bounce listener sessions:

lsnrctl reload LISTENER

3. Fix NTS Authentication Issues on Windows

# On Windows, if NTS is the issue, either:

# Option A: Change sqlnet.ora to remove NTS requirement
# Edit %ORACLE_HOME%\network\admin\sqlnet.ora
# Change:
#   SQLNET.AUTHENTICATION_SERVICES = (NTS)
# To:
#   SQLNET.AUTHENTICATION_SERVICES = (NONE)
# or
#   SQLNET.AUTHENTICATION_SERVICES = (NTS, NONE)

# Option B: Create an OS-authenticated Oracle user (if NTS is required):
# SQL*Plus as SYSDBA:

-- Check OS_AUTHENT_PREFIX:
SHOW PARAMETER os_authent_prefix;  -- Typically 'OPS$' or ''

-- Create OS-authenticated user (if prefix is 'OPS$' and Windows user is DOMAIN\DBUSER):
CREATE USER ops$domain\\dbuser IDENTIFIED EXTERNALLY;
GRANT CREATE SESSION TO ops$domain\\dbuser;

4. Fix a Wallet Configuration Error

If WALLET_LOCATION is set in sqlnet.ora but the wallet doesn’t exist:

# Check wallet location setting in sqlnet.ora:
grep -i wallet $ORACLE_HOME/network/admin/sqlnet.ora

# Create the wallet directory if missing:
mkdir -p /u01/app/oracle/wallet
chown oracle:dba /u01/app/oracle/wallet

# Or remove/comment out the WALLET_LOCATION line if Oracle Wallet is not in use:
# WALLET_LOCATION = (SOURCE = (METHOD = FILE)(METHOD_DATA = (DIRECTORY = /wallet)))
# → Comment this out if wallet is not needed

5. Fix TCP.VALIDNODE_CHECKING

If TCP.VALIDNODE_CHECKING is enabled, the client’s IP must be in the allowed list:

# In sqlnet.ora:
# TCP.VALIDNODE_CHECKING = YES
# TCP.INVITED_NODES = (server1, 10.20.30.0/24, client1.example.com)

# Either add the client IP:
# TCP.INVITED_NODES = (existing_hosts, NEW_CLIENT_IP)

# Or disable if not needed:
# TCP.VALIDNODE_CHECKING = NO

After editing sqlnet.ora:

lsnrctl reload

6. Verify the Fix

# Test from the client:
sqlplus user/password@service_name

# Or with Easy Connect:
sqlplus user/password@hostname:1521/service_name

# Minimal connectivity test:
tnsping service_name

7. Collect Diagnostic Information for Oracle Support

If the issue persists:

# Enable tracing on the server side — add to server sqlnet.ora:
TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = server_trace
TRACE_DIRECTORY_SERVER = /tmp/oracle_trace

# Attempt the connection, then review:
cat /tmp/oracle_trace/server_trace*.trc | grep -i "ora-\|auth\|nts\|handshake"

Prevention Strategies

1. Standardize sqlnet.ora Across All Environments

# Maintain a standard sqlnet.ora template in version control.
# Example minimal secure configuration:
SQLNET.AUTHENTICATION_SERVICES = (NONE)
SQLNET.EXPIRE_TIME = 10
SQLNET.OUTBOUND_CONNECT_TIMEOUT = 30
TCP.CONNECT_TIMEOUT = 30

2. Test After Every Oracle Net Configuration Change

# After any change to sqlnet.ora, tnsnames.ora, or listener.ora:
lsnrctl reload
tnsping service_name
sqlplus system/password@service_name <<< "SELECT 1 FROM dual;"

3. Document Authentication Requirements

• Maintain a runbook section documenting whether NTS, Kerberos, or database authentication is required
• Include the exact SQLNET.AUTHENTICATION_SERVICES value for each environment
• Record any wallet locations and their contents

4. Monitor for Connection Failures

-- Check audit trail for failed connections:
SELECT
  username,
  terminal,
  timestamp,
  action_name,
  returncode
FROM dba_audit_session
WHERE returncode != 0
  AND timestamp > SYSDATE - 1
ORDER BY timestamp DESC
FETCH FIRST 50 ROWS ONLY;

Related Errors

• ORA-01017 - Invalid username/password
• ORA-12154 - TNS could not resolve connect identifier
• ORA-12543 - TNS destination host unreachable
• ORA-28040 - No matching authentication protocol

Emergency Response

Fastest Fix for Most Cases

# 1. Open sqlnet.ora on the database server:
vi $ORACLE_HOME/network/admin/sqlnet.ora

# 2. Set or change:
SQLNET.AUTHENTICATION_SERVICES = (NONE)

# 3. Reload listener:
lsnrctl reload

# 4. Test:
sqlplus user/password@service_name

If the Issue Is on Windows With NTS

-- Connect as SYSDBA using OS authentication (/ as sysdba) on the server:
-- Then check:
SHOW PARAMETER os_authent_prefix;

-- If NTS must remain, ensure the connecting OS user has an Oracle account:
SELECT username FROM dba_users WHERE authentication_type = 'EXTERNAL';

Post-Fix Validation

-- Confirm connections are succeeding:
SELECT
  username,
  machine,
  program,
  logon_time
FROM v$session
WHERE username IS NOT NULL
  AND username != 'SYS'
ORDER BY logon_time DESC
FETCH FIRST 10 ROWS ONLY;