Oracle connection failed: ORA-03113

Toad Data Modeler
1

Hi,

Using TDM 5.3.4.13

When trying to connect to Oracle 11.2.0.4 to reverse engineer I get;

Connection failed because of the following error: “ORA-03113: end-of-file on communication channel”

TOAD connects OK as does SQLPLUS.

I finally tracked this down to the database server lockdown.

Commenting out the sqlnet.encryption settings in the server SQLNET.ORA allowed the connection.

Are there plans for TDM to support this feature (especially since TOAD does)?

Regards

Malcolm

2

Hello Malcolm,

Thank you for bringing this to our attention. Our devs are currently analyzing the issue, once we arrive at some conclusion, I’ll be sure to tell you.

Regards,

Lukas

3

Hello again,

Could you tell us what kind of connection are you using to Reverse Engineer your database (ADO, Native, TCP/IP)? Also, have you tried other connection methods? Did you get the same error?

Regards,

Lukas

4

Hi Lukas,

I was connecting using TCP/IP.

I just set up my tnsnames and tried with Native. This connects OK.

Not sure how to set up ADO.

Regards

Malcolm

From: Lukas Knapek [mailto:bounce-LukasKnapek@toadworld.com]
Sent: 30 January 2015 07:53
To: tdm@toadworld.com
Subject: RE: [Toad Data Modeler - Discussion Forum] Oracle connection failed: ORA-03113

RE: Oracle connection failed: ORA-03113

Reply by Lukas Knapek

Hello again,

Could you tell us what kind of connection are you using to Reverse Engineer your database (ADO, Native, TCP/IP)? Also, have you tried other connection methods? Did you get the same error?

Regards,

Lukas

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Modeler - General notifications altogether.
Toad Data Modeler - Discussion Forum

Flag this post as spam/abuse.

5

If native connection works, I would recommend you to use it instead. Toad and SQLPlus do not use TCP/IP connection, which is essentially connection without using Oracle client. However, it should still work in all cases.

We would like to reproduce your issue, could you provide us with some more information? What specific sqlnet.encryption parameter have you commented out? Did the error start appearing after upgrading to a newer version of TDM, or did you start using another database?

Regards,

Lukas

6

Hi,

We use direct connection in TOAD.

Below please find the contents (with some values redacted) of our %ORACLE_HOME%\NETWORK\ADMIN*.ora files.

The sqlnet.ora lines I changed are commented out with “###”

Title: listener.ora

Description: Oracle database-level configuration file.

Tokens in the configured file (e.g. $ORACLE BASE) are replaced by

system-specific values at the point of install by the installer.

CIS Ref numbers refer to document:

Security Configuration Benchmark for Oracle Database Server 11g v1.1.0, Dec 31,2011.

Renamed listener, with non-default port.

This requires an ‘alias’ entry in tnsnames.ora, and a local_listener setting in the spfile.

CIS ref 2.11, 2.06

aaaaaaa_lsnr =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY = REGISTER))

(ADDRESS = (PROTOCOL = TCP)(HOST = nnn.nnn.nnn.nnn)(PORT = iiii))

)

Static registration of the database. CIS Ref 4.42

SID_LIST_aaaaaaa_lsnr =

(SID_LIST =

(SID_DESC =

(SID_NAME = aaaaaaa)

(ORACLE_HOME = C:\oracle\product\11.2.0\db_1)

)

ExtProcs not used, and therefore disabled.

If required, DLLs must be specifically listed in the ENVS entry. CIS Ref 4.43

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = C:\oracle\product\11.2.0\db_1)

(ENVS=“EXTPROC_DLLS=ONLY:”)

)

)

#################################

#Protect the listener.

Prevent unauthorized admin of the listener.

This setting may be legacy from 9i security, but

is mandated by the CIS benchmark. CIS ref 4.13

ADMIN_RESTRICTIONS_aaaaaaa_lsnr=ON

Local OS authentication is the default.

With this enabled, the status security line includes “Local OS authentication”

The DBA user can control the listener without a password.

If this is turned off, and no password set, any remote user can control

the listener without a password.

So we enforce the default:

LOCAL_OS_AUTHENTICATION_aaaaaaa_lsnr=ON

‘set password’ and ‘save_config’ result in the following line.

This requires the correct password to control the listener.

The listener status security includes “Password”

However this then allows remote admin of the listener.

So by keeping LOCAL_OS_AUTH, and not using a password,

we prevent any remote admin of the listener.

CIS Ref 2.08

LEAVE THIS LINE COMMENTED OUT.

#PASSWORDS_aaaaaaa_lsnr = xxxxxxxxxxxxxxxxxxxx

#################################

CIS ref 4.19

INBOUND_CONNECT_TIMEOUT_aaaaaaa_lsnr=0

#TRACE_LEVEL_aaaaaaa_lsnr=admin

CIS ref 3.21, 3.22

TRACE_DIRECTORY_aaaaaaa_lsnr = C:\oracle\product\11.2.0\db_1\network\trace

TRACE_FILE_aaaaaaa_lsnr = listener.trc

log listener commands. CIS ref 3.19, 3.20

LOG_STATUS=ON

LOG_DIRECTORY_aaaaaaa_lsnr = C:\oracle\product\11.2.0\db_1\network\log

LOG_FILE_aaaaaaa_lsnr = aaaaaaa_lsnr.log

log all listener admin activity. CIS ref 4.14

LOGGING_aaaaaaa_lsnr=ON

remote admin requests only allowed over secure transport. CIS ref 4.39

SECURE_CONTROL_aaaaaaa_lsnr = IPC

admin requests only allowed over secure transport. CIS ref 4.40

(overridden by SECURE_CONTROL and SECURE_REGISTER)

SECURE_PROTOCOL_aaaaaaa_lsnr = TCP

registration requests only allowed over secure transport. CIS ref 4.41

SECURE_REGISTER_aaaaaaa_lsnr = IPC

Only allow known, preconfigured databases to be linked to this listener. CIS ref 4.42

DYNAMIC_REGISTRATION_aaaaaaa_lsnr = OFF

Title: sqlnet.ora

Description: Oracle database-level configuration file.

Tokens in the configured file (e.g. $ORACLE BASE) are replaced by

system-specific values at the point of install by the installer.

CIS Ref numbers refer to document:

Security Configuration Benchmark for Oracle Database Server 11g v1.1.0, Dec 31,2011.

Default Auth Services on Windows is NTS.

NONE seems to be required for running the Oracle services as user oramgr.

NONE also blocks oramgr from using ‘sqlplus / as sysdba’, so that all users

must supply a password.

SQLNET.AUTHENTICATION_SERVICES = (NONE)

Ensure trace files are in directories owned by the oracle user. CIS ref 3.17, 3.18

#trace_level_client=16

trace_directory_client= C:\oracle\product\11.2.0\db_1\network\trace

trace_directory_server= C:\oracle\product\11.2.0\db_1\network\trace

trace_unique_client=on

Ensure log files are in directories owned by the oracle user. CIS ref 3.15, 3.16

log_directory_client= C:\oracle\product\11.2.0\db_1\network\log

log_directory_server= C:\oracle\product\11.2.0\db_1\network\log

limit the Db connection requests to those from systems on

the current configuration. CIS ref 4.20, 4.21

TODO tcp.validnode_checking = YES

invited nodes list: localhost, clientname, clientname,…

Notes:

1. “localhost” must be in the list

3. client names must exist in the server’s hosts.dat file (but don’t need to be switched on)

4. Hosts should be identified by IP address for increased security.

5. the value ‘localhost’ is expected by the deploy_ora.xml ant script.

This must remain as the first entry. deploy_ora will include the server

IP address on first run, and will maintain the existing entries on subsequent runs.

NOTE: The DB Server IP address seems to have to be in the list for local connections

made via the listener.

tcp.invited_nodes=(localhost,nnn.nnn.nnn.nnn)

Set a timeout on making the initial connection. CIS REF 4.23

sqlnet.inbound_connect_timeout = 3

Expire idle connections. CIS REF 4.24

sqlnet.expire_time = 30

Enforce use of higher security 11g client only. CIS ref 4.27

Oracle Net Services Administrator’s Guide 11gR2 states;

Releases of OCI clients before Oracle Database 10g and all versions of JDBC thin clients

cannot authenticate to the Oracle database using password-based authentication

The client must support certain abilities of an authentication protocol before the server will authenticate.

If the client does not support a specified authentication ability, then the server rejects the connection

with an ORA-28040: No matching authentication protocol error message.

so specify the default of 8 to allow servers to connect

#sqlnet.allowed_logon_version = 11

sqlnet.allowed_logon_version = 8

Make an audit-warning banner available to client connections. CIS ref 4.37

sec_user_audit_action_banner=C:\oracle\product\11.2.0\db_1\network\admin\audit_warning_banner.txt

Make an unauthorized-access warning banner available to client connections. CIS ref 4.38

sec_user_unauthorized_access_banner=C:\oracle\product\11.2.0\db_1\network\admin\unauthorized_access_banner.txt

##############################################################################

OAS configuration is mandated by CIS chapter 5

However, it is not in use here, so the configuration is set, but not described.

sqlnet.crypto_checksum_server left as default, to allow logins.

CIS refs 5.02 - 5.06

###sqlnet.encryption_server = required

###sqlnet.encryption_client = required

###sslfips_14 = true

#sqlnet.crypto_checksum_server = required

sqlnet.crypto_checksum_client = required

sqlnet.crypto_checksum_types_server = (sha1)

#############################

SSL configuration, if used.

CIS refs 5.13, 5.16, 5.25, 5.26

ssl_version = 3.0

ssl_cipher_suites = ssl_rsa_with_3des_ede_cbc_sha

ssl_client_authentication = false

ssl_cert_revocation = required

ssl_server_dn_match = yes

##############################################################################

Title: tnsnames.ora

Description: Oracle database-level configuration file.

Tokens in the configured file (e.g. $ORACLE BASE) are replaced by

system-specific values at the point of install by the installer.

CIS Ref numbers refer to document:

Security Configuration Benchmark for Oracle Database Server 11g v1.1.0, Dec 31,2011.

##############################################################################

No TNS entry, as all software connections use direct access via JDBC.

While these connections are still made via the listener, there is no TNS lookup.

Local sqlplus connections & scripts will use the Bequeath adapter (i.e. connect without the ‘@SID’

service name)

##############################################################################

The default extproc entry is removed, as external procedure calls are not currently used.

Turning off extproc access removes a potential security exploit.

Although Oracle Security Alert 57 (244523.1) has been fixed in 10g,

others may appear in future.

Java functions are built directly into Oracle, and are not called via extproc.

CIS ref 4.30

##############################################################################

Alias for the renamed Listener.

CIS Ref 2.05

A renamed listener (not the default: LISTENER) is also required to use a non-default port number.

aaaaaaa_LSNR =

(ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = IPC)(KEY = REGISTER))

  (ADDRESS = (PROTOCOL = TCP)(HOST = nnn.nnn.nnn.nnn)(PORT = iiii))

)

Malcolm

From: Lukas Knapek [mailto:bounce-LukasKnapek@toadworld.com]
Sent: 30 January 2015 09:02
To: tdm@toadworld.com
Subject: RE: [Toad Data Modeler - Discussion Forum] Oracle connection failed: ORA-03113

RE: Oracle connection failed: ORA-03113

Reply by Lukas Knapek

If native connection works, I would recommend you to use it instead. Toad and SQLPlus do not use TCP/IP connection, which is essentially connection without using Oracle client. However, it should still work in all cases.

We would like to reproduce your issue, could you provide us with some more information? What specific sqlnet.encryption parameter have you commented out? Did the error start appearing after upgrading to a newer version of TDM, or did you start using another database?

Regards,

Lukas

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Modeler - General notifications altogether.
Toad Data Modeler - Discussion Forum

Flag this post as spam/abuse.

7

Hello again,

We’ve discovered that TCP/IP connection unfortunately does not support Oracle native encryption. This functionality is implemented at the client level, and client is not used used in this type of connection.

Therefore, we recommend using Native or ADO connection when dealing with encrypted Oracle databases.

Regards,

Lukas

8

OK,

Thank you for clarifying this.

Regards, Malcolm