Oracle databases have the ability to integrate with many applications and systems today. Web services are frequently used during these integrations. However, these integrations do not always occur smoothly. The problem we encountered occurred when calling a web service API from the Oracle database via PL/SQL code. This web service had a critical role in the application, so we had to solve this problem.
The issue appeared when tried to access a web service using the HTTPS (TLS.1.2) protocol. The client (Oracle database in this case) encountered an unexpected error message..
Web service call using the example code of which you can see below, resulted in ORA-29253 and ORA-29259 errors.
select
utl_http.request('https://testerman.example.com/WebService/ErmService.asmx',
null, 'file:/oracle/PROD/db/tech_st/12.1.0/admin/PROD/wallet',
'wallet_password') from dual;
ORA-29263: HTTP protocol error
ORA-29259: end-of-input reached
---
These error messages, at first glance, indicate that the call to the web service failed and the expected response was not received. But what was the real reason behind these errors? We had to do an in-depth analysis to find the answer to this question. I will share with you our findings regarding that in-depth analysis and the solutions we implemented using the output of the analysis.
While performing the analysis, we went through the following document to identify SSL/TLS issues between the database and the web server: MOS note "How To Investigate And Troubleshoot SSL/TLS Issues on the Database And Client SQL*Net Layer (Doc ID 2238096.1)"
Based on the MOS document above, we performed many checks and enabled different types of traces (tcpdump, sqlnet.ora, listener). We examined the trace files in detail. We checked the wallet permissions and made sure that they are correct. Likewise, we examined the certificates of the relevant HTTPS web server and concluded that there was no problem there either. As a result of these investigations, we agreed that the problem was not related to the wallet or certificates.
Considering the SSL/TLS version we were using (TLS 1.2), we checked the PSU level of the database.. The PSU version was not the highest one, but it was high enough and anything higher would not have added anything to us.. However, in addition to all these checks, the network traces we received with tcpdump revealed some great details.
The command used for getting the relevant trace using the tcpdump was as follows;
/usr/sbin/tcpdump -i <interface or any> -s0 -w
<output filename>
Example command: /usr/sbin/tcpdump -i eth0 -s0 -w ssl_dump.cap
Note that we analyzed the tcpdump traces with Wireshark...
In parallel to that, we also continued to review other Oracle documentation and MOS notes, specifically when we were investigating the source of "ORA-29259: end-of-input reached" and "ORA-29263: HTTP protocol error".
These errors could also be related to wallet access permissions. However, the changes we made in this direction did not solve the problem (ACE or ACL permissions didn't help, the cause was not related with those things.) -- a reference : MOS note : "ORA-29259: end-of-input reached" or "ORA-29263: HTTP protocol error" when using HTTPS with a Wallet File (Doc ID 2303905.1)")
We also checked the ciphers as there might be a missing cipher in the database.. However, we saw that the patches correcting the potential missing ciphers were already applied to the client side (the Oracle database 12.1 in this case) -- a reference: MOS Note: "UTL_HTTP access to secured website fails with ORA-29259 in 12c database (Doc ID 2402276.1)" (Note that, we also used openssl to check the ciphers requested by the server)
The document above given as the reference contained the following statement: The issue is due to missing cipher suits in 12c.
TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256 and ECDHE_RSA_WITH_AES_128_GCM_SHA256 were in question, but we had them in the database TLS layer, so it wasn't the cause.
We even explicitly defined the ciphers that we wanted to use in sqlnet.ora and listener.ora, but this action (as expected) didn't solve the problem either.
SSL_CIPHER_SUITES = (SSL_DH_anon_WITH_DES_CBC_SHA,
SSL_DH_anon_WITH_3DES_EDE_CBC_SHA,SL_DH_anon_WITH_RC4_128_MD5,SSL_RSA_WITH_AES_256_CBC_SHA)"
We checked lots of things at many different areas and the known issues along with the possible bugs related with TLS 1.2 and Oracle Database 12.1 carefully.
After all these diagnostics and solution attempts, we turned back our focus to the findings we had with tcpdump.
The most important finding there, was the proof of SNI (Server Name Indication) extension usage during this problematic TLS 1.2 Web Service call.
Handshake Protocol: Client Hello
Handshake Type: Client
Hello (1)
Length: 290
Version: TLS 1.2
(0x0303)
Random
Session ID Length: 0
Cipher Suites Length:
128
Cipher Suites (64
suites)
Compression Methods
Length: 1
Compression Methods (1
method)
Extensions Length: 121
Extension: server_name
Type: server_name (0x0000)
Length: 33
Server Name Indication extension!!!!!! -- HERE IT IS!
Server Name list length: 31
Server Name Type: host_name (0)
Server Name length: 28
Server Name: test.server.com
Extension: ec_point_formats
The SNI feature was not supported in Oracle Database version 12.1 and it was the cause of the problems.. In order to use SNI , the database version should be at least 12.2. Additionally, this SNI feature could not be backported to version 12.1.
Server Name Indicator (SNI) is an extension of the TLS protocol. At the beginning of the handshake process when establishing a secure SSL connection, it verifies the digital certificate of the server name (hostname) mentioned in the request. SNI technology allows multiple secure websites to be accessed via the same IP address and TCP port. "Since it performs the verification process for SSL, it eliminates the fixed IP requirement that was previously required to use SSL." source: https://tr.wikipedia.org/wiki/Server_Name_Indication
Even the latest database PSU release for 12.1 wouldn't solve the problem, since there was no backport..
We shared our findings about this SNI feature with the website administrator and asked whether this feature could be disabled on the web server side. They said they could disable the SNI only in their TEST environment, and we made them do so. We tired the web service call after the web server administrator disabled the SNI in the TEST environment , and the problem disappeared.
On the other hand, the web server administrator (and the company that owns the web service) stated that they cannot disable SNI for the PROD environment.
This situation led us to find different solutions or workarounds.
Upgrading the version of the database (12.1 -> 12.2 or 19C upgrade) would solve this problem, but this kind of an upgrade would require an intensive testing process and it did not seem possible at that moment, considering the customer have no resources and motivation for such a task.
Well.. DB upgrade was not an option.. There was no solution for 12.1 Oracle Database ( No backports for SNI capability due to the inadequacy of the DB TLS code of 12.1)
Therefore, we started looking for alternative solutions.
Ability to look from differences perspectives, and produce flexible solutions to overcome the challenges that our customer face; let us implement a method of accessing the web service by calling a function through a "Database" that acts as an intermediate proxy.
This was actually a workaround.. It required less efforts for the implementation and did not directly affect the existing system.
The solution we planned was to access the web service through a database link (DB Link) residing in a 12.2 Oracle database.
In this solution, the database in the middle (Oracle Database 12.2) provides access to a specific websi service with a function. However, what is interesting here is that it transmits this data directly to the 12.1 database via db link. However, the fact that DB Link does not support the CLOB data type appeared as an obstacle at the beginning. To get around this problem, we wrote a custom CLOB parser function and deployed it to the 12.1 database. Thanks to this function, we could receive the HTML response from the website without any problems.
-- 12.1 Database (The source)
CREATE OR REPLACE FUNCTION
APPS.fetch_https_content_via_link(p_url IN VARCHAR2) RETURN CLOB IS
l_clob clob;
l_tmp long;
l_offset number := 1;
begin
loop
select dbms_lob.substr@https_link(dblink.fetch_https_content@https_link(p_url),
4000, l_offset)
into l_tmp
from dual@https_link;
exit when l_tmp is null;
l_clob := l_clob || l_tmp;
l_offset := l_offset + length(l_tmp);
end loop;
return l_clob;
END fetch_https_content_via_link;
/
-- 12.2 Database -- the intermediate / proxy environment
CREATE OR REPLACE FUNCTION dblink.fetch_https_content(p_url
IN VARCHAR2) RETURN CLOB IS
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_content CLOB;
BEGIN
UTL_HTTP.SET_WALLET
('file:/orahome/LINK/db_122/product/12.2.0.1/db_1/wallet','wallet_password');
-- Make the HTTP request
l_http_request :=
UTL_HTTP.begin_request(p_url);
l_http_response :=
UTL_HTTP.get_response(l_http_request);
-- Read the response content
UTL_HTTP.read_text(l_http_response,
l_content);
-- Close the HTTP request and
response
UTL_HTTP.end_response(l_http_response);
RETURN l_content;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
RETURN l_content;
WHEN OTHERS THEN
RETURN 'Error
fetching content';
END fetch_https_content;
/
