Re: Getting "ORA-29024: Certificate validation failure", when using UTL_HTTP to POST to Google Checkout URL

From: Jimbo1 <jamestheboarder_at_googlemail.com>
Date: Thu, 19 Feb 2009 11:53:01 -0800 (PST)
Message-ID: <81b053db-af2a-4d6b-a444-71eda9763283_at_p29g2000vbn.googlegroups.com>

On Feb 19, 1:32 am, Jimbo1 <jamestheboar..._at_googlemail.com> wrote:
> On Feb 18, 6:54 pm, joel garry <joel-ga..._at_home.com> wrote:
>
>
>
> > On Feb 17, 2:07 pm, Jimbo1 <jamestheboar..._at_googlemail.com> wrote:
>
> > > Hello there,
>
> > > I'm running Oracle 10g Release2, XE Edition.
>
> > > I'm trying to use the UTL_HTTP Package to submit a POST request to the
> > > Google Checkout Test Server.
>
> > > Now, if I run my script to try to send a POST request tohttp://www.google.co.uk,
> > > it works, i.e.
>
> > > DECLARE
> > >    l_url VARCHAR2(200) := 'http://www.google.co.uk';
> > >    l_request      UTL_HTTP.REQ;
> > >    l_response     UTL_HTTP.RESP;
> > > BEGIN
> > >    l_request := UTL_HTTP.BEGIN_REQUEST( l_url , 'POST' );
> > >    UTL_HTTP.END_REQUEST( l_request ) ;
> > > END;
> > > /
>
> > > However, if I run it to post to the "https://"-prefixed address, the
> > > following script will get an ORA-29024 Exception, i.e.
>
> > > DECLARE
> > >    l_merchant_url VARCHAR2(200) := 'https://sandbox.google.com/
> > > checkout/api/checkout/v2/reports/Merchant/'||'&MERCHANT_ID';
> > >    l_request      UTL_HTTP.REQ;
> > >    l_response     UTL_HTTP.RESP;
> > > BEGIN
> > >    l_request := UTL_HTTP.BEGIN_REQUEST( l_merchant_url, 'POST' );
> > >    UTL_HTTP.END_REQUEST( l_request ) ;
> > > END;
> > > /
>
> > > The full exception string I'm getting is:
>
> > > ORA-29273: HTTP request failed
> > > ORA-06512: at "SYS.UTL_HTTP", line 1029
> > > ORA-29024: Certificate validation failure
> > > ORA-06512: at line 8
>
> > > I entered my sandbox MERCHANT_ID when prompted for the substitution
> > > variable above.
>
> > > Now, the questions I have are:
>
> > > 1. Do I need to obtain some kind of SSL Certificate from Google?
> > > 2. If the answer to the last question is "Yes", do I then need to
> > > register it in the database using Wallet Manager?
>
> > > Thanks in advance for any help or advice.
>
> > > James
>
> > I claim to know nothing about it, but seehttp://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_http...
>
> > or google set_wallet.
>
> > jg
> > --
> > _at_home.com is bogus.http://www3.signonsandiego.com/stories/2009/feb/18/1b18stanford2200-s...
>
> Thanks Joel,
>
> I'm actually on my way down that route at the moment. I'm starting to
> take a look at the Oracle Advanced Security manual and how to set up
> wallets for use with SSL. I'll post up my solution on this thread, if
> I can solve it.
>
> I'm into very new territory here for me. I'd describe myself as a very
> competent Oracle database developer, but very green when it comes to
> networks and communication protocols, etc........... I usually work as
> a contractor in medium - large companies, so all that kind of thing is
> dealt with by network people. This piece of work is for a friend's
> small business website, built using APEX and running on an Oracle XE
> database. The only techies are me and him, and we're both primarily
> database developers.
>
> This is certainly going to be an interesting challenge! ;)

I've solved this problem and am now successfully talking to the Google Sandbox Server with UTL_HTTP via a Secure Socket Layer (SSL).

I'm going to roughly document my approach here, as this entire experience has been pretty frustrating and has taken me around 8 hours to reach the solution.

Firstly, it is absolutely essential to obtain the SSL certificates for Google's Live Checkout Server, and the Sandbox (Test) Server. You can obtain those via Internet Explorer; the instructions can be found on this blog: http://eternal-donut.blogspot.com/2008/07/tip-5-using-utlhttp-and-ssl.html

The URLs I obtained my certificates from were:

1. https://checkout.google.com/checkout/sell/ (Live Server)
2. https://sandbox.google.com/checkout/sell/ (Test Server)

The only thing to bear in mind is that when it comes to choosing an Export File Format for the Certificate, do NOT choose the "Base-64 encoded X.509 (.CER)" format. Instead, choose the "Cryptographic Message Syntax Standard - PKCS #7 Certificates (.P7B)" option, and tick the checkbox for "Include all certificates in the certification path if possible".

You can also export the certificate from Firefox, via the "Page Info" Window.

After obtaining the certificates, use Oracle's Wallet Manager Software to import them into your Wallet. Instructions for that can be found in the Oracle Advanced Security Manual.

When it comes to testing using UTL_HTTP, you MUST ensure that you open a new session after importing the SSL certificates into your Wallet, as I've learned (the hard way) that existing sessions point to the wallet contents that were present when the session was opened. If you don't realise/know this, it can cause a lot of additional frustration during testing, when you keep getting the ORA-29024 exception AFTER

you've imported the SSL certificates................. ;)

Once you've got the Wallet set up, run the following test script to ensure you can see the wallet contents from the database session:

SELECT UTL_HTTP.REQUEST('https://metalink.oracle.com',

                        NULL,
                        'file:'||'<wallet_location>',
                        '<wallet_password>')

  FROM dual;'

 NOTE 1: <wallet_location> represents the path to your wallet file that you set up in Wallet Manager. In my case, it's "c:\oraclexe \wallets\". Do NOT include the wallet filename and extenstion in this path.

NOTE 2: <wallet_password> is the password to your wallet that you set up in Wallet Manager.

If you've configured everything correctly, you will get an HTML response back from the metalink server.

For Google Checkout, all I then needed to do was run this test query:

SELECT UTL_HTTP.REQUEST('https://sandbox.google.com/checkout/api/ checkout/v2/checkout/Merchant/&MY_MERCHANT_ID',

                        NULL,
                        'file:'||'<wallet_location>',
                        '<wallet_password>')

  FROM dual;

If this is successful, you will get an HTML page back from the Google Checkout Sandbox Server.

Hopefully, this post will be of great assistance to anybody else who's trying to get Oracle talking to Google Checkout Server to Server. Received on Thu Feb 19 2009 - 13:53:01 CST