Forum Stats

  • 3,733,857 Users
  • 2,246,830 Discussions
  • 7,856,898 Comments

Discussions

error ORA-24263 with social login microsoft

jmarc
jmarc Member Posts: 397 Bronze Badge
edited June 2018 in APEX Discussions

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

Pavel_pJustin Warwick3682296

Answers

  • Pavel_p
    Pavel_p Member Posts: 2,303 Gold Trophy
    edited May 2018

    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

  • jmarc
    jmarc Member Posts: 397 Bronze Badge
    edited May 2018

    Hello,

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

    Regards

    Jm

  • Pavel_p
    Pavel_p Member Posts: 2,303 Gold Trophy
    edited May 2018

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

    jmarc
  • Pavel_p
    Pavel_p Member Posts: 2,303 Gold Trophy
    edited May 2018

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

    select * from v$versionOracle 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.

    jmarc
  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,139 Employee
    edited May 2018

    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

    Pavel_pjmarcJustin Warwick
  • Pavel_p
    Pavel_p Member Posts: 2,303 Gold Trophy
    edited May 2018

    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

    Justin Warwick
  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,139 Employee
    edited May 2018

    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

    Pavel_p
  • Pavel_p
    Pavel_p Member Posts: 2,303 Gold Trophy
    edited May 2018

    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

    Justin Warwick
  • jmarc
    jmarc Member Posts: 397 Bronze Badge
    edited June 2018

    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

  • Christian Neumueller-Oracle
    Christian Neumueller-Oracle Member Posts: 844 Employee
    edited June 2018

    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

  • jmarc
    jmarc Member Posts: 397 Bronze Badge
    edited June 2018

    hello,

    I finally succeeded with an apache reverse proxy following

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

    regards

    jm

    3682296
This discussion has been closed.