11 Replies Latest reply on Oct 16, 2018 9:49 AM by Bas de Klerk

    utl_http.request on ssl site fails with ORA-29024

    Bas de Klerk



      This question is more db related then apex but I think the most knowledge related to https from the db is probably this place.

      I've tried this on version 11.2, 12.1 and 12.2

      Since a short time an interface used from an apex app is failing.

      A short example of what fails is below, I think the problem is related to the server where the request is send to since other ssl sites don't have this issue.

      We try to open an ssl connection to a site but it fails with ORA-29024 ( validation of certificate failed ).

      The root certificate of the site ( which is entrust.net ) is installed in the wallet.

      I've tried to create a clean new wallet, import the entrust root certificate which I downloaded from the site which I try to access. But after doing a utl_http.request it fails.

      Any insights on why this goes wrong, maybe a specific protocol version which is not supported or do I need to do some additional stuff for this entrust cert ?



      l_dummy varchar2(32000);


      UTL_HTTP.set_wallet('file:d:\<<wallet location>>\', '<<wallet password>>');

      SELECT utl_http.request('https://login.twinfield.com/') into l_dummy from dual;



      Hope someone can point me in the right direction how to solve this.




        • 1. Re: utl_http.request on ssl site fails with ORA-29024

          Hello Bas,


          To my experience, most of the time is the wallet construction itself that's the root of the problem.


          First, I'd recommend to check the proper steps here:


          Special attention to not adding the host's certificate to the wallet, but only CA and "middle" CA, since it won't work on DD.BB. 12.2 onward, I believe.


          Then, as an alternative to UTL_HTTP, I'd recommend trying with APEX_WEB_SERVICE:


          select apex_web_service.make_rest_request(p_url => 'https://www.google.com', p_http_method => 'GET'
          , p_wallet_path => 'file:/home/oracle/apex_wallet'
          , p_wallet_pwd => 'xxxx') from dual;


          Hope it helps.


          Kind regards,


          • 2. Re: utl_http.request on ssl site fails with ORA-29024

            Hello Bas,


            You have to create the wallet with both intermediate and root certificate.


            Best regards

            • 3. Re: utl_http.request on ssl site fails with ORA-29024
              Bas de Klerk

              Hi Sergio,


              Thanks, but I already created a new wallet ( using the oracle-base) steps.

              Wallet works fine, tested this with multiple other CA's, before importing them into the wallet I got the ORA-29024 and after importing them it works fine, as expected. So far this only fails with the mentioned site which uses entrust as CA. As you suggested I also tried apex_web_service to do the request but I got the same error.

              So my guess is that it has something to do with the CA but not sure how to solve this and what the exact cause is.

              B.t.w I do not import the sites CRT, only the root CA ( but I've also tested using the root CA and the intermediate CA, tried it with the root first and intermediate second and also vice versa, not 100% sure if the order matters but just to be sure ).




              • 4. Re: utl_http.request on ssl site fails with ORA-29024
                Bas de Klerk

                Hi Rob,


                Thanks, but already tried that and it did not work ( also see reply to Sergio's answer for all details ).




                • 5. Re: utl_http.request on ssl site fails with ORA-29024

                  Hi Bas,

                  I don't think it makes sense to even try on 11.2 (the other numbers are also important), at least not on XE which is 2.0 because it does not support SHA-2 based certificates. Unfortunately I cannot test on 12.1 but definitely it should work on 12.2.

                  Well, the sad thing is that I did not manage to make it working no matter what I tried. In all cases so far it just needed to carefully follow this https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/6121/index-en.html Carsten's excellent article (to import the certificate properly) combined with additional info about the (relatively) new https_host parameter Re: error ORA-24263 with social login microsoft provided in this thread. Unfortunately with no luck this time, so I'm afraid there is the only way to ask the greatest Guru Carsten Czarski-Oracle himself.

                  I think I did everything properly and this statement

                  select utl_http.request(url => 'https://login.twinfield.com/auth/authentication/login?signin=a00445827bd71a6ffd631a20ea05b051', 
                  wallet_path => 'file:/u01/app/oracle/product/12.2/db_1/owm/wallets/oracle', 
                  wallet_password => 'wallet_pwd', 
                  https_host => 'incapsula.com')
                    from dual;

                  still stubbornly produces ORA-29024: Certificate validation failure exactly as you described (the https_host => 'incapsula.com' I found using the command openssl s_client -showcerts -connect login.twinfield.com:443).

                  Anyway, there is no difference between apex_web_service and utl_http (certificate-wise) because apex_web_service uses utl_http under the hood, so both will produce the very same error if there is something wrong with certificates, thus for debugging purposes seems to be the low-level utl_http a better option.



                  • 6. Re: utl_http.request on ssl site fails with ORA-29024

                    Hello Bas,


                    Pardon for being so insistent. I've tried the following and I believe it worked (tried on a 18c, but if needed can try it on a 12.1 I think):


                    1. Download root and intermediate CA from IE in X.509 base64 format
                    2. orapki wallet create -wallet /tmp/apex_wallet -pwd ThisIs2018 -auto_login
                    3. orapki wallet add -wallet /tmp/apex_wallet -trusted_cert -cert root_CA.cer -pwd ThisIs2018
                    4. orapki wallet add -wallet /tmp/apex_wallet -trusted_cert -cert intermediate_CA.cer -pwd ThisIs2018


                    And then the following:



                    exec utl_http.set_wallet('file:/tmp/apex_wallet', 'ThisIs2018');
                    select utl_http.request(url => 'https://login.twinfield.com/') from dual;


                    And I didn't receive any error. Maybe I've overlooked something.


                    Do let me know if it helps.


                    Kind regards,

                    • 7. Re: utl_http.request on ssl site fails with ORA-29024
                      Carsten Czarski-Oracle

                      Hi Everybody,


                      I just tried that out on my local 12.2 instance (with a wallet in place) - and the following command does work:


                      select apex_web_service.make_rest_request('https://login.twinfield.com/auth/authentication/login', 'GET', p_https_host=> 'incapsula.com') from dual;


                      On 12.1, the p_https_host parameter in APEX_WEB_SERVICE is not functional, so there you can just run


                      select apex_web_service.make_rest_request('https://login.twinfield.com/auth/authentication/login', 'GET') from dual;


                      On database 18.x  you also should not need to set the p_https_host parameter any more - the database should sort this out automatically.


                      I think, when you still see the ORA-29024, something is still wrong with your wallet ...


                      -) Actually, the root certificate should be sufficient; IMO you don't need to add the intermediate certificates

                      -) You also don't need a password for that kind of wallet (it's not used for sensitive stuff like authentication). So you might just create it as an auto-login wallet (orapki has a switch for that -auto_login)


                      I would recommend to review the wallet (or to re-create it)..


                      I hope this helps


                      Best regards



                      1 person found this helpful
                      • 8. Re: utl_http.request on ssl site fails with ORA-29024

                        Hi Carsten,

                        thank you very much for investigating this issue but obviously when the two (or maybe three) of us are doing the same, it's not the same.

                        On my 12.2 instance the command

                        select apex_web_service.make_rest_request('https://login.twinfield.com/auth/authentication/login', 'GET', p_https_host=> 'incapsula.com') from dual;

                        still raises a certificate validation failure, so please, could you share a snapshot of your wallet? Mine looks like this.

                        Thank you very much




                        • 9. Re: utl_http.request on ssl site fails with ORA-29024
                          Carsten Czarski-Oracle

                          Hi Pavel,


                          the wallet needs an additional root certificate - add this one:



                          The reason is that "login.twinfield.com" sends two SSL certificates. One is signed by Entrust ( as we see in the browser ) - the other one by GlobalSign (you see that when investigating the URL with SSLLabs.com). Since Oracle 12.2 does not select the correct one out of the box, it needs both root certificates in the wallet. I believe (but have not tested), on 18.3 the "Entrust G2" would be sufficient.


                          So with these two wallets it should work on 12.2:


                          Oracle PKI Tool : Version

                          Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved.


                          Requested Certificates:

                          User Certificates:

                          Trusted Certificates:

                          Subject:        CN=Entrust Root Certification Authority - G2,OU=(c) 2009 Entrust\, Inc. - for authorized use only,OU=See www.entrust.net/legal-terms,O=Entrust\, Inc.,C=US

                          Subject:        CN=GlobalSign Root CA,OU=Root CA,O=GlobalSign nv-sa,C=BE



                          I hope this helps



                          1 person found this helpful
                          • 10. Re: utl_http.request on ssl site fails with ORA-29024

                            HI Carsten,

                            thanks a lot for further explanation (and btw, thank you very much for implementing apex_web_service.g_reason_phrase Re: APEX_WEB_SERVICE expose reason_phrase . ).

                            This entire thing with wallets is extremely tricky (and annoying, I must say) and I'm yet to find a way how to always reliably setup a wallet. The best article (by far) about all of this stuff is yours, but it looks like it did not work for this particular case. I further investigated and this command

                            [oracle@localhost ~]$ openssl s_client -showcerts -connect login.twinfield.com:443

                            produces the following output:

                            depth=2 C = BE, O = GlobalSign nv-sa, OU = Root CA, CN = GlobalSign Root CA
                            verify return:1
                            depth=1 C = BE, O = GlobalSign nv-sa, CN = GlobalSign CloudSSL CA - SHA256 - G3
                            verify return:1
                            depth=0 C = US, ST = Delaware, L = Dover, O = Incapsula Inc, CN = incapsula.com
                            verify return:1
                            Certificate chain
                            0 s:/C=US/ST=Delaware/L=Dover/O=Incapsula Inc/CN=incapsula.com
                              i:/C=BE/O=GlobalSign nv-sa/CN=GlobalSign CloudSSL CA - SHA256 - G3
                            -----BEGIN CERTIFICATE-----
                            -----END CERTIFICATE-----
                            1 s:/C=BE/O=GlobalSign nv-sa/CN=GlobalSign CloudSSL CA - SHA256 - G3
                              i:/C=BE/O=GlobalSign nv-sa/OU=Root CA/CN=GlobalSign Root CA
                            -----BEGIN CERTIFICATE-----
                            -----END CERTIFICATE-----
                            Server certificate
                            subject=/C=US/ST=Delaware/L=Dover/O=Incapsula Inc/CN=incapsula.com
                            issuer=/C=BE/O=GlobalSign nv-sa/CN=GlobalSign CloudSSL CA - SHA256 - G3
                            No client certificate CA names sent
                            Peer signing digest: SHA512
                            Server Temp Key: ECDH, P-256, 256 bits
                            SSL handshake has read 3420 bytes and written 415 bytes
                            New, TLSv1/SSLv3, Cipher is ECDHE-RSA-AES128-GCM-SHA256
                            Server public key is 2048 bit
                            Secure Renegotiation IS supported
                            Compression: NONE
                            Expansion: NONE
                            No ALPN negotiated
                                Protocol  : TLSv1.2
                                Cipher    : ECDHE-RSA-AES128-GCM-SHA256
                                Session-ID: E3F9209750FA413D3480176EFA231E02045E884AE70910F3B30D382CF5785218
                                Master-Key: D4C1E71CD119D70235DADA3F94A21F8D4730E327D89E7E07A9B9F94F7FDFDF90B9F638A7E3DA67995B93BF7CAB287DC3
                                Key-Arg  : None
                                Krb5 Principal: None
                                PSK identity: None
                                PSK identity hint: None
                                TLS session ticket lifetime hint: 300 (seconds)
                                TLS session ticket:
                                0000 - d4 9e 34 c5 93 08 17 ac-2d 41 53 c6 a6 a7 39 ee  ..4.....-AS...9.
                                0010 - 7a 88 78 4f 8f cf 90 a6-0e 0b 3a 29 57 e7 c3 20  z.xO......:)W..
                                0020 - b9 b9 70 34 54 13 5e dc-e9 db 60 bc bf ec a2 33  ..p4T.^...`....3
                                0030 - de 85 c7 09 66 1b 20 dd-b6 ce 7f df 4e 25 c7 e5  ....f. .....N%..
                                0040 - ad 5f 1b fe 39 82 a9 e8-f9 8e dd fe 06 6b b2 e5  ._..9........k..
                                0050 - a1 5f 2a ce c3 f7 85 fd-82 df 35 57 86 19 27 79  ._*.......5W..'y
                                0060 - df 81 54 cf c6 02 a0 1a-67 27 37 ff 4d af 7d b9  ..T.....g'7.M.}.
                                0070 - be 46 03 d6 33 b0 05 ed-c0 23 cc ed 66 3b ad 4b  .F..3....#..f;.K
                                0080 - f1 00 15 c7 09 7c 37 ae-34 2e b1 db 09 f6 48 1e  .....|7.4.....H.
                                0090 - 0e f0 43 0b 9c 55 ed 66-43 d6 85 e5 24 d2 52 b7  ..C..U.fC...$.R.
                                Start Time: 1539613209
                                Timeout  : 300 (sec)
                                Verify return code: 0 (ok)

                            I copy&pasted the second one into the OWM and it started to work, no Entrust Root Certification Authority needed at all which, I'm afraid, does not fully correspond with your article (no offense, as I said your article is by far the most informative of all the sources I found so far and I was digging a lot), just to support my previous statement that the entire thing with wallets is tricky and some official Oracle guide would be highly appreciated. Then I deleted it, imported the first one - it did not work, then both and after some toying with adding and removing certificates I'm getting this error message:

                            ORA-29273: HTTP request failed
                            ORA-06512: at "SYS.UTL_HTTP", line 1501
                            ORA-29106: Cannot import PKCS #12 wallet.
                            ORA-06512: at "SYS.UTL_HTTP", line 380
                            ORA-06512: at "SYS.UTL_HTTP", line 1441
                            ORA-06512: at line 1
                            29273. 00000 - "HTTP request failed"
                            *Cause: The UTL_HTTP package failed to execute the HTTP request.
                            *Action: Use get_detailed_sqlerrm to check the detailed error message.
                            Fix the error and retry the HTTP request.

                            Well, this is something new and finally I recreated the entire wallet.

                            For the OP:

                            Bas, the following procedure led to the desired output:

                            1) run the command

                            openssl s_client -showcerts -connect login.twinfield.com:443

                            or you can directly copy the second certificate from my post (everything

                            -----BEGIN CERTIFICATE-----


                            -----END CERTIFICATE-----


                            2) open Oracle Wallet Manager (owm),

                            3) open your wallet,

                            4) right-click Trusted Certificates => Import Trusted Certificate => Paste the certificate and save the wallet.

                            Now this command (you need to reconnect/create new session before running it)

                            select utl_http.request(url => 'https://login.twinfield.com/auth/authentication/login', wallet_path => 'file:/u01/app/oracle/product/12.2/db_1/owm/wallets/oracle', wallet_password => 'pwd', https_host => 'incapsula.com')
                              from dual;

                            should work (the same using apex_web_service.make_rest_request as well).



                            • 11. Re: utl_http.request on ssl site fails with ORA-29024
                              Bas de Klerk



                              sorry for my late reponse but my first prio was to get this working and understanding what went wrong.

                              As you & Carsten suggested the cause was the missing globalsign CA certificate.

                              I've tested this on 12.1, 12.2 and 18.1.

                              There are smalle differences, 12.1 works right away after installing the globalsign certtificate, the same goed for 18.1.

                              In 12.2 you need to add the https_host => 'incapsula.com' parameter to get it working.


                              Thanks a lot for talking the time to explore and helping me out. I wasn't aware that a second root CA could be the cause, learned a lot from this thread and the links posted ! Thanks Carsten for explaining in detail and Pavel for keep searching for a full working solution and explanation of what happend !!!