11 Replies Latest reply on Jun 5, 2018 2:59 PM by jmarc

    error ORA-24263 with social login microsoft

    jmarc

      hello,

      i use APEX 18.1 on premise

      i follow https://ora-00001.blogspot.fr/2018/02/apex-authentication-with-microsoft-account.html for SSO microsoft

      and https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/6121/index-en.html

       

      i use the certificat at https://login.microsoftonline.com/common/v2.0/.well-known/openid-configuration

       

      orapki wallet add -wallet https_wallet  -cert /u01/userhome/oracle/BaltimoreCyberTrustRoot.crt  -trusted_cert  -pwd ********

       

      when i try the social login Microsoft Authentification

       

       

      i have the following error

      Exception in "final_exception_handler":

      Error Stack: ORA-29273: HTTP request failed

      ORA-06512: at "APEX_180100.WWV_FLOW_WEB_SERVICES", line 1011

      ORA-24263: Certificate of the remote server does not match the target address.

      ORA-06512: at "SYS.UTL_HTTP", line 380

      ORA-06512: at "SYS.UTL_HTTP", line 1127

      ORA-06512: at "APEX_180100.WWV_FLOW_WEB_SERVICES", line 911

      ORA-06512: at "APEX_180100.WWV_FLOW_WEB_SERVICES", line 1517

      ORA-06512: at "APEX_180100.WWV_FLOW_WEBSERVICES_API", line 369

      ORA-06512: at "APEX_180100.WWV_FLOW_AUTHENTICATION_NATIVE", line 451

      ORA-06512: at "APEX_180100.WWV_FLOW_AUTHENTICATION_NATIVE", line 501

      ORA-06512: at "APEX_180100.WWV_FLOW_AUTHENTICATION_NATIVE", line 613

      ORA-06512: at "APEX_180100.WWV_FLOW_AUTHENTICATION_NATIVE", line 1674

      ORA-06512: at "APEX_180100.WWV_FLOW_PLUGIN", line 2706

      ORA-06512: at "APEX_180100.WWV_FLOW_AUTHENTICATION", line 1954

      Backtrace: ORA-06512: at "APEX_180100.WWV_FLOW_WEB_SERVICES", line 1011

      ORA-06512: at "SYS.UTL_HTTP", line 380

      ORA-06512: at "SYS.UTL_HTTP", line 1127

      ORA-06512: at "APEX_180100.WWV_FLOW_WEB_SERVICES", line 911

      ORA-06512: at "APEX_180100.WWV_FLOW_WEB_SERVICES", line 1517

      ORA-06512: at "APEX_180100.WWV_FLOW_WEBSERVICES_API", line 369

      ORA-06512: at "APEX_180100.WWV_FLOW_AUTHENTICATION_NATIVE", line 451

      ORA-06512: at "APEX_180100.WWV_FLOW_AUTHENTICATION_NATIVE", line 501

      ORA-06512: at "APEX_180100.WWV_FLOW_AUTHENTICATION_NATIVE", line 613

      ORA-06512: at "APEX_180100.WWV_FLOW_AUTHENTICATION_NATIVE", line 1674

      ORA-06512: at "APEX_180100.WWV_FLOW_PLUGIN", line 2706

      ORA-06512: at "APEX_180100.WWV_FLOW_AUTHENTICATION", line 1954

      ORA-06512: at "APEX_180100.WWV_FLOW", line 3983

       

      i have no issue with google social login authentification

       

       

      regards

      jm

        • 1. Re: error ORA-24263 with social login microsoft
          Pavel_p

          Hi,

          please, what is your full DB version? On some older versions like XE it is not possible as it needs some patch for SHA-2 based certificates. If you're on 12.1/2, just follow this excellent Carsten's article https://blogs.oracle.com/apex/apex-https-certificates-and-the-oracle-wallet .

          Regards,

          Pavel

          • 2. Re: error ORA-24263 with social login microsoft
            jmarc

            Hello,

            I use Oracle database 12.2 and thé Link you provided i have already notify on my description issue de.

            Regards

            Jm

            • 3. Re: error ORA-24263 with social login microsoft
              Pavel_p

              Sorry for my previous post, I was absolutely sure that with Carsten's blogpost it's a piece of cake and you must have missed it (always worked for me...till now).

              Well, piece of cake...supposedly. I'm getting the very same error like you (which is actually not that surprising as we both followed the same procedure and using the same DB).

              If I run this code on my 12c DB (developer VM)

              declare
                l_resp clob;
              begin
                l_resp := apex_web_service.make_rest_request(p_url => 'https://login.microsoftonline.com/common/v2.0/.well-known/openid-configuration',
                p_http_method => 'GET',
                p_wallet_path => 'file://u01/app/oracle/product/12.2/db_1/owm/wallets/oracle',
                p_wallet_pwd => 'wallet_pwd',
                p_https_host=> 'stamp2.login.microsoftonline.com'--no matter if I specify this parameter or not
                );
              
                dbms_output.put_line(l_resp);
              end;
              

              and getting this

              ORA-29273: HTTP request failed
              ORA-06512: at "APEX_180100.WWV_FLOW_WEB_SERVICES", line 1011
              ORA-24263: Certificate of the remote server does not match the target address.
              ORA-06512: at "SYS.UTL_HTTP", line 380
              ORA-06512: at "SYS.UTL_HTTP", line 1127
              ORA-06512: at "APEX_180100.WWV_FLOW_WEB_SERVICES", line 911
              ORA-06512: at "APEX_180100.WWV_FLOW_WEB_SERVICES", line 1517
              ORA-06512: at "APEX_180100.WWV_FLOW_WEBSERVICES_API", line 369
              ORA-06512: at line 4
              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.
              

              however if I run the same code on apex.oracle.com (just without specifying the wallet path+pwd as there it is configured on APEX instance level)

              declare
                l_resp clob;
              begin
                l_resp := apex_web_service.make_rest_request(p_url => 'https://login.microsoftonline.com/common/v2.0/.well-known/openid-configuration',
                p_http_method => 'GET'
                );
                dbms_output.put_line(l_resp);
              end;
              

              the response is exactly as expected. So there are obviously still some secrets about certificates that have not been revealed yet.

              We can try to ask Carsten Czarski-Oracle what we're missing here and how exactly must be exported/imported this certificate into Oracle wallet.

              1 person found this helpful
              • 4. Re: error ORA-24263 with social login microsoft
                Pavel_p

                And one more thing... If it works on apex.oracle.com might be also caused by the different DB version

                select * from v$version
                Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
                

                which does not necessarily mean that it works on 12.2 as well, as in 12.2 was introduced the new https_host parameter https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/UTL_HTTP.html#GUID-BBD953E8-CA2B-4D2F-B4E8-125A0C2… that corresponds with p_https_host in apex_web_service.make_rest_request, so the behavior in 12.2 is different. Obviously you're not the only one who has this issue as here is being described exactly the same problem https://asktom.oracle.com/pls/asktom/f?p=100:11:::NO:RP:P11_QUESTION_ID:9536564700346663150  (unfortunately unanswered) with the link to this thread https://asktom.oracle.com/pls/asktom/asktom.search?tag=ora-24263-certificate-of-the-remote-server-does-not-match-the-tar… (no clear solution there as well).

                I've spent enough time messing with Oracle wallet and certificates and such things to come to conclusion that the best thing we can do is to completely avoid https calls altogether and setup a reverse proxy like in this example Apex the Smart way: making https (webservice) requests from PL/SQL without a wallet .

                It would be really great if someone from APEX development team could provide more details how to deal with such certificates (if it even works in 12.2).

                Thanks a lot in advance Carsten Czarski-Oracle, Christian Neumueller-Oracle.

                1 person found this helpful
                • 5. Re: error ORA-24263 with social login microsoft
                  Carsten Czarski-Oracle

                  Hi everybody,

                   

                  here is some background information on this:

                   

                  • With 12.2, Server Name Indication (SNI) was introduced to the URL HTTP Package. This supports cases where the server name of SSL certificate does not match the requested host name.
                  • So when we request "https://foo.com", but the server sends a certificate for "bar.com", we can use the p_https_host parameter in APEX_WEB_SERVICE to indicate that "bar.com" is the correct server name and that the certificate can be accepted.
                  • The new REST Consumption feature supports this - when creating a web source module, you will see the "HTTPS Host" Parameter when on 12.2 or higher.
                  • In 12.1, this feature did not exist
                  • 12.2 contains a bug which leads to UTL_HTTP being not very smart in matching the given server name to the certificates being sent by the server. For example, we request "https://foo.com" and the server sends certificates for both "foo.com" and "bar.com". UTL_HTTP compares only with bar.com and the request fails. Setting p_https_host to "bar.com" lets the request succeed. These bugs are AFAIK fixed in database 18.1.

                   

                  The following APEX_WEB_SERVICE call works for me on a 12.2 database:

                  select apex_web_service.make_rest_request(
                      p_url         => 'https://login.microsoftonline.com/common/v2.0/.well-known/openid-configuration',
                      p_http_method => 'GET',
                      p_https_host  => 'graph.windows.net' ) from dual;
                  

                   

                  We need to check with Christian Neumueller-Oracle how this can be used for the Social Login feature.

                   

                  I hope this helps

                   

                  Best regards

                   

                  -Carsten

                  1 person found this helpful
                  • 6. Re: error ORA-24263 with social login microsoft
                    Pavel_p

                    Hello Carsten,

                    thank you very much for your explanation, I was able to successfully invoke the service. As I expected, the key was really the p_https_host parameter, however I have absolutely no idea how you came up with graph.windows.net as according to the documentation

                    p_https_host - The host name to be matched against the common name (CN) of the remote server's certificate for an HTTPS request.

                    which is in this case stamp2.login.microsoftonline.com and without your help I would have never ever found that it expects graph.windows.net. Please, could you explain how you found out that graph.windows.net is the right https host? For the life of me I cannot find it anywhere and I have no idea where it came from (edit: it's in a response "cloud_graph_host_name": "graph.windows.net" but I think in general it may or may not be there).

                    Anyway, such invaluable information definitely does not deserve to be hidden in this forum and maybe you could also update your (highly educational) article accordingly.

                    Best regards,

                    Pavel

                    • 7. Re: error ORA-24263 with social login microsoft
                      Carsten Czarski-Oracle

                      Hi Pavel,

                       

                      you're right. I've forgot to mention that I used http://ssllabs.com in order to get a list of the SSL certificates sent by the server. You can then easily review the Common Name and figure out the setting for p_https_host.

                       

                      Best regards

                       

                      -Carsten

                      • 8. Re: error ORA-24263 with social login microsoft
                        Pavel_p

                        Hi Carsten,

                        thank you very much again for making things clear. Unfortunately I was not able to find it on ssllabs.com, however you pointed me in the right direction and this command (openssl is available for all the main platforms) does the trick as well.

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

                         

                        ---

                        Certificate chain

                        0 s:/CN=graph.windows.net

                          i:/C=US/ST=Washington/L=Redmond/O=Microsoft Corporation/OU=Microsoft IT/CN=Microsoft IT TLS CA 2

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

                        Have a nice day,

                        Pavel

                        • 9. Re: error ORA-24263 with social login microsoft
                          jmarc

                          hello,

                          i confirm that the oracle database 12.1 is not affected, only 12.2

                          Configure an apache reverse proxy don't seem to be easy, apache reverse proxy have some issue to deal with external web site.

                          I did not manage to do it with microsoft login openid connect + Apache reverse proxy

                           

                           

                           

                          regards

                          jm

                          • 10. Re: error ORA-24263 with social login microsoft
                            Christian Neumueller-Oracle

                            Hi,

                             

                            RDBMS 18.1 adds SNI support to UTL_HTTP, so the https_host parameter will become unnecessary in many cases. There are also backports available for RDBMS 12.2. The necessary one-offs are 27551077 and 27126796, you can find them on MOS. I expect that they will also be bundled with the July RU.

                             

                            Regards,

                            Christian

                            • 11. Re: error ORA-24263 with social login microsoft
                              jmarc

                              hello,

                              I finally succeeded with an apache reverse proxy following

                              https://fuzziebrain.com/content/id/1711/

                               

                              regards

                              jm