ORA-28860: Fatal SSL error when using UTL_HTTP?

Question

We use Oracle 11g (11.2.0.3.0), and when making the UTL_HTTP call, we get the following error:

EXCEPTION: ORA-28860: Fatal SSL error EXCEPTION: ORA-06512: at "SYS.UTL_HTTP", line 1128 ORA-06512: at line 23 EXCEPTION: ORA-28860: Fatal SSL error 

This is the code we use:

 DECLARE url_chr VARCHAR2(500); user_id_chr VARCHAR2(100); password_chr VARCHAR2(20); wallet_path_chr VARCHAR2(500); wallet_pass_chr VARCHAR2(20); l_http_request UTL_HTTP.REQ; l_http_response UTL_HTTP.RESP; l_text VARCHAR2(32767); BEGIN url_chr := '*****'; user_id_chr := '*****'; password_chr := '*****'; wallet_pass_chr := '*****'; wallet_path_chr := 'file:/etc/ORACLE/WALLETS/astens/rtca/cer/'; UTL_HTTP.SET_DETAILED_EXCP_SUPPORT(TRUE); UTL_HTTP.SET_WALLET(wallet_path_chr, wallet_pass_chr); l_http_request := UTL_HTTP.BEGIN_REQUEST(url_chr); UTL_HTTP.SET_AUTHENTICATION(r => l_http_request, username => user_id_chr, PASSWORD => password_chr); l_http_response := UTL_HTTP.GET_RESPONSE(l_http_request); DBMS_OUTPUT.PUT_LINE ('STATUS_CODE : ' || l_http_response.STATUS_CODE); BEGIN LOOP UTL_HTTP.READ_TEXT(l_http_response, l_text, 32766); DBMS_OUTPUT.PUT_LINE (l_text); END LOOP; EXCEPTION WHEN UTL_HTTP.END_OF_BODY THEN UTL_HTTP.END_RESPONSE(l_http_response); END; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('EXCEPTION: '||SQLERRM); DBMS_OUTPUT.PUT_LINE('EXCEPTION: '||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); DBMS_OUTPUT.PUT_LINE('EXCEPTION: '||UTL_HTTP.GET_DETAILED_SQLERRM); UTL_HTTP.END_RESPONSE(l_http_response); END; 

We installed the supplied certificates in Oracle Wallet, and we use the same code for different customers without any problems.

Any ideas?

Answer 1

The site you are calling may interfere with the connection through the legacy SSLv3 protocol, and at the same time, the newer algorithm may not be supported by Oracle DB 11.2.0.3.

There is this known bug, but it affects versions prior to 11.1, obviously:

The UTL_HTTP package does not work with ORA-29273 ORA-28860 when using TLSv1 (Doc ID 727118.1) https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=842518171804826&id=727118.1&_afrWindowft=142_14_1_1_1

Recently, error 20323753 was also registered, registered for 11.2.0.4, but not fixed. Perhaps this may be the same case as yours.

Answer 2

You do not specify your network access control lists (ACLs), but in Oracle 11g you must configure the ACLs for the host you want to connect to and the wallet you want to use. Since you are not mentioning the error "ORA-24247: denial of network access through access control (ACL)", I assume that this part is configured correctly.

The wallet ACL determines its location and provides privileges against the wallet to users. Without these privileges, Oracle will not open a wallet or provide a certificate to a web server, even if you have the correct password. A wallet ACL is created with the following PL / SQL run as SYS:

 BEGIN UTL_HTTP.ASSIGN_WALLET_ACL ( acl => 'your_acl_name.xdb', wallet_path => '/path/to/my/wallet/'); END; / 

After creating the wallet ACL, the user must have privileges granted to him.

 BEGIN DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE( acl => 'your_acl_name.xml', principal => 'MY_USER', is_grant => TRUE, privilege => 'use-client-certificates'); END; / 

This will allow Oracle to open the wallet on behalf of your user and submit the certificate to the web server.

Answer 3

I would suggest the following:

• Create a JAVA Function

   CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "HttpSSLGet" AS import java.net.URL; import java.io.*; import javax.net.ssl.HttpsURLConnection; public class HttpSSLGet { public static String GetSSL(final String url) { StringBuffer buffer = new StringBuffer(); try { URL myUrl = new URL(url); HttpsURLConnection con = (HttpsURLConnection)myUrl.openConnection(); InputStream ins = con.getInputStream(); InputStreamReader isr = new InputStreamReader(ins); BufferedReader in = new BufferedReader(isr); String inputLine; while ((inputLine = in.readLine()) != null) { buffer.append(inputLine); } in.close(); } catch (Exception e) { return buffer.toString() + "\n" + e.toString(); } return buffer.toString(); } } 

• Create a PL / SQL package (stand-alone function)

   CREATE OR REPLACE PACKAGE PCK_HTTP AUTHID DEFINER AS function GetSSL(aUrl Varchar2) return Varchar2; END; / CREATE OR REPLACE PACKAGE BODY PCK_HTTP AS function GetSSL(aUrl Varchar2) return Varchar2 AS LANGUAGE JAVA NAME 'HttpSSLGet.GetSSL(java.lang.String) return java.lang.String'; END; / 

• In Oracle, there is a problem with the built-in JAVA machine. It contains fewer certificates as a standard JAVA "satanalon". You should probably add the downloaded certificate to the embedded java machine (not stand-alone java), for example. on the command line (Windows):

   keytool -import -alias geos -keystore "d:\Oracle\product\11.2.0\dbhome_1\javavm\lib\security\cacerts" -file example.com.cer -storepass changeit 

• Use a function in a query or PL / SQL, for example,

   SELECT PCK_HTTP.GetSSL('https://www.example.com') FROM DUAL 

Answer 4

We found that the old certificates in the wallet for the https website, although they have not expired, can no longer be used. A test with new certificates in a new wallet proved this. Removing the old certificate and adding new certificates to the original wallet solved the problem.