Here is the issue we had a couple of days ago. Oracle UTL_HTTP (12.1.0.2) could not connect to a specific web site via https. We double checked the Oracle wallet and made sure the wallet file had all the root and intermediate CAs for the web site. ACL was also set up correctly. However, we still got the same error with either correct or incorrect wallet password.
SQL*Plus: Release 12.1.0.2.0 Production on Thu Feb 15 11:10:32 2018
SQL> select utl_http.request('https://sni.example.com/test.html', NULL,'file:/nfs/wallet12c','walletpass') from dual;
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-29259: end-of-input reached
ORA-06512: at "SYS.UTL_HTTP", line 1491
ORA-06512: at line 1
Checking server certificate with openssl client command line returned error and no certificate chain. Really weird!
-bash-4.2$ openssl s_client -connect sni.example.com:443
CONNECTED(00000003)
write:errno=104
---
no peer certificate available
---
No client certificate CA names sent
---
SSL handshake has read 0 bytes and written 289 bytes
---
New, (NONE), Cipher is (NONE)
Secure Renegotiation IS NOT supported
Compression: NONE
Expansion: NONE
No ALPN negotiated
SSL-Session:
Protocol : TLSv1.2
Cipher : 0000
Session-ID:
Session-ID-ctx:
Master-Key:
Key-Arg : None
Krb5 Principal: None
PSK identity: None
PSK identity hint: None
Start Time: 1518719121
Timeout : 300 (sec)
Verify return code: 0 (ok)
---
Googling the internet or searching Oracle support site did not return any useful information until I found this discussion on Oracle Developer Community. Though the error message in the discussion was different, I thought I should still check if my remote web server is using SNI (Server Name Indication) for HTTPS.
No error was returned if server name is specified.
-bash-4.2$ openssl s_client -connect sni.example.com:443 -servername sni.example.com CONNECTED(00000003) depth=3 C = SE, O = AddTrust AB, OU = AddTrust External TTP Network, CN = AddTrust External CA Root verify return:1 depth=2 C = US, ST = New Jersey, L = Jersey City, O = The USERTRUST Network, CN = USERTrust RSA Certification Authority verify return:1 depth=1 C = US, ST = MI, L = Ann Arbor, O = Internet2, OU = InCommon, CN = InCommon RSA Server CA verify return:1 depth=0 C = US, (...omitted...), CN = sni.example.com verify return:1 --- Certificate chain (...omitted...) --- No client certificate CA names sent Peer signing digest: SHA1 Server Temp Key: ECDH, P-256, 256 bits --- SSL handshake has read 4939 bytes and written 510 bytes --- New, TLSv1/SSLv3, Cipher is ECDHE-RSA-AES256-SHA384 Server public key is 2048 bit Secure Renegotiation IS supported Compression: NONE Expansion: NONE No ALPN negotiated SSL-Session: (...omitted...) Verify return code: 0 (ok) ---
Seemed like the web server was returning SNI header information in its ServerHello response.
-bash-4.2$ openssl s_client -connect sni.example.com:443 -servername sni.example.com -tlsextdebug -msg 2> /dev/null | grep "server name" TLS server extension "server name" (id=0), len=0
Our Windows system administrator confirmed that SNI was indeed enabled on the server. The only way to get utl_http to work is to disable SNI since Oracle bug 22707400 stated that utl_http on Oracle 12.1 would fail when SNI extension was enabled on the web server. According to Oracle, the bug was fixed in V18.1. There’s an enhancement request to support SNI in 12.2 Database. For how to disable SNI, please refer to this page.
After SNI was disabled, utl_http worked like a charm!
SQL> select utl_http.request('https://sni.example.com/test.html', NULL,'file:/nfs/wallet12c','walletpass') from dual;
UTL_HTTP.REQUEST('HTTPS://SNI.EXAMPLE.COM/TEST.HTML
--------------------------------------------------------------------------------