Re: Getting "ORA-29024: Certificate validation failure", when using UTL_HTTP to POST to Google Checkout URL
From: Palooka <nobody_at_nowhere.com>
Date: Thu, 19 Feb 2009 23:29:50 +0000
Message-ID: <RXlnl.9985$Ii4.6715_at_newsfe19.ams2>
Jimbo1 wrote:
> On Feb 19, 1:32 am, Jimbo1 <jamestheboar..._at_googlemail.com> wrote:
>
> 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.
>
>
Most useful. Thanks, Jimbo.
Date: Thu, 19 Feb 2009 23:29:50 +0000
Message-ID: <RXlnl.9985$Ii4.6715_at_newsfe19.ams2>
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.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.
>
>
Most useful. Thanks, Jimbo.
Palooka Received on Thu Feb 19 2009 - 17:29:50 CST