Hmm. thought so that it is quite hard to derive the correct p_https_host dynamically if it is actually different from the p_url .
I have understood that apache reverse proxying is ultimate solution to this challenge, but I still would like to understand and have a proper fix.
Is it time to turn to MOS and if so, are there patches which brings ease?
After googling furiously I found this: https://security.stackexchange.com/questions/113267/can-i-read-the-domain-name-from-https-before-ssl-handshake
Without SNI, the domain first appears in cleartext in the Server Hello of the TLS handshake (In the rdnSequence of the Certificate field).
and it raises the question is it possible to get the domain name from in the rdnSequence of the Certificate field in the 'Server Hello of the TLS handshake' ?
please, read also this Re: utl_http.request on ssl site fails with ORA-29024. I'm not sure if by "programmatically" you meant also invoking the openssl command, but it should not be a big deal to process its output.
Yes indeed this aint easy.
To be frank I assume that some of errors I have been able to generate while iterating the p_https_host are due 'snakeoil self-signed certs' somewhere deep where I can't see from the plsql.
Only indication about it is that the error remains the same ORA-29024 in our pretty patched 12.2.x for them regardless about the p_https_host.
The valid certificate at the ws server end gives more reasonable errors like: ORA-28788, ORA-24263 or just works when p_https_host appears to be correct.
For 12.1.x there is no need to define p_https_host.
For the programmatic fetch I dreamed about the MLE python script something like ssl-check.py or so, but it seems that its still in vbox pampering phase its not said that we are allowed to wireshark db.
I must admit that I don't fully understand your last post. I cannot test your code and I have no idea what site your trying to invoke, so there is very little that can be done. The p_https_host parameter was introduced in 12.2 (https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/UTL_HTTP.html#GUID-BBD953E8-CA2B-4D2F-B4E8-125A0C2… ) because of multi-site certificates, so obviously there is no such parameter in 12.1 and in 18+ version this is being handled automatically by the database.
I know very little about Python (as it's the most "dangerous" and error-prone language I've ever seen - just my opinion, of course) but it should not be that tough to invoke openssl and process its output instead of using native Python module(s).
its not said that we are allowed to wireshark db
I have no idea whatsoever what this can possibly mean.
Yes, I should have elaborated more my findings. 1st I was perhaps barking the wrong tree with the https_host parameter, because it seemed be already patched in 'my' environment, so no bugs. Also the error ORA-29024 was thrown for all possible variants I was trying for both the Oracle wallet + https_host parameter in the call for some of the webservices and for some of the web_services which apparently had 'valid CA for the client certificate' and which had 'selfsigned client certificate' I started to get more descriptive errors like ORA-28788 and ORA-24263 and I managed to get those webservices up & running in 12.2.
It is very hard to see from plsql what are the actual ssl certificates in the client-server TCP+TLS1.2 handshake and further on what keeps failing when the Client + Server Hellos and TCP handshakes have been done.
Even the openssl is not showing what happens, so tcpdump to listen the network device is needed to see. The wireshark is just cool gui to e.g. analyze the tcpdumps, and it showed for the working connections before actual enciphered data exchanged between client-server the enciphering is agreed between client and server (tls handshake).
For that the server client certificate which might be different and self-signed for the outgoing data might be the source of the problem - or - not having client certificate at all (currently cant say which one of these). For selfsigned certs the issuer is the same as the certs cn itself so their CA-issuer chain might also be not known by the wallet. Wireshark can show from the tcpdump 'unknown CA' 'from 'client db' for the connections which fail miserably with the ORA-29024.
What I meant with wireshark db is that it is not typical to be able to dump network traffic on the database servers network interface.
Also what I was hoping to see is more details about the actual TLS handshake between the client and server, so was thinking first python code and some premade scripts with them. (I like you don't like Python at all and just because of not being able to have strongly typed null, just dull none type for nulled values. So if we could do this with plsql then it would be the best.).
As said there were numerous forum articles e.g. yours etc. to be checked before I was able to reach this step having some of the ws working and some of them not. Imagine the situation for 'casual' developer trying to contact those ws via proxy and having no idea whatsoever about the needed checks.
It wouldn't hurt if there would be a good 'sequence diagram' about this doc-page: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/dbseg/configuring-secure-sockets-layer-authentication.ht…
where also the ORA-nnnn errors would be presented for e.g. typical Apex application consuming the webservices. Good subject for very descriptive blog I think. Also it wouldn't hurt to give SNI examples both working and erronous with network traffic dumps showing what is going wrong in the handshakes.
Actually this whole discussion (sort of question) has triggered more generic question about how to really capture the ORA-nnnn errors from harmless ws-calls which are not necessarily needed. I opened other thread for Robustness to page with error-prone ws requests? , but havent yet been able to wrap the 'flaky' ws calls with better 'error handlers' because such want fix the root cause for the problems.
ps. sorry for not being able to share screenshots about the actual network analysis dumps etc. - it would require more time to make clean test case for this...
Below some ORA-errors and how I have interpreted them:
First I spend lots of time to get see errors behind the ORA-29273
Error report - ORA-29273: HTTP request failed ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1988 ORA-53203: security violation ORA-06512: at "SYS.UTL_HTTP", line 380 ORA-06512: at "SYS.UTL_HTTP", line 1127 ORA-06512: at "APEX_190100.WWV_FLOW_WEB_SERVICES", line 1891 ORA-06512: at "APEX_190100.WWV_FLOW_WEBSERVICES_API", line 196 ORA-06512: at "MYSCHEMA.MY_CODE_PKG", line 364 ORA-06512: at "MYSCHEMA.SP_MY_TEST_CALL", line 41 ORA-06512: at line 1 29273. 00000 - "HTTP request failed" *Cause: The UTL_HTTP package failed to execute the HTTP request. *Action: Use get_detailed_sqlerrm to check the detailed error message. Fix the error and retry the HTTP request.
And looked for the more detailed ORA-error like in the above ORA-53203
ORA-error my interpretation ORA-29273: HTTP request failed this is the error the end-user will see e.g. in the Apex application and deeper analysis is needed why. ORA-24263: Certificate of the remote server does not match the target address. certificate is ok, but p_https_host FQDN is not. I was sure that I had faced the 12c 12.2.* challenges with the https_host but that wasn't the case for my setup. And I was desperately iterating all imaginable host names for the ws with multi-site certificate I was trying to consume. ORA-28788: user provided invalid information, or an unknown error. error from erroneous wildcard in p_https_host = *.somwhere.tla ORA-29024: Certificate validation failure
no p_wallet_path defined, missing correct certificate.
I really think there should be perhaps more descriptive error which gives hint about missing wallet or explains that no wallet has been used, so the certificates are not trusted in TLS handshake.
It was hard to believe that only CA cert was needed to get things working
ORA-12535: TNS:operation timed out no p_proxy_override defined or just no connectivity ORA-29106: Cannot import PKCS #12 wallet. this is actually because of wrong password for wallet ORA-53203: security violation
this is shown in sqldeveloper (not disconnected session)
after issuing for wallet wrong password,
all wallets throw this so all apex_web_service.make_request connections for this sql client fail.