If you want to call a web service over HTTPS from the
utl_http or
apex_web_service packages in PL/SQL, you need to set up an Oracle Wallet that contains the SSL certificates of the server you are connecting to from the database.
Setting up an Oracle Wallet is quite straightforward, but it can be a bit of a hassle to configure a large number of certificates. Also, if you are using Oracle Express Edition (XE) which gets very infrequent updates, you are stuck with whatever SSL protocol support was in the database at the time of release.
One solution is to set up an outgoing proxy in a local web server. The database will communicate with the web server via plain HTTP, without any need for SSL certificates, and the web server translates the requests to HTTPS. This is fine from a security point of view as long as the database and the web server are on the same machine or on the same internal network. Traffic from the web server to the outside world is encrypted using SSL, while the internal traffic between the web server and the database remains unencrypted.
Richard Martens wrote a blog post about this and showed how to set up the proxy on Apache. In this blog post I will show you the equivalent setup on Microsoft Internet Information Server (IIS).
Step 1: Installation
First you need to download and install the following IIS extensions from Microsoft:
Install these extensions as per Microsoft's documentation.
Step 2: Set up proxy website
Create a new website in IIS. The name of the website is not important, but in this example I will call it "MyProxy". You need to link the website to a physical location on disk, I use
C:\temp\myproxy but again this does not really matter.
Then edit the website bindings to bind the site to a specific IP address and port. In this example, I choose 8888 as the port number. I also bind the site to the localhost IP address 127.0.0.1. This ensures that the site can only accept connections coming from the local machine, and therefore assumes that my Oracle database is located on the same machine as the web server.
Step 3: Configure URL rewrite from HTTP to HTTPS
Follow the instructions in
this blog post to enable Application Request Routing at the server level (short version: Click "Application Request Routing Cache" at the server level, choose "Server Proxy Settings" and then check the "Enable Proxy" checkbox).
Then go to the MyProxy website that you created in step 2, and open the "URL Rewrite" feature. Create a new rule called something like "Forward all requests as HTTPS".
Set up a rewrite rule as follows: Pattern
myproxy/(.*?)/(.*) rewrites to
https://{R:1}/{R:2}
This rule means that, for example, a request to
http://127.0.0.1:8888/myproxy/example.com/foo/bar would be rewritten to
https://example.com/foo/bar
Step 4: Test the proxy setup
You can now try out the proxy rewrite rule from a Powershell command prompt on the server. In the screenshot below you can see two regular requests (the direct HTTP request doesn't work as the remote server does not accept non-HTTPS requests), as well as a request to the local proxy which gets a successful response back from the remote server.
With that in place, we can now turn to the database.
Step 5: Adjust database network ACL
To be able to access the local proxy, we need to open this up using the database network ACL. A typical script to do this would look like this:
-- to be run by user SYS
alter session set nls_language = AMERICAN;
alter session set nls_territory = AMERICA;
BEGIN
DBMS_NETWORK_ACL_ADMIN.create_acl (
acl => 'apex.xml',
description => 'Access Control List for APEX',
principal => 'APEX_050000',
is_grant => TRUE,
privilege => 'connect',
start_date => SYSTIMESTAMP,
end_date => NULL);
COMMIT;
END;
/
-- for https proxy
-- see http://blog.rhjmartens.nl/2015/07/making-https-webservice-requests-from.html
-- see http://ora-00001.blogspot.com/2016/04/how-to-set-up-iis-as-ssl-proxy-for-utl-http-in-oracle-xe.html
BEGIN
DBMS_NETWORK_ACL_ADMIN.assign_acl (
acl => 'apex.xml',
host => '127.0.0.1',
lower_port => 8888,
upper_port => 8888);
COMMIT;
END;
/
Since we can now use this proxy for any HTTPS traffic, we only need this single entry in the network ACL. (If you want to narrow down the list of allowed remote sites, you could adjust the rewrite rule in step 3 so that it only works for specific sites instead of all sites.)
Step 6: Enjoy
You can now use the
utl_http and
apex_web_service packages from PL/SQL to call any HTTPS site. Just remember that you need to alter the original URL so it hits the proxy instead.
