Workflow mailer OAF-based notifications and SSL terminated load balancer environment

[ArtKuznetsov]

Hi Erman!

We have recently implemented SSL-terminated load balance configuration in our 11.5.10.2 EBS installation. The only place with HTTPS is load balancer, middle tier is not configured with HTTPS.
After that, some functionality stopped working:
- View workflow (and any other) logs from OAF pages. Please see attached screenshots
- Workflow Mailer notifications with OAF regions
- Workflow status diagram
In all cases, the following error is displayed: "SSL handshake failed: SSLSessionNotFoundErr".

Could you please advise what can we do in this situation? We don't want to implement SSL on the middle tier

Regards,
Artyom

[ErmanArslansOracleBlog]

Hi,

This seems address in the following MOS note;

11i Workflow Notifications With OA Framework Based HTML Content Fail With Error 'Oracle.apps.fnd.wf.common.HTTPClientException Unable to invoke method HTTPClient.HTTPConnection.Get caused by: javax.net.ssl.SSLException: SSL handshake failed' (Doc ID 2226186.1)

When you review the note, you will see that some patches are required...

[ArtKuznetsov]

Thank you very much for the quick response!

Yes I found that note, but I'm confused a bit with the following points:
 - not only WF mailer notification fail, but also View log functionality, that isn't connected to workflow mailer Java process
 - we didn't implement ssl on the middle tier, so neither wfmailer java process nor FNDFS (view log functionality) basically don't try to establish SSL connection. That's why the error is "SSLSessionNotFoundErr", and not "SSL handshake failed".

Do you have the similar experience of configuring EBS with SSL termination? I'm pretty sure there are few pitfalls with SSL cert store, but not sure the exact place

Thank you,
Artyom

[ErmanArslansOracleBlog]

Yes, I see..
You didn't enable SSL in the middle tier, but there are agents.. So when you take actions in the middle tier, they may try to reach the services using https hostnames, even through the https load balancer url itself..
It is probably related with WF Agent Profiles.
For instance the profile option named WF_MAIL_WEB_AGENT.

Check this blog post -> https://ermanarslan.blogspot.com/2014/08/ebs-122-notification-mailer.html  (Read the last pieces of it..)

-There are some workarounds, please check them first. (read the blog post above -- it s for 12.2, but you will get the idea)
-Apply the patches documented (MOS doc. I sent you earlier.)

[ArtKuznetsov]

Yep, WF_MAIL_WEB_AGENT is set correctly, workflow queue was rebuilt after that.
Thank you for the suggestions, I'll try and configure SSL_TRUSTSTORE for the mailer and import root certificates there

[ErmanArslansOracleBlog]

Okay Artyom. Waiting for your update..

[ArtKuznetsov]

It seems that the issue occurs because load balance uses TLS 1.2, not SSL. There is a similar situation described here
XML Gateway Purchase Order (PO) Transmission To Supplier Site Fails With 'SSL Handshake Failure' Error After Migration From SSL To TLS (Doc ID 2065167.1)
But still no ideas how to make WF Mailer work with TLS

[ErmanArslansOracleBlog]

Hi Artyom,

Please do the following;

Set the workflow mailer log level to statement.
Reproduce the issue
Send me the related part of the log.


--what about setting WF_MAIL_WEB_AGENT to the one of the apps nodes on your EBS instance? If it is applicable, this may be a workaround.(supposing your apps nodes still communicate with http.)

[ArtKuznetsov]

Hi Erman! Here is the error:

[Oct 25, 2020 5:40:03 PM MSK]:1603636803795:Thread[outboundThreadGroup1,5,outboundThreadGroup]:0:-1:prulmasofia01.hq.ru.corp.leroymerlin.com:10.99.16.1:-1:-1:UNEXPECTED:[SVC-GSM-WFMLRSVC-206160-10006 : oracle.apps.fnd.wf.mailer.NotificationFormatter.handleResEndTag]:Problem handling the RESOURCE content javax.net.ssl.SSLException: SSL handshake failed: SSLSessionNotFoundErr
[Oct 25, 2020 5:40:03 PM MSK]:1603636803797:Thread[outboundThreadGroup1,5,outboundThreadGroup]:0:-1:prulmasofia01.hq.ru.corp.leroymerlin.com:10.99.16.1:-1:-1:UNEXPECTED:[SVC-GSM-WFMLRSVC-206160-10006 : oracle.apps.fnd.wf.mailer.NotificationFormatter.getFormattedMessages()]:Problem parsing XML -> org.xml.sax.SAXException: Problem handling the RESOURCE content -> javax.net.ssl.SSLException: SSL handshake failed: SSLSessionNotFoundErr
[Oct 25, 2020 5:40:03 PM MSK]:1603636803797:Thread[outboundThreadGroup1,5,outboundThreadGroup]:0:-1:prulmasofia01.hq.ru.corp.leroymerlin.com:10.99.16.1:-1:-1:ERROR:[SVC-GSM-WFMLRSVC-206160-10006 : oracle.apps.fnd.wf.mailer.SMTPMessageHandler.prepareMessages(String)]:FormatterException -> oracle.apps.fnd.wf.mailer.FormatterException: Problem parsing XML-> org.xml.sax.SAXException: Problem handling the RESOURCE content -> javax.net.ssl.SSLException: SSL handshake failed: SSLSessionNotFoundErr
	at oracle.apps.fnd.wf.mailer.NotificationFormatter.handleResEndTag(NotificationFormatter.java:3492)
	at oracle.apps.fnd.wf.mailer.NotificationFormatter.endElement(NotificationFormatter.java:578)
	at oracle.xml.parser.v2.XMLContentHandler.endElement(XMLContentHandler.java:196)
	at oracle.xml.parser.v2.NonValidatingParser.parseElement(NonValidatingParser.java:1212)
	at oracle.xml.parser.v2.NonValidatingParser.parseRootElement(NonValidatingParser.java:301)
	at oracle.xml.parser.v2.NonValidatingParser.parseDocument(NonValidatingParser.java:268)
	at oracle.xml.parser.v2.XMLParser.parse(XMLParser.java:253)
	at oracle.apps.fnd.wf.mailer.NotificationFormatter.getFormattedMessages(NotificationFormatter.java:354)
	at oracle.apps.fnd.wf.mailer.SMTPMessageHandler.prepareMessages(SMTPMessageHandler.java:77)
	at oracle.apps.fnd.wf.mailer.SMTPOutboundProcessor.read(SMTPOutboundProcessor.java:732)
	at oracle.apps.fnd.cp.gsc.SvcComponentProcessor.process(SvcComponentProcessor.java:604)
	at oracle.apps.fnd.cp.gsc.Processor.run(Processor.java:283)
	at java.lang.Thread.run(Thread.java:682)
 Caused by: org.xml.sax.SAXException: Problem handling the RESOURCE content -> javax.net.ssl.SSLException: SSL handshake failed: SSLSessionNotFoundErr

I have already set WF_MAIL_WEB_AGENT to the first physical server HTTP URL. Some mails are sent successfully, but some don't. In addition, view logs (WF mailer, concurrent managers etc) from OAF interface doesn't work. I'm confused with that. I have already tried and configured cacerts in JDK (added intercal CAs) and pointer workflow mailer SSL_TRUST to that certificate store, but with no luck.

[ErmanArslansOracleBlog]

Okay , I have limited connectivity right now, so I will send you my comments and some reference documents.. Please take a look these documents and comment and try to find your way.

Please update me with the outcome.

-->

1)
It is may be related with the APPS_FRAMEWORK_AGENT.
What is the value of the profile option named APPS_FRAMEWORK_AGENT?
Note that, if the Application Framework Agent profile option is set to a virtual or load balancing server, one must explicitly set the WF: Workflow Mailer Framework Web Agent profile option to a specific physical Web Server host address that is non-load balanced.

Maybe..

2)
Also is this a new config? I mean  is the Load Balancer configured according to the  EBS? ( configuration on load balancer itself)

3)
**

Ref: What Should Be The Value Of Application Agent Profiles? (Doc ID 559232.1)
->  Make sure that a System Profile 'Application Framework Agent' (APPS_FRAMEWORK_AGENT) is setup to non-SSL URL.

4)
**

Ref: E-Business Suite Workflow Java Mailer Fails With Error 'UNEXPECTED:[SVC-GSM-WFMLRSVC' And 'oracle.apps.fnd.wf.mailer.NotificationFormatter.handleResEndTag' Due To Problem obtaining the HTML content oracle.apps.fnd.wf.common.HTTPClientException (Doc ID 367483.1)
---Note that, you already did that!
-> Customers using loadbalancers, etc that uses a virtual hostname you must use the URL of one of their iAS web servers in WF: Workflow Mailer Framework Web Agent profile option (WF_MAIL_WEB_AGENT).


5)
** LOOK AT THIS ONE ->  it says rebuild the queue..

Ref: Java Notification Mailer Fails to Send Email Notifications from FormatterException with Framework Regions (Doc ID 339718.1)

To set the WF_MAIL_WEB_AGENT profile :
Stop Workflow Mailer and Agent Listener service
Change the "WF: Workflow Mailer Framework Web Agent" [WF_MAIL_WEB_AGENT] profile option to point to a physical Web Server host address that is non-load balanced.
Rebuild mailer queue with:
SQL>@$FND_TOP/patch/115/sql/wfntfqup.sql APPS <APPS schema passwd> APPLSYS
Start Workflow Mailer and Agent Listener service.
Test scenario with a new notification.


6)
 You said " In addition, view logs (WF mailer, concurrent managers etc) from OAF interface doesn't work. "

This is interesting.. This  also makes me think about the APPS_FRAMEWORK_AGENT and the configuration in the load balancer side
Check -> Note:217368.1 - Advanced Configurations and Topologies for EBS 11i -> Option 2.2. HTTP Layer Hardware Load Balancing

[ArtKuznetsov]

Hi Erman,

thank you very much for the help! After deep research, we discovered that it was custom workflow message body generation that caused the error. There was a custom PLSQL procedure that generated email body, and it used APPS_FRAMEWORK_AGENT instead of WF_MAIL_WEB_AGENT. We changed the code, and now it works fine.

Workflow status diagram was fixed by Patch 8802559: 1OFF:8618975:11.5.10.6:WF STATUS DIAGRAM SHOULD OPTIONALLY USE WF: Workflow Mailer Framework Web Agent, that implements the same logic (use WF_MAIL_WEB_AGENT)

The only thing that keeps failing is view log files from OAF interface. I'm pretty sure it is a TLS issue, because our load balancer configured with TLS 1.2, but OeBS 11.5.10.2 with JDK6 is able to work with TLS 1.0 only. But this issue is not that crucial, so we keep it as is.

Thanks a lot again for your help!

Regards,
Artyom

[ErmanArslansOracleBlog]

I am glad for that Artyom :) If there are new problems, I will try to help again.