CODEWITHSUNDEEP
oracle-databasesslplsqloracle11gcertificate
Steve
Steve

Reputation: 475

ORA-28860: Fatal SSL error when using UTL_HTTP?

We are using Oracle 11g (11.2.0.3.0) and we are receiving the following error when executing a UTL_HTTP call:

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 are using:

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 have installed the supplied certificates into the Oracle Wallet, and we use the same code for different clients without issues.

Any ideas?

Upvotes: 4

Views: 39676

Answers (4)

Saroj Bhumbla
Saroj Bhumbla

Reputation: 1

We found that old certificates in wallet for https site even though not expired was no longer usable. Test with new certs in new wallet proved that. Removing old cert and adding new certs to the original wallet fixed the issue.

Upvotes: 0

Sergiy Balter
Sergiy Balter

Reputation: 11

I'd like to offer the following:

  1. 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();
  }
}

  2. Create a PL/SQL Package (of standalone 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;
/

  3. There is a problem with the built-in JAVA-machine in Oracle. It contains less certificates as standarte "satandalone" JAVA. Probably, you should add a downloaded certificate to built-in java machine (not standalone java), for ex. in 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

  4. Use function in query or PL/SQL, for eg.

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

Upvotes: 1

James Schrumpf
James Schrumpf

Reputation: 83

You don't mention your network Access Control List (ACL) grants, but in Oracle 11g you must set up an ACL for both the host you want to connect to and for the wallet you want to use. Since you don't mention getting the "ORA-24247: network access denied by access control list (ACL)" error, I'll assume that part is set up properly.

The wallet ACL defines its location and grants privileges against the wallet to users. Without these privileges, Oracle will not open the wallet and present the certificate to the web server, even if you have the correct password. The 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 the wallet ACL is created, the user must have privileges granted to it.

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

That will allow Oracle to open the wallet on your user's behalf and present the certificate to the web server.

Upvotes: 4

Wanted
Wanted

Reputation: 421

The site you're calling could be preventing connections via outdated SSLv3 protocol and at the same time, a newer algorithm might not be supported by Oracle DB 11.2.0.3.

There is this known bug, but it affects versions up to 11.1 apparently:

UTL_HTTP Package Fails With ORA-29273 ORA-28860 When Using TLSv1 (Doc ID 727118.1) https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=842518171804826&id=727118.1&_afrWindowMode=0&_adf.ctrl-state=142oqbz21t_4

There is also a bug 20323753 registered for 11.2.0.4 recently, still not fixed. Possibly could be the same case as yours.

Upvotes: 4

Related Questions