Why do I have "Checksum fail" on every bad SQL request in Oracle when Native Encryption (ASO) is enabled?

Question

We recently configured our Oracle database to be in so-called native encryption (Oracle Advanced Security Option).

As development goes on, some SQL queries are sometimes badly-written and so an error should be returned by the JDBC driver (ojdbc7 v12.1.0.2). Instead of that, a Checksum Fail IOException is raised.

So the problem is that we do not have anymore any syntax or database integrity error at all. The problem is the same in SQL GUI editors, like DBeaver, SQLDeveloper or SQuirrel.

Solution 1

It's a known issue in the Oracle JDBC thin driver. If you can use SSL instead of ASO then this problem will go away.

Solution 2

With driver ojdbc7 12.1.0.1 the correct VM parameter names are as follows:

-Doracle.net.crypto_checksum_client=REQUIRED
-Doracle.net.crypto_checksum_types_client=SHA1

Solution 3

Driver version 12.1 and earlier have a bug in SHA-2 functions If able force the server to handshake with SHA-1

-Doracle.net.crypto_checksum_client=REQUIRED
-Doracle.net.crypto_checksum_types=SHA1

This is fixed in ojdbc8.jar version 12.2

Solution 4

Our team is also experiencing the same issue.

Determined that setting the WebLogic Connection Pool to use either SHA1 or MD5 for Checksum Encryption resolved the issue (also had to add the chosen value to the list of approved algorithms in the DB-server's sqlnet.ora file of course).

Attempts to use any Checksum value on the client side aside from SHA1 or MD5 produced the Checksum Fail error message when Oracle attempted to return a 'standard' error ie Constraint Violation.

View more solutions