2 way ssl authentication in calling webservice from plsql

Question

I am trying to call a third party web-service from a PL/SQL block.

For using SSL I downloaded the certificate from website and installed in an Oracle wallet.

Now the client wants a 2 way SSL authentication. How to achieve this? Do I need to add anything else in my HTTP call?

Below is the code I am using.

create or replace function GetDeptInfo( arg0 number ) return XmlType is
    --// URL to call
    SOAP_URL constant varchar2(1000) := 'https://google.com:7002/WebService-Annotation-context-root/MyCompanyPort';

    SOAP_ENVELOPE constant varchar2(32767) :=
        '<env:Envelope xmlns:env="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ns1="http://annotation/">
             <env:Header/>
             <env:Body>
                 <ns1:getDeptInfo>
                          <arg0>$arg0</arg0>
                 </ns1:getDeptInfo>
             </env:Body>
         </env:Envelope>';

    --// we'll identify ourselves using an IE9/Windows7 generic browser signature
    C_USER_AGENT    constant varchar2(4000) := 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)';

    --// these variables need to be set if web access
    --// is via a proxy server
    proxyServer varchar2(20) default null;
    proxyUser   varchar2(20) default null;
    proxyPass   varchar2(20) default null;

    --// our local variables
    soapEnvelope    varchar2(32767);
    proxyURL        varchar2(4000);
    request         utl_http.req;
    response        utl_http.resp;
    buffer          varchar2(32767);
    soapResponse    clob;
    xmlResponse     XmlType;
    eof             boolean;
begin
    --// create the SOAP envelope
    soapEnvelope := replace( SOAP_ENVELOPE, '$arg0', arg0 );

    --// our "browser" settings
    utl_http.set_response_error_check( true );
    utl_http.set_detailed_excp_support( true );
    utl_http.set_cookie_support( true );
    utl_http.set_transfer_timeout( 10 );
    utl_http.set_follow_redirect( 3 );
    utl_http.set_persistent_conn_support( true );

    --// configure for web proxy access if applicable
    if proxyServer is not null then
        proxyURL := 'http://'||proxyServer;
        if (proxyUser is not null) and (proxyPass is not null) then
            proxyURL := Replace( proxyURL, 'http://',  'http://'||proxyUser||':'||proxyPass||'@' );
        end if;
        utl_http.set_proxy( proxyURL, null );
    end if;

    --// make the POST call to the web service
    UTL_HTTP.set_wallet('file:/tmp/DEVF1MB/wallet', 'WalletPasswd123');
    request := utl_http.begin_request( SOAP_URL, 'POST', utl_http.HTTP_VERSION_1_1 );
    utl_http.set_header( request, 'User-Agent', C_USER_AGENT );
    utl_http.set_header( request, 'Content-Type', 'text/xml; charset=utf-8' );
    utl_http.set_header( request, 'Content-Length', length(soapEnvelope) );

    utl_http.set_header( request, 'SoapAction', 'http://www.webserviceX.NET/GetWeather' );
    utl_http.write_text( request, soapEnvelope );

    --// read the web service HTTP response
    response := utl_http.get_response( request );
    dbms_lob.CreateTemporary( soapResponse, true );
    eof := false;
    loop
        exit when eof;
        begin
            utl_http.read_line( response, buffer, true );
            if length(buffer) > 0 then
                dbms_lob.WriteAppend(
                    soapResponse,
                    length(buffer),
                    buffer
                );
            end if;

        exception when utl_http.END_OF_BODY then
            eof := true;
        end;

    end loop;
    utl_http.end_response( response );

    --// as the SOAP responds with XML, we convert
    --// the response to XML
    xmlResponse := XmlType( soapResponse );
    dbms_lob.FreeTemporary( soapResponse );

    return( xmlResponse );

exception when OTHERS then
    if soapResponse is not null then
        dbms_lob.FreeTemporary( soapResponse );
    end if;
    raise;
end;

Answer 1

Here is my 2 cents: (and it worked ;-) )

First, I had success in calling a web service using utl_http, to a closed wsdl which was an http URL.

Next, when the web-service host changed the url to https, I still had success after I imported their certificate into my Wallet. And had this statement before doing a begin_request:

UTL_HTTP.SET_WALLET ('file:/tmp/wallet/dp_wallet','pass1234');

(I was testing it in tmp folder where I had privileges to test on). It also worked, until they enforced two-way authentication on. And I had been looking for a way to SEND (present) my certificate while making the call. I didn't have success: Fatal SSL Error.

Few tricks that helped and might be useful to you:

1. DO NOT use wallet password as "password". Or anything simple. Please use numbers. The error message if at all, is not obvious. I used smart1234 and it worked.

2. You must have added resolve, connect to the current user into network acl. PLEASE ADD The following as well to wallet acls (this made the difference):

   BEGIN
       DBMS_NETWORK_ACL_ADMIN.ASSIGN_WALLET_ACL('www.xml','file:/tmp/wallet/dp_wallet');
   END;
   /
   COMMIT;
   

3. Provide read access to world (just in case). The error is easy to identify as such. It would be: "failure to open file"

4. Using the testing sql is a good idea, but does not mean you have success in actual begin_request:

   SELECT utl_http.request('https://dpserver:4401/tuxops/LookUp',
                           '',
                           'file:/tmp/wallet/dp_wallet',
                           'smart1234')
   FROM dual;
   

5. Log out and log back in from this user!

6. Sometimes it takes a few minutes to take effect! I had a hard time testing because I ignored these two.

7. If you provide some wrong password or some other failure 3 or more times, you might have a latency in success. Easy option is to logout and log back in. That resets the session errors.

Hope my findings is of use to you :)

Answer 2

Conceptually, you need to exchange SSL certificate with third party, generated in your client for 2 way SSL. It totaly depends on web service (wsdl) provider's configuration. Normally there are two types of configuration is there for webservice in https mode.

1. https without client authentication
2. https with client authentication

If provider has configured his webservice as "https with client authentication" then you have to exchange SSL certificates with him (2 way SSL). In this case you also need to provide SSL certificate generated from your client to install at third party server and vice versa. But it works only if provider has configured to communicate this way with you for that webservice.

In second option, normally trusting third party webservice end point url at your (client) side works, no need to exchange certificates.