12 Replies Latest reply on Jun 19, 2017 10:18 AM by Jeff Kemp

    Reverse proxy your https traffic

    ade_adekoya

      Hello

       

      I am trying to make a request to a webservice using apex_web_service.make_rest_request and receive error ORA-28857: Unknown SSL error.

       

      I am using a local install of OracleXE on Windows7, with wallet entry for the website certificates.

       

      I understand this is due to Oracle database 11.2.0.2 lack of support to SSL certificates signed with SHA-256 or newer. See Blog post :

      https://blog.hazrulnizam.com/openssl-workaround-oracle-xe-wallet/

       

      To fix this  I would like to use the reverse proxy my  https traffic to http method.

       

      After installing Apache2.4, I amended the http.conf with :

       

      LoadModule ssl_module modules/mod_ssl.so

       

      SSLProxyEngine on

      ProxyPass /mendeley https://api.mendeley.com

      ProxyPassReverse /mendeley https://api.mendeley.com

       

      Unfortunately this did not work.

       

      Can you please help with the exact entries in my Apache config to perform reverse proxy, to  :

             - perform reverse proxy for website https://api.mendeley.com

          - install mod_ssl (with default options) and “ProxyRequests Off”

       

      Kind Regards

      Ade

        • 1. Re: Reverse proxy your https traffic
          Jeff Kemp

          "this did not work" is not an error message.

           

          Your Proxy directives look fine. Are they defined within a VirtualHost? e.g. like this:

           

          <VirtualHost *:80>

            ServerName api.mydomain.com

            SSLProxyEngine on

            ProxyPass /mendeley https://api.mendeley.com

            ProxyPassReverse /mendeley https://api.mendeley.com

            ProxyRequests Off

          </VirtualHost>

           

          Make sure you are calling your server name, e.g. http://api.mydomain.com/mendeley

           

          Make sure your database is configured with a Network ACL which allows it to call out to your server, on port 80.

           

          For more help, post the actual error you're getting, and full virtualhost config.

           

          • 2. Re: Reverse proxy your https traffic
            ade_adekoya

            Hello Jeff

             

            Thanks for your response.

             

            I edited my C:\Apache24\conf\httpd.conf

             

            and at the end of the file added entry :

             

            <VirtualHost *:80>

              ServerName api.mydomain.com

              SSLProxyEngine on

              ProxyPass /mendeley https://api.mendeley.com

              ProxyPassReverse /mendeley https://api.mendeley.com

              ProxyRequests Off

            </VirtualHost>

             

            I restarted the Apache server and received error :

             

            "Windows could not start the Apache on Local Computer. For more information, review the system event log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 1"

             

            With my local install of OracleXE on Windows7 in my cmd window I issue a ping localhost ==> NUK20002443.xxx.xxxxxxxx.net  (replaced with xxx for security)

             

            I tried changing the entry ServerName api.mydomain.com to

             

                     api.NUK20002443.xxx.xxxxxxxx.net or NUK20002443.xxx.xxxxxxxx.net or NUK20002443.xxx.xxxxxxxx.net:80

             

            Still the same error.

             

            I suspect my mydomain entry is incorrect, what entry should I use?

             

            Also I call apex_web_service.make_rest_request with p_url  => 'https://api.mendeley.com/oauth/token'

             

            with the reverse proxy correctly setup I wanted to use p_url  => 'http://api.mendeley.com/oauth/token'

             

            but you stated use http://api.mydomain.com/mendeley

             

            Can you clarify?

             

            Thanks

            Ade

            • 3. Re: Reverse proxy your https traffic
              Jeff Kemp

              Sounds like you have a syntax error somewhere in your apache config. You need to review the apache event log (I don't use apache on Windows so don't know where that would be) to see what error is being detected.

               

              Apache usually has a configtest option which should tell you more info as well. In fact, it is good practice to do a configtest before starting the service.

               

              Yes, obviously if you try to refer to api.mendeley.com you would be trying to bypass your proxy. To use a proxy you need to refer to the server name that corresponds to the proxy. Note that if it's running on the same server I think you would refer to http://localhost/mendeley instead? But I might be wrong here.

              • 4. Re: Reverse proxy your https traffic
                ade_adekoya

                Hi Jeff

                 

                Moving slowly forward, I'm an Apache novice!

                 

                I removed the <virtualHost> entry from the http.conf and placed it in the http-ssl.conf

                 

                <VirtualHost *:80>

                  ServerName NUK20002443

                  SSLProxyEngine on

                  ProxyPass /mendeley https://api.mendeley.com

                  ProxyPassReverse /mendeley https://api.mendeley.com

                  ProxyRequests Off

                </VirtualHost>

                 

                I restarted the Apache without any errors!

                 

                I then modified my apex_web_service.make_rest_request with p_url  =>  'http://NUK20002443/api.mendeley.com/oauth/token'

                 

                and the plsql call executed succesfully

                 

                However I received this output :

                 

                <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">

                <html><head>

                <title>404 Not Found</title>

                </head><body>

                <h1>Not Found</h1>

                <p>The requested URL /api.mendeley.com/oauth/token was not found on this server.</p>

                </body></html>

                 

                So I need to look into this further.

                 

                I think your </VirtualHost> entry helped, but will try and figure out the the request results.

                 

                Do you have an API I can test with a known result?

                 

                Regards

                Ade

                • 5. Re: Reverse proxy your https traffic
                  Jeff Kemp

                  The clue is right there in the error you got back - you're still adding the "api.mendeley.com/" bit in your request - you don't do this because it's your proxy that will pass the request on to that URL.

                   

                  In other words, this is probably what you should be doing:

                   

                  p_url  =>  'http://NUK20002443/mendeley/oauth/token'

                  • 6. Re: Reverse proxy your https traffic
                    ade_adekoya

                    Hi Jeff

                     

                    I manually create my Token and test the api in curl with a JSON result

                     

                    curl --request GET --header "Authorization: Bearer MSwxNDk3NTQyNDEyMjUyLDQ5MzkyNDY0MSwxMDI4LGFsbCwsLGQ1ODZkZDIyYTBjYWM1MTgzNTg1NDA2YzZkZmJiNjI4ZDI4OGM3YSxhZjFjZjJhYi0zMTY2LTNmYTQtYmI5Mi1mYmE4OGExNjVhMTAscTdTUFJ1OWpiOEkySk11YXNPdmRBb1RLVmZj" "https://api.mendeley.com/search/catalog?title=oracle&limit=5"

                     

                    I then try it using plsql

                     

                    declare

                       L_token                 varchar2(4000):= 'MSwxNDk3NTQyNDEyMjUyLDQ5MzkyNDY0MSwxMDI4LGFsbCwsLGQ1ODZkZDIyYTBjYWM1MTgzNTg1NDA2YzZkZmJiNjI4ZDI4OGM3YSxhZjFjZjJhYi0zMTY2LTNmYTQtYmI5Mi1mYmE4OGExNjVhMTAscTdTUFJ1OWpiOEkySk11YXNPdmRBb1RLVmZj';

                       L_mendeley_document   VARCHAR2(32767);

                    begin

                       utl_http.set_body_charset('UTF-8');

                       -- build the Authorisation header

                       apex_web_service.g_request_headers(1).name  := 'Content-Type';

                       apex_web_service.g_request_headers(1).value := 'application/jsonrequest';

                       apex_web_service.g_request_headers(1).name  := 'Authorization';

                       apex_web_service.g_request_headers(1).value := 'Bearer '||L_token||'';

                      

                       L_mendeley_document := apex_web_service.make_rest_request

                           (

                             p_url         => 'http://NUK20002443/mendeley/search/catalog'

                           , p_http_method => 'GET'

                           , p_parm_name   => apex_util.string_to_table('title:limit')

                           , p_parm_value  => apex_util.string_to_table('oracle:5')

                           );

                          

                    dbms_output.put_line('This is L_mendeley_document'|| L_mendeley_document);

                     

                     

                    EXCEPTION

                       WHEN OTHERS THEN

                          raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);

                    end;

                     

                    But still get my Not found result :

                     

                    This is L_mendeley_document<!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">

                    <html><head>

                    <title>404 Not Found</title>

                    </head><body>

                    <h1>Not Found</h1>

                    <p>The requested URL /mendeley/search/catalog was not found on this server.</p>

                    </body></html>

                     

                    Anything else I can try to test this connection?

                     

                    Regards

                    Ade

                    • 7. Re: Reverse proxy your https traffic
                      Jeff Kemp

                      Your curl test is the right approach but you're still using the wrong URL - you have to test with the same URL that your PL/SQL program will use - i.e. http://NUK20002443/mendeley/search/catalog

                       

                      I suspect that the error is probably being raised by your own proxy server, thus a problem with the virtualhost config.

                      • 8. Re: Reverse proxy your https traffic
                        ade_adekoya

                        Hi Jeff

                         

                        I get the same error with the curl command. It really boils down to understanding the Windows Apache virtualhost config, I will investigate further.

                         

                        Thanks for your help so far.

                         

                        Can any body on the Oracle APEX Team help with this?

                         

                        Regards

                        Ade

                        • 9. Re: Reverse proxy your https traffic
                          Pavel_p

                          Hi Ade,

                          it looks like Jeff already did his best and it's quite unlikely that anyone from the APEX team could give you better guidelines how to configure your Apache reverse proxy.

                          This blogpost Apex the Smart way: making https (webservice) requests from PL/SQL without a wallet might be helpful.

                          Regards,

                          Pavel

                          • 10. Re: Reverse proxy your https traffic
                            ade_adekoya

                            Hi Jeff

                             

                            Checked my httpd.conf settings and realised my "mod_proxy.so" and "mod_proxy_http.so" and "mod_ssl.so" Apache modules were not loaded by default install, so after un-commenting them everything worked as expected.

                             

                            Thanks for your assistance the virtual Host entry in my httpd.conf file was correct.

                             

                            Kind Regards

                             

                            Ade

                            • 11. Re: Reverse proxy your https traffic
                              ade_adekoya

                              Hi Pavel

                               

                              Saw the Blog post and was going to be plan B, but managed to get things working!

                               

                              Thanks

                              Ade

                              • 12. Re: Reverse proxy your https traffic
                                Jeff Kemp

                                Great to hear.