Re: Getting "ORA-29024: Certificate validation failure", when using UTL_HTTP to POST to Google Checkout URL
From: Jimbo1 <jamestheboarder_at_googlemail.com>
Date: Sun, 22 Feb 2009 06:14:02 -0800 (PST)
Message-ID: <130ec7d0-84f2-425f-85e7-f594c2a486b5_at_s14g2000vbp.googlegroups.com>
On Feb 19, 11:29 pm, Palooka <nob..._at_nowhere.com> wrote:
> Jimbo1 wrote:
> > 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...
>
> > 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.
>
> Most useful. Thanks, Jimbo.
>
> Palooka
Date: Sun, 22 Feb 2009 06:14:02 -0800 (PST)
Message-ID: <130ec7d0-84f2-425f-85e7-f594c2a486b5_at_s14g2000vbp.googlegroups.com>
On Feb 19, 11:29 pm, Palooka <nob..._at_nowhere.com> wrote:
> Jimbo1 wrote:
> > 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...
>
> > 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.
>
> Most useful. Thanks, Jimbo.
>
> Palooka
No worries; my pleasure. ;)
Just one little thing I overlooked. If you want to use Firefox to capture SSL certificates, you must have the "Certificate Viewer Plus" Add-on installed. Received on Sun Feb 22 2009 - 08:14:02 CST