CF2021 Update 11 cannot connect to Oracle database with encryption and checksum

[Paul Hirose]

Updating to CF2021 Enterprise Update 11 on my Linux server connecting to Oracle 19c database that uses encryption & checksum breaks.  Downgrading the Oracle package from 2021.0.11 to 2021.0.05 lets me connect again.

While an update to the Oracle driver isn't listed in the Update 11 notes, it is indeed updated. They do mention "enhancements in ...Database, and other areas".  Searching for "database" I see some changes to MySQL and Postgres but nothing for Oracle.  I do indeed see the new files on my filesystem.  Going to the Package Manager in the CF Administrator GUI shows that it has updated to "2021.0.11.330247".  If I select "2021.0.05.330109" in the "Available Versions" and then click "Downgrade" and restart my CF instance, the datasource now connects.  Incidentally, there is no "downgrade" command in cfpm.sh so I had to actually go through using the web-gui to do the downgrade.  I'll submit a Feature Request to allow downgrade command via cfpm.sh separately.

The error I see in coldfusion-error.log or exception.log is "[DataDirect][Oracle JDBC Driver]ORA-12656: Cryptographic checksum mismatch"

I didn't change any parameter in my DataSource itself.  My Oracle database hasn't changed either. 

I will note I am using Amazon Linux 2 in AWS EC2 and am also using Amazon Corretto 11.0.20.9.1.  So at first, I thought it was something to do with that, and I suppose that's still possible.  I'm also seeing this in my Oracle Linux 7 system using openjdk11 as well, but that's a test system so it could have other things going on with it.  However, given that simply downgrading the Oracle package from 2021.0.11 to 2021.0.05 makes it work again, I suspect this issue is because of some undocumented Oracle driver update/change.

If anyone has any ideas, please chime in.

Thanks all,

PH

[Charlie Arehart]

While we all consider and assess that issue further, how about trying instead the jvm that Adobe supports, which is neither coretto nor openjdk but Oracle's Java? 

If you have people concerned about paying for it, you don't need to. Adobe has licensed it for our use with CF. And notice that the jvm Adobe offers on the "java installers" section of the cf downloads page is indeed only the Oracle jdk. That's what one should be using with CF--and Java 11 (only, for now) with CF2021 and Java 17 (only, for now) with CF2023, as are offered there.

I'm not saying saying changing it WILL solve your issue, but it's a couple minutes and a cf restart to then try the new update again (a few more mins). It would be great to hear whether it helps or not.

And even if it doesn't, this was a comment worth clarifying things for you and any other readers using other than Oracle Java. Also not saying anything will fail by using another jvm implementation. Just clarifying what's SUPPORTED. Folks do all kinds of unsupported things. It's important always to know when that's the case, for you and for us. 🙂 

[Paul Hirose]

Good point.  I wasn't even aware Adobe had made Oracle Java available for production use for ColdFusion purposes.

Having switched to that, and re-updated Oracle to 2021.0.11 again, I'm still seeing the same issue.  I did switch my jvm.config and the system info does indeed show I'm now using Oracle Corporations 11.0.20+9-LTS-256.  Downgrading the package to 2021.0.05 makes it connect ok again.

I have not tried connecting with "Other" datasource type and using a downloaded Oracle odbc.jar file, but I kinda figure not having to do that is one (of many) reasons to have CF-Enterprise to begin with.

Now that I know this is available, I may as well go switch my Oracle on all my CF instances to that.  I don't see it available as a yum repo, so I may have to manually update but I guess I can live w/that, if it makes for better compatiblity. 

I won't quite be able to easily swap out an entire OS, and I'm not sure we'd go down that route for the sole purpose of making CF2021-Update11-Oracle-Package work, even if that was the official answer.  And yes, that's why I did note both my OS and my Java, because it is officially unsupported.

I was hoping someone would chime in and say that it does work on an officially supported configuration in which case, I'd love to know the connect string configuration in your CF Admin datasource and the sqlnet.ora encryption/checksum parameters under Oracle 19c that does work (other than none.)

[Paul Hirose]

Sigh.  Meant to say I may as well go switch my Java to Oracle on all my CF instances 🙂

[BKBK]

If your application works with the Oracle 2021.0.05 package, then I would expect it to work with the Oracle 2021.0.11 package. There is a reason why I say that.

I unzipped \ColdFusion2021\bundles\oracle-2021.0.05.330109.jar and \ColdFusion2021\bundles\oracle-2021.0.11.330247.jar. Then I examined their respective manifest files in a text editor. I saw that they have the same build-JDK version, namely, 1.8.0_251. They also have the same Export-Package and Import-Package settings.

Which leads me to wonder whether what you are experiencing has to do with some hidden dependency. Therefore, I would suggest the following:

1.  On CF2021 Update 11, start the cfpm tool (with administrator / sudo rights);
2.  On cfpm, run the command: uninstall oracle
3.  Restart ColdFusion;
4.  On the cfpm tool (as administrator / sudo), run the command: install oracle
5.  Restart ColdFusion.

Is the problem solved?

[Paul Hirose]

Alas, nope.  Just did that, and that didn't resolve it either.

Will keep working on some magic combination of it all, but so far downgrading back to 2021.05 has been reliable across all my CF2021-Update11 dev/test environments (haven't done the update in prod yet) connecting to many different Oracle 19c databases (both on-premise in-house managed, and AWS RDS instances).

[BKBK]

Thanks for the update.

Let's look into the issue some more.

[Paul Hirose]

Just in case, I built a new RHEL8.8 system and installed CF2021 in 30day-trial mode.  No sandbox security, no secure profile, used deveopment mode, etc.  I figure that's as permissive as I could get it, in case I had some weird setting.  I've disabled selinux.  I then used a .car to import only one single datasource from one of my other dev servers.  I can connect to that datasource.  Just for the heck of it, I also created that same datasource manually separately and it connects too, no surprise.

I updated via the CF2021-Administrator GUI directly to Update 11 and rebooted.  I can no longer connect to the datasource - either the one imported via .car file or the manually created one.

I used both the built-in JRE in /opt/ColdFusion/jre/bin/java ( java version "11.0.7" 2020-04-14 LTS ) as well as the Oracle Java 11.0.20 downloaded from Adobe ( java version "11.0.20" 2023-07-18 LTS ).  I'm using the supported OS (albeit the support-matrix says RHEL8.2 and 8.5 but I can't not dnf update my server, which takes me currently to 8.8.)  

Again, if I simply downgrade to Oracle 2021.0.00, 2021.0.02, or 2021.0.05 it works fine.  If I use 2021.0.11 then it fails.

And just for completeness' sake, I then installed odbc8.jar for Oracle 19c (downloaded from https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html  ).  I copied that into /opt/ColdFusion/lib/ and restarted.  I created a new datasource using "Other" type and used "oracle.jdbc.driver.OracleDriver" as the "Driver Class Name".  Left "Driver Name" blank and used the old-school "jdbc:oracle:thin:@HOSTNAME:1521/SID" in the "JDBC URL" box and it connects fine.  

Running out of ideas.  If anyone has any other suggestions, I'm happy to try them.

Thanks all,

PH

[Paul Hirose]

Oh, and the odbc8.jar connection string (which works), including the encryption & checksum (even though it shouldn't be necessary because the database server is already requiring it anyways) looks like (old-school format)  : jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=1521))(CONNECT_DATA= (SERVICE_NAME=<servicename>))(Security=(ENCRYPTION_LEVEL=REQUIRED)(ENCRYPTION_TYPES_CLIENT=AES256)(CRYPTO_CHECKSUM_CLIENT=REQUIRED)(CRYPTO_CHECKSUM_TYPES_CLIENT=SHA256)))

Using the Oracle data-type, I enter SID, Server, Port, Username, and Password.   Under "Show Advanced Settings" -> "Conection String", I've left it blank to date (again since the db server requires it, I've never bothered to specify anything on the client side.)  But I've tried various incantations that are equivalent to what I specify explicitly in the Other jdbc:oracle:thin method, eg: "encryption_client=required&encryption_types_client=AES256&crypto_checksum_client=required&crypto_checksum_types_client=sha256".  I wasn't sure what type of connection-string format using the "Oracle" datasource type needs.  The Oracle docs ( eg: https://docs.oracle.com/en/database/oracle/oracle-database/21/jajdb/oracle/jdbc/OracleConnection.html#CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_LEVEL  ) specify either a "EzConnectPlus" or "TNS Long URL" format..  I tried the EzConnectPlus format, but realistically, I find I could just add gibberish in that "Connection String" box and it still seems to connect (if I'm using the 00-05 driver) and doesn't connect (regardless of what I put in that "Connection String" box) if I'm using the 11 driver.

I realize there's a lot here, but trying things.  If anyone has ideas, please let me know.

Thanks all,

PH

[Charlie Arehart]

Paul, this is certainly a frustrating sitaution to see you go through, but you're helping a lot with all the detail you've shared.

First, as for the fact that the Oracle thin driver (as "other") worked while the built-in Oracle driver did not , note that the former is created by Oracle while the latter is licensed by Adobe from Progress. That explains first why connectionstring values for the one may not work for the other.

On the other hand, I have done a bit of digging and I see at least one thing you can "try" for the Adobe-provided Oracle JDBC driver (from Progress--the one you were using originally). Can you add this to the connection string--which is likely empty for now:

AuthenticationMethod=SSL

I realize nothing was required before. Let's put that aside for the moment. And don't worry about "ssl" vs "tls". For now, please just check if this works. Of course, no CF restart is required. just add it, and on submit of the change CF will try to verify the DSN. Does it work? If so, I found it mentioned in the release notes of the Progress JDBC driver 6.0 (not on Adobe's site but the Progress site).

And I realize someone may find evidence (per files in the various CF driver jars) that suggest that even 2021.0.5 was using that 6.0 driver already--so this should not be necessary. But again let's just try it and see.

Finally, if it does NOT work, I can see in some REALLY low-level digging/comparison between CF's oracle-2021.0.05.330109.jar and the newer oracle-2021.0.11.330247.jar that one of the files deep inside those (2024EPSI.SF) has an indication that the old one refers to Java 8 while the new one refers to Java 11. To be clear, the "older" one (based on Java 8, specifically 1.8.0_73) is within the 2021.0.05 jar, so perhaps there's a connection to the new one now referring to Java 11 (specifically 11.0.16).

Of course, CF2021 itself only supports running on Java 11 (not 8). I'm not saying that it was wrong that the old driver has that Java 8 reference--again that's the one that WORKS for you. But I am observing this as another avenue of exploration if (and only if) the connectionstring I suggested doesn't do the job. There may well be some low-level encryption (ssl/tls) matter that this is now somehow tripping over for being on such a newer Java version. And I can think of some other things to proposed, but let's not clutter this post with more detail yet.

Let us know first how things go with the connectionstring.

[BKBK]

A suggestion: add the connection setting

DataIntegrityLevel=0

[Paul Hirose]

> DataIntegrity=0

Results in "Connection verification failed for data source: meow java.sql.SQLNonTransientConnectionException: [DataDirect][Oracle JDBC Driver]The property value given is not a valid choice : dataIntegrityLevel=0"

> AuthenticationMethod=SSL

Results in "Connection verification failed for data source: meow  java.sql.SQLException: [DataDirect][Oracle JDBC Driver]SSL Authentication was requested, but is not supported by this Oracle Server."

Both (separately) were added to the "Show Advanced Settings" -> "Connection String" box.

It's interesting that depending on the context, I can put gibberish in the "Connection String" box.  For example, I put "foo=bar" in the box and clicked Submit and I get the same issue = ORA-12656.  Yet, as above, giving it a valid option (DataIntegrityLevel) and setting it "0", the driver knows that "0" is invalid.

Note, DataIntegrityLevel=required (or accepted, requested) are valid values, and all of them work (in the sense it doesn't give the error message about "not a valid choice".  But it still does fail as it still returns the "java.sql.SQLException: [DataDirect][Oracle JDBC Driver]ORA-12656: Cryptographic checksum mismatch" error.

Thanks to Charlie, now that I know this is a "progress" driver, the docs there use different options, and are what BKBK also mentioned.  While the Oracle odbc.jar driver would use "cryto_checksum_client=required" the progress driver uses "DataIntegrityLevel=required".  Based on that, I also tried "DataIntegrityLevel=required;DataIntegrityTypes=(SHA256);" but alas that still returns ORA-12656.  Incidentally, i guess progress uses ";" as a separator for options whereas Oracle seems to use "&"

Thanks all,

PH

[BKBK]

Thanks for the update. 

What about

DataIntegrityLevel=rejected

I would suppose, that is the option closest, in essence, to DataIntegrityLevel=0.

[Charlie Arehart]

Or what about how my suggestion (AuthenticationMethod=SSL) has led to a different error that you've not seemed to explore :

"SSL Authentication was requested, but is not supported by this Oracle Server."

I didn't notice this in your reply on the 17th,where you led with responding to bkbk's suggested option.

Just to clarify, you refer to still seeing ORA-12656. Was that the code with the error with my suggested arg? If not, I'd think we should explore that other error.

And I'd think this comes back to Java. Now I know: you've changed cf to use what was then the latest Java 11.0.20 (11.0.21 came out this week). And I realize that what "broke" was your simply updating the oracle cf package/module/driver (by updating cf itself).

What I'm proposing is that that new oracle driver may be changing something about encryption, in a way that's conflicting with that Java. And I know that recent versions of Java changed various things related to encryption. (I discuss these in each of my blog posts on the jvm updates, in July, April, January, etc., listing the cumulative recent jvm changes which might need to be "undone" in the jvm because it causes some incompatibility. )

In this case, what I wonder is if the new driver presumes to use a new aspect of encryption that's somehow conflicting with that Java. I realize this is a leap. I just share if it may help. Again, if you may like direct assistance to try to resolve it, I'm available. But I realize you'll regard this as a bug in cf and so may not care to pay for assistance resolving it.

If you bring this to Adobe's attention, perhaps they may step in to help. They may or may not see this (but you can point them to it z for all that you, bkbk, and I/others have offered or might share). Did you create a ticket at tracker.adobe.com? You could, and share the link here. And then you could email cfsup@adobe.com, pointing out that and this, to get their direct attention. 

[Paul Hirose]

> (AuthenticationMethod=SSL

> SSL Authentication was requested, but is not supported by this Oracle Server."

I haven't pursued this.  Presumably it would require changes to the Oracle database server, and that's probably beyond the scope of fixes/changes we'd want to make, just to get CF working again.  I'll dig into this w/our DBA and see, although I don't want to get them onto what appears (at least to my non-DBA eyes) to be a tangent from the initial issue.

> DataIntegrityLevel=rejected

I did try that, and I'm still getting the same ORA-12656 error.  That really has me stumped, since I would expect a different error message if I were rejecting checksum vs having what I would expect this error code to mean that the checksum packet didn't match.

Having said that, I did find these notes at Progress (thanks again for letting me know this is a Progress driver):

- https://community.progress.com/s/article/ora-12656-cryptographic-checksum-mismatch-with-progress-dat...

- https://community.progress.com/s/article/Very-intermittent-copy-activity-fails-with-ORA-12656-Crypto...

The first one might apply, although I don't exactly know what they mean by "login keys that are smaller than 32 bytes" and I don't even know what version of the Progress driver this is.  A quick unzip of the ./bundles/repo/adobe_oracledriver-6.0.0.001051.jar and ./bundles/oracle-2021.0.11.330247.jar didn't show me an obvious Progress driver version number, but all I did was a simple grep 🙂  But this note does mention this affects 8.0 and there is a hotfix in 8.02

The second one also might apply since they specifically do show a sample sqlnet.ora file and no surprise, it mostly matches what I have.

I did see the Oracle PSU that came out.  I'll update to the latest one, when Adobe updates their Java-for-ColdFusion download page.  The one currently available is 11.0.20 2023-07-18 LTS , 11.0.20+9-LTS-256, so I'm guessing this is July's version and not October's version.  Presumably, Adobe will make the October version available soon(ish).

I do have a ticket open w/Adobe Support (#104942) via email.  I don't know if folks are able to see other people's tickets.  I don't have anything sensitive in there, so I don't mind if folks do go looking at it.  Since I just found the two Progress notes, I'll point that out to the Adobe folks too.   I'll check w/this ticket and then open up a corresponding one in tracker.adobe.com.

Thanks all,

PH

[BKBK]

...

> DataIntegrityLevel=rejected

I did try that, and I'm still getting the same ORA-12656 error.  That really has me stumped
...

By @Paul Hirose

Has me stumped, too.