Thoth Gateway version 1.4.3 now available

I've updated the Thoth Gateway (a PL/SQL web gateway similar to mod_plsql and ORDS, but written in .NET that allows you to run APEX and PL/SQL Web Toolkit applications using Microsoft IIS web server).

The latest release is version 1.4.3. It fixes a few minor issues, but more importantly it is compiled against the latest ODP.NET Managed Driver.

Among other things, this version of the Managed Driver supports Oracle Advanced Security Option (ASO) encryption; without this certain operations such as APEX file uploads would cause "OracleInternal.Network.NetworkException: ORA-12537: Network Session: End of file" errors against an Oracle 18c database.

When upgrading to Thoth Gateway v1.4.3 or later, please make sure you also copy the Oracle.ManagedDataAccess.dll file in addition to the PLSQLGatewayModule.dll file to make sure you have the correct library.

Enjoy!

Thoth Gateway moved to GitHub, now supports Apex 5.0

As with the Alexandria PL/SQL Utility Library, the source code for the Thoth Gateway, a mod_plsql and Apex Listener/ORDS equivalent for Microsoft IIS, now has a new home on GitHub. This should make it easy for anyone to look at and perhaps contribute to the code.

If you don't care about the source code, you can head straight to the releases page to download just the binaries. Updated installation instructions for IIS 8 will be forthcoming, but deploying the Thoth Gateway is really as easy as just dropping the binaries into the wwwroot and using IIS Manager to turn the folder into an application.

Also, there were a few minor tweaks needed to support Apex 5.0. As far as I can tell from my own testing, everything now works, but do let me know if you have any issues.

Download the lastest version of the Thoth Gateway with Apex 5.0 support here.

Thoth Gateway 1.4.0 beta release 1 available

As mentioned in my previous post about the Thoth Gateway, a mod_plsql and Apex Listener alternative that runs on IIS, the goal for the next release of the gateway (tentatively called version 1.4.0) is to change the Oracle Data Provider for .NET (ODP.NET) from the "Unmanaged" to the "Managed" driver. By using the Managed (pure .NET) driver, the installation of the Thoth Gateway becomes even simpler, because it removes the need to have the full Oracle client (or even the Instant Client) on the system. Note that this will change the minimum system requirements from .NET Framework 3.5 to 4.0 (the version that the managed driver requires).

There's now a beta version of Thoth Gateway 1.4.0 available for download here. I've tested it myself successfully on Windows Server 2012 with IIS 8.5 and .NET Framework 4.5. I would appreciate feedback from those who want to download and test it out on the same, or other, configurations.

Please note the following:

• The gateway no longer requires an Oracle client installed on the same machine (the web server). Similar to the way the "Thin JDBC" driver works, the "Managed ODP.NET" driver is just a single file (Oracle.ManagedDataAccess.dll) that is placed in the same "bin" folder as the gateway.
• The supplied web.config file includes a configSections/SectionGroup with the .NET 4.0 sections. If upgrading from an earlier framework version running the gateway, you probably need to update the config file.
• The "requestValidationMode" attribute of the "httpRuntime" node should be set to "2.0" to avoid problems when Apex passes HTML markup in the querystring (for success messages and the like).

 Please leave a comment here, or in the Google Code issue log, if you had any problems or indeed if you were able to install and run it successfully.

Oracle XE 11g Win64 on Microsoft Azure cloud

(Or: "How to build your own Oracle Cloud in 25 minutes"... ! )

After downloading the recently released Oracle Express Edition for 64-bit Windows, I decided to test it out on a Windows Server 2008 R2 running in the Azure cloud (recently renamed from "Windows Azure" to "Microsoft Azure", I guess because you can run Linux on it, as well).

Here are the high-level steps needed to get this up and running:

1. Sign up for Azure and login to the management portal.
2. Create a new Virtual Machine, select Windows Server 2008 R2. The provisioning of the VM takes around 5 minutes. (I chose to use an A1 instance type, which costs around USD 50 per month, and has 1 core and 1,75GB of memory. Note that XE is limited to use only 1GB of memory, leaving some extra memory for the OS itself and for IIS).
3. When you create the VM, you specify a DNS name for it, for example "myxetest". This becomes the subdomain of Microsoft's "cloudapp.net" domain, for example "myxetest.cloudapp.net". If you want, you can use your own domain name by adding a CNAME record to your domain and pointing it towards the cloudapp.net subdomain.
4. Use Remote Desktop to login to the new server.
5. Download Oracle XE for 64-bit Windows. The file is around 300MB, so this takes less than a minute to download.
6. Unzip the XE file and run setup.exe. Specify a password for the SYS and SYSTEM users, and let the installer do its work. The whole setup process took about 10-15 minutes on my server.
7. Check that the database is running using sqlplus on the command line on the server.
8. Oracle XE 11g comes with Apex 4.0 preinstalled, and running via the Embedded PL/SQL Gateway (DBMS_EPG) on port 8080 (localhost only). You can verify that it's up and running by going to http://127.0.0.1:8080/apex/apex on the server. If you want EPG to work for remote hosts, you have to execute the dbms_xdb.setlistenerlocalaccess procedure and pass FALSE to enable non-local access. (You can also change the port used by EPG via the dbms_xdb.sethttpport procedure.)
9. You need to modify the Windows Firewall settings on the server to allow outbound traffic on port 8080. Finally, you also need to open up port 8080 for the VM in the Azure management portal (you'll find this setting under "Endpoints" for the VM.)

Here's a screenshot after the database has been installed:

The Apex homepage on localhost:

The Apex builder in action, accessed via the default cloudapp.net domain:

A note on production deployments

The Embedded PL/SQL Gateway (DBMS_EPG) is intended for development environments, and is not recommended for production. Since you are running on a Windows server, you probably want to leverage the excellent built-in Internet Information Server (IIS), with the Thoth Gateway which is an open source ASP.NET implementation of the OWA interface which is what the EPG, mod_plsql and the Apex Listener (now called Oracle REST Data Services, or ORDS) use to communicate with the database. You'll need to enable/install IIS first, as this is not enabled by default on a Windows 2008 server. Then download the Thoth Gateway and follow the installation instructions in the downloaded zip file.

Thoth Gateway version 1.3.7 available, including source code

There is a new version of the Thoth Gateway, a mod_plsql replacement for IIS, available for download. The latest version is 1.3.7.

This version also includes the source code for the gateway. The project has been "open source" (under a BSD license) since day 1, but I never got around to actually publishing the source code, until now that is! :-)

This version fixes an issue with page rendering that was only impacting Apex 4.2 installations. Thanks to Patrick and Christian on the Apex developer team who responded quickly to my inquiries about the underlying changes in Apex that caused this problem to appear (the problem was caused by improper CGI setup in IIS/Thoth, not a problem with Apex itself).

Note also that this version removes support for OWA basic authentication (see the release notes in the downloaded archive for details). This should not have any impact on Apex applications at all (but may impact older, "pure OWA" applications).

Upgrades: For existing installations, simply overwrite the existing PLSQLGatewayModule.dll file in the "bin" folder with the latest version from the downloaded archive.

Roadmap for the Thoth Gateway

The goal for the next release of the gateway (tentatively called version 1.4.0) is to change the Oracle Data Provider for .NET (ODP.NET) from the "Unmanaged" to the "Managed" driver. By using the Managed (pure .NET) driver, the installation of the Thoth Gateway becomes even simpler, because it removes the need to have the full Oracle client (or even the Instant Client) on the system. Note that this will change the minimum system requirements from .NET Framework 3.5 to 4.0 (the version that the managed driver requires).

For Thoth Gateway version 2.0, it might be fun to implement RESTful Web Services, to catch up feature-wise with the Apex Listener. (The Thoth Gateway already has automatic SOAP Web Services built-in, though.) By the way, now that the source code is available, anyone can pick up the challenge and contribute new features to the gateway!

Using Apex with IIS Express

The Thoth Gateway is a gateway written in C# and ASP.NET that allows you to run PL/SQL web applications (including Oracle Application Express) on Microsoft's Internet Information Server (IIS). You can read more about the gateway here. It is an alternative to Apache/mod_plsql and the Java-based Apex Listener.

For production deployment on a server, you should use a regular IIS installation. However, if you are a developer working on a local Apex installation on your laptop, then the full IIS package is more than you need. Microsoft also offers a lightweight alternative in IIS Express:

IS Express (...) will run on Windows XP and higher systems, does not require an administrator account, and does not require any code changes to use.

You might want to take a look at this nice video with lots of information about IIS Express.

Using the Thoth Gateway with IIS Express is in many ways similar to running the Apex Listener in Standalone mode. Here's how to use the Thoth Gateway with IIS Express on your development machine.

Download and Install IIS Express

Download the IIS Express installer from Microsoft's website.

Run the installer.

This will put the IIS Express software under C:\Program Files\IIS Express and the configuration files under C:\Documents and Settings\Administrator\My Documents\IISExpress (assuming you are running Windows XP and your username is Administrator).

Configure IIS Express

Open \My Documents\IISExpress\config\applicationhost.config and add a new site under the "sites" node:

Copy and paste the following:


                <application path="/" applicationPool="Clr2IntegratedAppPool">
                    <virtualDirectory path="/i" physicalPath="C:\temp\apex_4.1.1_en\apex\images" />
                    <virtualDirectory path="/" physicalPath="C:\temp\apex_4.1.1_en\apex\images" />
                </application>

                <application path="/web" applicationPool="Clr2IntegratedAppPool">
                    <virtualDirectory path="/" physicalPath="c:\temp\thoth_iisexpress" />
                </application>

                <bindings>
                    <binding protocol="http" bindingInformation=":8090:localhost" />
                </bindings>
            </site>

Some points to note:

• This configuration sets up the virtual directories to serve the Apex images (in the "i" folder) and to run the actual gateway application (the "web" folder, which in mod_plsql is usually called "pls", but you can call it whatever you want). Both the images and the ASP.NET application are placed under c:\temp (but you can place them wherever you want).
• The port number is set to 8090.
• The traceFailedRequestsLogging has been disabled to improve speed.

For better performance with static files, you should also add a httpExpires value to the clientCache setting:


        <!-- see http://madskristensen.net/post/Add-expires-header-for-images.aspx -->
        <clientCache httpExpires="Sun, 29 Mar 2030 00:00:00 GMT" cacheControlMode="UseExpires" />

Download and Install the Thoth Gateway

Download the Thoth Gateway and unzip the file.

Instead of following the normal installation instructions (which assume a full IIS installation), simply create a folder (I've used c:\temp\thoth_iisexpress in the example above) and copy the web.config and the bin folder from the unzipped file to this location.

Create a shortcut to start IIS Express

Now we just need a convenient way to start IIS Express whenever we want to do Apex work.

Create a batch file (I've called it start_iisexpress.bat) and put the following command in it:


"c:\program files\iis express\iisexpress.exe" /siteid:2 /systray:true

Create a shortcut to this batch file on your Windows desktop, and double-click the shortcut to start IIS Express.

As long as IIS Express is running, a console window will show each request:

That's it, you now have a lightweight, local web server for working with Oracle Application Express on a Windows machine.

Troubleshooting

If you need to troubleshoot the installation, you should enable the traceFailedRequestsLogging (see above). You'll find the trace files under \My Documents\IISExpress\TraceLogFiles. When you are done, disable this setting again for better performance.

Also note that all configuration changes to IIS Express require you to restart IIS Express before they take effect.

PL/SQL and Microsoft technology

Many companies tend to have mixed environments, with Oracle databases (and PL/SQL) running business-critical back-office systems, as well as Microsoft products for front-office applications such as email, word processing and collaboration/file sharing (ie Outlook, Office and Sharepoint), and of course the desktop operating system itself (Windows, with Internet Explorer, Internet Information Server, all integrated via Active Directory).

Working in these mixed environments means that you, as an Oracle PL/SQL developer, frequently need to work/integrate with various Microsoft technologies from PL/SQL.

Over the last couple of years, I've written a number of blog posts on this topic. This post is just a convenient collection of links to these previous posts:


PL/SQL and NTLM

• An implementation of the NTLM protocol for PL/SQL, which you can use to call web services on servers with Integrated Windows Authentication
• NTLM Single Sign-On in Oracle Application Express (Apex), how to set up your Apex applications so that users will automatically be logged in without having to enter their usernames and passwords, useful for intranet sites

Oracle Data Provider for .NET (ODP.NET)

• Minimal, non-intrusive install of ODP.NET, or how to make ODP.NET "just work" without interfering with other software on the machine, with the smallest possible footprint

Oracle Application Express (Apex) and IIS

• Using the Thoth Gateway to run Apex on Microsoft Internet Information Server (IIS), a freely available, easy-to-install, high-performance port of mod_plsql that runs on Microsoft's web server, including IIS 6 and IIS 7
• Using Oracle Proxy Authentication with the Thoth Gateway, or how to pass your Windows credentials from the the web server on to the database server

Microsoft Office and Office Open XML (OOXML)

• PL/SQL package for working with the OOXML file format, or how to read and write OOXML documents (Word, Excel, Powerpoint) from PL/SQL

Microsoft Exchange (Email, Calendar and Contacts)

• PL/SQL package for working with Microsoft Exchange, or how to read and write email, attachments, calendar events and contact information from PL/SQL

Microsoft Sharepoint

• I have not yet had the need (or the desire...) to work with Sharepoint from PL/SQL, but it would be quite easy to create a PL/SQL library package to interface with the Sharepoint web services, similar to the Exchange package listed above. Perhaps I will revisit this topic sometime in the future... ? 

Windows NTLM SSO with Apex

I see this question being asked now and then:

Is it possible to set up my Oracle Application Express (Apex) application with Single Signon (SSO) based on Windows Integrated Security (NTLM), so that the end-users are automatically logged into the Apex application with their Windows (Active Directory) account without entering their username and password?

The answer is yes, but the implementation depends on your infrastructure, and specifically your web server:

• If you are using Apache (Oracle HTTP Server), the Embedded PL/SQL Gateway (DBMS_EPG) or  the Apex Listener, then this post is not for you. There are various options such as mod_ntlm, mod_auth_sspi, proxies, etc., but you must look elsewhere for more details about those.
• If you are using (or can use) Microsoft Internet Information Server (IIS) to serve your Apex applications, then read on.

How to set up Single Signon for Oracle Apex applications on Microsoft IIS

Here are the high-level steps you need to take:

1. Install (enable) Microsoft IIS on your Windows Server
2. Download and install the Thoth Gateway, a mod_plsql replacement for IIS
3. Configure the virtual directory of the gateway application (typically the "pls" folder) to use Integrated Windows Authentication
4. Create an authentication scheme in your Apex application that checks the HTTP header called "LOGON_USER" and make that the current scheme of your application

Let's look at the two last steps in some more detail.

Configure virtual directory to use Integrated Windows Authentication

After you have installed the Thoth Gateway (see the installation instructions provided with the download), you can use the IIS management console to change the directory security settings of the virtual folder (application) where the gateway is installed.

In the following screenshot the folder is called "web-integrated-security", which just shows that you can really call it anything, but in a default installation of the Thoth Gateway the folder will be called "pls" (which mimics the naming convention used by Apache and mod_plsql).

Simply check "Integrated Windows Authentication" to enable SSO. Also remember to remove anonymous access to the website!

Create Apex authentication scheme that checks LOGON_USER

IIS will now capture the username of the current user and pass it along to the Thoth Gateway as the CGI variable called "LOGON_USER".

You can access this value from PL/SQL using

owa_util.get_cgi_env('LOGON_USER')

To use this value in an authentication scheme in Apex, you can use the code provided by Joel Kallmann in this post where he provides a "custom authentication scheme, which can be used with (...) really any environment which will securely set a header variable to an authenticated username." Joel describes five high-level steps, of which you can ignore steps 3 and 4, as this is now handled by IIS and the Thoth Gateway. Also remember to reference "LOGON_USER" rather than "REMOTE_USER".

If you are using Apex 4.1 or later, then there is no need to create a custom authentication scheme, as there is a new built-in "HTTP Header Variable" authentication scheme:

Be sure to make this authentication scheme the current scheme of your application.

Verify that it works

If you are logged into the network with a Windows (Active Directory) account and use Internet Explorer to access your website (which should be in the browser's list of Trusted Sites -- it will typically already be trusted if the website is in the Intranet zone), you should not be asked for a username and password, and your Apex username (APP_USER) should be automatically set to "DOMAIN\USERNAME".

That was easy, wasn't it ?

Thoth Gateway version 1.3.6 available

There is a new version of the Thoth Gateway, a mod_plsql replacement for IIS, available for download. The latest version is 1.3.6.

CC by Flickr user gzayatz

The previous version available for download was 1.3.0, so there are a number of enhancements and bug fixes rolled up into this release, including:


Version 1.3.6

Bug fix: Value of the CGI environment variable LOGON_USER was empty on IIS 7 running in Integrated pipeline mode: This was due to a breaking change on IIS 7. The Thoth Gateway now hooks into the PostAuthenticate event, rather than the BeginRequest event, in order for information about the current user to be available when using Windows Integrated Authentication.

Version 1.3.5

Hide server banner: A new top-level (non-DAD-specific) configuration parameter, HideServerBanner, has been added. If this is set to true (the default is false), the server will not emit the response server headers that identify the server as IIS powered by ASP.NET. This can improve security in certain situations, and reduces the response size. Requires IIS 7 with "integrated pipeline" mode.

Set SOAP date format: A new DAD-specific configuration parameter, SoapDateFormat, has been added. The default is YYYY-MM-DD"T"HH24:MI:SS. This is used to set the database session NLS date format when the gateway receives a SOAP request. You can adjust this parameter if your SOAP clients are sending dates with an UTC time indicator or offset.

Bug fix: Don't upload empty files: The gateway incorrectly uploaded files even from an empty file browse field (when the user submitted a form without having selected a file).

Bug fix: Handle responses with HTTP headers but no HTTP body: An ArgumentOutOfRangeException was thrown if the request included (just) HTTP headers without any body.

Version 1.3.4

Bug fix: Handle missing accept-encoding header: Dynamic content compression did not correctly handle clients with missing accept-encoding header.

Version 1.3.3

Serve static content from gateway folder: A new top-level (non-DAD-specific) configuration parameter, ServeStaticContent, has been added. If this is set to true (the default is false), the gateway will check if the requested URL maps to a physical file, and if so, will serve up this file instead of connecting to the database.

Compress dynamic content: A new top-level (non-DAD-specific) configuration parameter, CompressDynamicContent, has been added. If this is set to true (the default is false), and the client (browser) signals that it can accept compressed content, the gateway will compress the generated output (using GZip or Deflate, depending on the client's stated preference). This reduces network traffic at the cost of more CPU usage on the server. Note that although the default value of this is false (for backwards compatibility), I recommend you set the parameter to true, as it can give your website a significant performance boost.

Version 1.3.2

SOAP Faults: A database exception during a SOAP request now creates a SOAP Fault response that contains the database error message (see the SoapErrorStyle configuration parameter), instead of the normal 404 error (or debug error page).

Version 1.3.1

Performance optimization: The main PL/SQL procedure call issued by the gateway now also includes the call to check if the response is a file download (which used to be a separate PL/SQL call). This reduces the total number of database calls, thereby improving performance.



For existing installations, simply overwrite the existing PLSQLGatewayModule.dll file in the "bin" folder with the latest version from the downloaded archive.

Stress Testing Oracle XE 10g

Oracle Express Edition (XE) is Oracle's free entry-level database product, currently available only in a 10g version. XE is usually pitched as suitable for personal work or (very) small departmental applications, but I was curious as to what kind of load it can support.

Oracle XE 10g has the following limitations:

• Up to 1 instance per server
• Up to 1 CPU (will not use more even if available)
• Up to 1 GB RAM (will not use more even if available)
• Up to 4 GB datafiles (not including XE system data)
• Free to develop, distribute and deploy in production

By the way, several things seem to indicate that Oracle Express Edition (XE) 11g is just around the corner. It's rumored that XE 11g will raise the datafile limit from 4 GB to 11 GB, but the other limits will remain as far as I know (and I don't really know anything about it...!). So these performance tests will probably be representative both for XE 10g and 11g, although I can't know for sure until 11g is available.

Here is the test environment I set up:

Hardware

Note that the "hardware" in this case actually runs virtualized in a VMWare environment.
Here are the resources allocated to the server:

• 1 Intel Xeon X7350 2,9 GHz CPU
• 4 GB RAM (but remember that XE won't use more than 1 GB anyway)
• 30 GB disk space

Software

The software setup was as follows:

• Windows Server 2003 R2 Standard Edition Service Pack 2
• Microsoft Internet Information Server (IIS) 6.0
• Thoth Gateway
• Oracle Express Edition (XE) 10g
• Oracle Application Express (Apex) 4.0

Note that I am not using the Embedded PL/SQL Gateway (DBMS_EPG) as the web server, but rather IIS in combination with the Thoth Gateway, a free ASP.NET replacement for mod_plsql and DBMS_EPG that I wrote in C# using ODP.NET.

Everything (database, web server, Apex) was installed using default settings.
The Apex images folder (/i/) was set up with "Expires"-headers (7 days) to allow browsers to cache images.

Both the database and the web server run on the same machine (server), in order to keep the setup as simple as possible (and show what is possible with just one box).

Test page

I set up an Apex test page with a mix of regions, including a report region, a couple of PL/SQL regions and an HTML region. For each page view, a random number is generated (to simulate different users looking at different things). The report query selects up to 200 rows based on this random number (using bind variables, of course), out of a system table/view (DBA_OBJECTS) with around 17,000 total rows.

There is also an after footer page process that inserts a row in a log table, so this is not just a read-only page.

The application was set up with an authorization scheme of "No application authorization scheme required" to allow it to be accessed from the online stress test tool.

Stress test tool

LoadImpact is a user-friendly, web-based stress testing service that allows you to run free tests that simulate up to 50 concurrent clients (you can pay to test with more clients). A key point here is that the load is actually generated from their test servers on the public internet, so this gives a more realistic test than simply running a stress testing tool on your local network.

You don't even need to sign up, just enter the URL of a website to test it (apparently, there are safeguards to prevent this from being used as a denial-of-service attack tool).

A note regarding image caching: The LoadImpact service does not cache anything. This means that all the Apex images, Javascript and stylesheets will be loaded on every page hit, even though in reality they would be cached in the user's browser after the first page view. Since I wanted to test the database's ability to handle page views, rather than IIS's ability to serve up static files, I changed the Apex page so that the standard Javascript and CSS files were not included. I then manually added the core stylesheets (core Apex + theme) back on the page via the page template, just to preserve the look and layout of the page. But even these could have been left out in order to simulate a scenario where most users have the files cached in their browser.

Test results

The free test at LoadImpact runs five different subtests, starting with 10 concurrent clients and ending with 50 concurrent clients. The full test takes 10 minutes, so that is in five 2-minute parts with increasing load.

While this was going on, I watched the CPU usage in the Windows Task Manager and grabbed the following screenshots:

While there are some peaks at the start of each subtest, it seems like the database "warms up" and actually does less work at the middle and end of the subtest compared to the start. Even at 40 and 50 clients, the CPU is (on average) not working at more than half capacity.

Here is the summary from LoadImpact at the end of the test:

First of all, note the nice and almost flat curve, with response times of under 300 ms even with 50 clients (and remember that the load generator is actually located in a different country, accessing the Apex page via the public Internet).

Actually, while the test was running, I was also clicking through a separate Apex application (an actual application, not just a test page) on the same server, and I couldn't really notice that the server was under any kind of stress; the response times were excellent.

The total number of requests for the whole test was 4500, but these requests include not only the actual page (the call to the "f" procedure) but also 2 stylesheets and 3 images. So the database was hit 1 in 6 times, in other words 16,7% (of 4500) or 750 times. Each hit inserted a row into a log table. If we query that log table and group by down to the second, we can get the number of database requests per second:

This shows that the XE database was handling 10 or 11 page views per second. If this can be sustained over time, that is 36,000 page views per hour, or 864,000 page views per day.

By comparison, apex.oracle.com is reported to have 4,800,000 page views per week or 685,000 page views per day. And according to this, Reddit has 10 pageviews per second per server (with 80 servers), while Facebook has just 2 pageviews per second per server (with 30,000 servers!).

I won't read too much into these numbers (as obviously a "page view" can vary wildly between different websites, and only actual use can tell whether your site is performing well or not), but clearly the free XE database is more than a toy.

I'm even tempted to buy a Basic or Professional test at LoadImpact to test with more than 50 clients and see where that curve leads with 60, 80 or 100 concurrent clients...

Proxy Authentication with Thoth Gateway

Christian Vind submitted an enhancement request for the Thoth Gateway to support Oracle proxy authentication by passing on the current Windows username to the database connection string.

The point of proxy authentication is that

• The proxy user only has "create session" privileges but can't do much else.
• The real user does not have "create session" privileges and cannot log on to the database without knowing the proxy user name and password (and that is only set on the web/application server).
• The USER function returns the real user name, and all standard database auditing, roles, etc. work as usual.

As of version 1.3 of the Thoth Gateway, proxy authentication is now supported. Here is how it works:


IIS Setup

Set up the application (virtual directory) in IIS where the gateway runs with Integrated Windows Authentication, so that the CGI environment variable LOGON_USER will be populated with the client's Windows username. (If the user is using Internet Explorer to browse the site, his identity will be passed on to the web server/gateway automatically; if using another browser, then an explicit logon is required.)

Oracle Setup

Define an "application server user", ie the common user that connections will be established through:


-- Log on as DBA (SYS or SYSTEM) that has CREATE USER privilege.


create user appserver identified by eagle;



create user end_user identified by secret;
grant create session to end_user;
alter user end_user grant connect through appserver;




Now test the setup with SQL*Plus, by connecting with the "application server user", and then "becoming" the end user:



-- note we don't specify the end_user password, but still become that user


SQL> connect appserver[end_user]/eagle
Connected.
SQL> select user from dual;


USER
------------------------------
END_USER


SQL>

Note that since the point of this is to take advantage of existing Active Directory accounts, you probably want to create your users like this:

create user "your_domain\end_user" identified externally;

grant create session to "your_domain\end_user";

alter user "your_domain\end_user" grant connect through appserver;

Thoth Gateway Setup

In web.config, modify the DAD settings (the following example assumes a local Oracle XE installation):


param name="DatabaseConnectString" value="//127.0.0.1:1521/xe"
param name="DatabaseConnectStringAttributes" value="Enlist=false;Proxy User Id=appserver;Proxy Password=eagle;"
param name="DatabaseUserName" value="LOGON_USER"
param name="DatabasePassword" value=""


Notice the value "LOGON_USER" specified for the DatabaseUserName parameter. This is a reserved string that will be replaced with the actual value of the LOGON_USER value from the web request (ie. the user's Windows username, typically "domain\username"). You can also specify "LOGON_USER_NO_DOMAIN" to strip away the domain part of the user name -- what you use will depend on how you have set up your user accounts in Oracle.

Testing It

To test that everything works at this point, create a procedure similar to the following, and execute it via the gateway (don't forget to grant execute privileges on it to the end-user's account, and create a public synonym for it unless you prefix with the procedure owner's name in the URL).


procedure test_proxy_auth
as
begin


  htp.header(1, 'Proxy authentication');


  htp.ulistopen;
  htp.listitem ('USER = ' || user);
  htp.listitem ('Proxy user = ' || sys_context('userenv', 'proxy_user'));
  htp.listitem ('CGI LOGON_USER = ' || owa_util.get_cgi_env('LOGON_USER'));
  htp.ulistclose;


end test_proxy_auth;

If successful, the USER function should return the end-user's Windows username, and the Proxy User should display as "appserver".

Postscript: A little enigma

Actually, if you do as described above, you could possibly get this error when you try to run the procedure via the gateway:

ORA-1045: user %s lacks CREATE SESSION privilege; logon denied

At least, that's what I got . To get around it, I had to explicitly grant this to the "appserver" user:

grant create session to appserver;

The funny thing is that my example above, tested via SQL*Plus, shows that this works without the grant! But when attempting the same connection via ODP.NET, it gives the above error unless the grant is made.

And if I revoke the "create session" from the end_user, the above example doesn't work in SQL*Plus, because of the missing privilege. Which seems to contradict the purpose of proxying, as defined at the top of this blog post.

If anybody knows why SQL*Plus and ODP.NET show different behaviour here, please let me know.

Thoth Gateway version 1.3 available

There is a new version of the Thoth Gateway, a mod_plsql replacement for IIS, available for download. The latest version is 1.3.

It contains the following bug fixes and enhancements:

• Bug Fix: Issue with parsing client IP address: Added exception handling to prevent error when parsing client IP address with invalid format.
• Ignore additional request parameters: Certain tools and frameworks may dynamically add additional parameters to a request, which causes the corresponding PL/SQL call to fail, since these parameters are not defined in the procedure signature. As of this version, the gateway will now retry the call after dropping (ignoring) any parameters that cannot be found in the Oracle data dictionary for the procedure being called.
• Support for Oracle proxy authentication (and Single Sign On) via dynamic username substitution: Oracle proxy authentication, combined with Integrated Windows Authentication in IIS, allows you to pass the end-user's identity from the client to the database session (so the function USER will return the end-user's Windows username, with no login required). This is useful in an intranet scenario where users are defined in an Active Directory domain and use Internet Explorer to access the PL/SQL web application.

For existing installations, simply overwrite the existing PLSQLGatewayModule.dll file in the "bin" folder with the latest version from the downloaded archive.

Apex on Thoth Gateway and IIS 7

Several people have asked for instructions on how to run the Thoth Gateway (a mod_plsql replacement) on Microsoft Internet Information Server (IIS) 7.

I finally had the chance (and the time!) to test the configuration on a server running IIS 7. The biggest challenge was understanding the new administration console user interface for IIS 7; it was was slightly confusing for someone who is used to IIS 6.

After a bit of fiddling, I got the gateway up and running:

I have updated the installation instructions in the latest download package (version 1.2.1) with separate sections for IIS 6 and IIS 7. So download, unzip and read the instructions in the "doc" folder.

And leave a comment if you run into problems (or leave a comment if you managed to get it up and running using the instructions!).

Small patch for the Thoth Gateway (Apex on IIS)

Just a quick note to announce that a minor patch release (version 1.2.1) of the Thoth Gateway is available for download. The Thoth Gateway allows you to run Oracle Apex applications on the Microsoft IIS web server (a replacement for Apache and mod_plsql).

The following has changed:

• Bug Fix for Content-Length: Fixed an issue where the content-length header would be incorrectly set for non-AL32UTF8 databases if the page contained multibyte characters.
• Set ODP.NET connection string attributes: Added option to specify additional connection string attributes in the DAD configuration. This allows you to fine-tune the connection properties. See the ODP.NET documentation for more details.

Upgrade for existing installations: Simply download the latest version, unzip the archive and drop the PLSQLGatewayModule.dll file into your bin folder on the web server.

More PL/SQL Gateway Goodies

Hot on the heels of version 1.1, which was the topic of my previous blog post, the Thoth Gateway version 1.2 improves on the automatic SOAP Web Service feature, and adds a few new features as well!

Improved Automatic SOAP Web Services

Previously, every function had its own separate service endpoint. This was a bit of a pain, as you would have to (in Visual Studio-speak) add a separate web reference to each function. Now, all functions in a package are grouped together into a single service endpoint. Just add "?wsdl" to the package name, like this:

Upload files to file system instead of database table

Normally, files uploaded via a web page will be stored in the database table specified as "DocumentTableName" in the DAD configuration. In this version, there is a new configuration parameter, "DocumentFilePath", that will cause uploaded files to be saved to the file system instead.

XDB Integration

This version of the Thoth Gateway adds easy integration with Oracle XDB.

Upload files to XDB repository: You can specify a "DocumentXdbPath" in the DAD configuration file that causes uploaded files to be inserted as XDB resources in the specified folder. (This means there are now three different, and mutually exclusive, destinations for uploaded files: database table, file system, and XDB repository.)

Here, for example, we have just uploaded a zip file to XDB, which is then available via SQL, HTTP, FTP and WebDAV as usual:



Download files from XDB repository: You can specify an "XdbAlias" in the DAD configuration file. If this is specified, it will set up a virtual directory (similar to the "PathAlias" parameter) that forwards requests to the XDB repository. You can control which part of the repository you want to expose by specifying the "XdbPathRoot" parameter.

Here we are downloading the zip file via the gateway:



The new options are more fully explained in the installation guide.

Check out version 1.2 of the Thoth Gateway now!

Publish PL/SQL as SOAP Web Service

You can easily consume a SOAP Web Service from PL/SQL, for example using Application Express or the FLEX_WS_API (see also my companion utilities to FLEX_WS_API).


But if you want to publish (or "expose") your PL/SQL procedures as a SOAP Web Service, your options have so far been a bit limited.

JDeveloper/JPublisher

JDeveloper has a "Publish as Web Service" feature that uses JPublisher to create various Java artifacts which must then be deployed to the application server. There are some details here, and an issue you need to be aware of if you are using Oracle 10g Express Edition (XE).

Now, this Java-based approach probably works fine for you if you have Java developers and a Java infrastructure in your company, although the need to (re-)generate the Java code whenever the PL/SQL code changes seems like a bit of a hassle to me.

Native Web Services (11g)

Oracle 11g (Release 1) introduced "Native Web Services". This is a servlet running in the XDB listener that automagically exposes PL/SQL code as SOAP Web Services. Here is some more information about it.

If you are a database guy like me, you probably like the "Native" approach better than the JPublisher method. However, there are a couple of issues with Native Web Services; first of all, it's an 11g feature (which of course means that it is not available in 10g, nor in Express Edition 10g), and it requires the XDB listener (which means you must either allow direct connections to your database, or set up another web server as a proxy for XDB).

Automatic Web Services with the Thoth Gateway

Since I like the concept of Native Web Services, I decided to implement a similar feature in the Thoth Gateway, a mod_plsql replacement for Microsoft IIS.

Version 1.1 of the Thoth Gateway adds a new DAD configuration parameter called InvocationProtocol. If this is set to "SOAP" (instead of the default "CGI"), PL/SQL called through the DAD will take its parameters from a SOAP request body, and respond with a SOAP response body.

The Web Service Definition Language (WSDL) document is automatically generated if you append "?wsdl" to the end of the URL. This allows a tool like Visual Studio to easily add a Web Reference to your stored procedure.

Let's see an example. Let's say we have the following package specification:

create or replace package employee_service
as

function get_employee_name (p_empno in number) return varchar2;

function get_employees (p_search_filter in varchar2) return clob;

end employee_service;
/

And the following package body:

create or replace package body employee_service
as

function get_employee_name (p_empno in number) return varchar2
as
l_returnvalue emp.ename%type;
begin

begin
select ename
into l_returnvalue
from emp
where empno = p_empno;
exception
when no_data_found then
l_returnvalue := null;
end;

return l_returnvalue;

end get_employee_name;


function get_employees (p_search_filter in varchar2) return clob
as
l_context     dbms_xmlgen.ctxhandle;
l_returnvalue clob;
begin

-- there are many ways to generate XML in Oracle, this is one of them...

l_context := dbms_xmlgen.newcontext('select * from emp where lower(ename) like :p_filter_str order by empno');

-- let's make Tom Kyte happy :-)
dbms_xmlgen.setbindvalue (l_context, 'p_filter_str', lower(p_search_filter) || '%');

l_returnvalue := dbms_xmlgen.getxml (l_context);

dbms_xmlgen.closecontext (l_context);

return l_returnvalue;

end get_employees;


end employee_service;
/

Now navigate to the following URL with the browser (assuming you have downloaded and installed the Thoth Gateway, of course; see the installation guide in the Doc folder):

http://localhost/pls/soap-demo/employee_service.get_employee_name?wsdl

This brings up the automatically generated WSDL:




Now use your favorite SOAP testing tool (I'm using Web Service Studio, but another good tool is SoapUI) and enter the same URL.

After the test tool has generated a proxy class for the Web Service, you should see something similar to the following:



Fill in the value in the request and invoke the Web Service:



The response from the Thoth Gateway is a SOAP envelope that contains the return value of the function.

Invoking the second function in the example package above returns a CLOB with XML that represents a dataset with several rows:



Pretty cool, heh? It "just works", with no extra code or configuration necessary, except specifying "SOAP" as the protocol in the DAD!

You can use the usual parameters such as InclusionList, ExclusionList and RequestValidationFunction to control access to specific procedures. Also, the CGI environment is set up as usual before the call, so your PL/SQL code can use owa_util.get_cgi_env to get information about the client (browser).

Limitations and Caveats

There are a couple of limitations in this first release of the SOAP feature:

• You can only call PL/SQL functions (not procedures) via SOAP.
• Functions must return VARCHAR2 or CLOB (but as we have seen in the example above, functions returning CLOBs allow you to return any XML as the response, so this should not really be a big limitation). Support for arrays and complex (user-defined) types might come later.
• Each function is exposed as a service endpoint. This means that in Visual Studio, for example, you must create a separate Web Reference for each function you would like to call. A future version of the gateway might group all functions in a package into one service. Update (Nov 28, 2009): As of Thoth Gateway version 1.2, all functions in a package are now grouped together as a single service endpoint.

If you would like to try it out, go and grab version 1.1 of the Thoth Gateway now!

Thoth Gateway: mod_plsql replacement for Microsoft IIS

Take a look at this screenshot:



Yes, that's right... Oracle Application Express running on Microsoft's Internet Information Server (IIS)! In-between trips to the beach this summer, I decided to write a PL/SQL gateway module for IIS. The result, called the Thoth Gateway, is now available as open source.

This means that the deployment options for PL/SQL web applications are now:

• The Embedded PL/SQL Gateway (DBMS_EPG), which is a webserver built into the database itself
• The Oracle HTTP Server (OHS), which is based on the Apache codebase, with mod_plsql
• Java-based web servers (such as Tomcat, WebLogic, and others) with the open-source DBPrism plugin, or the upcoming, Oracle-supported Apex Listener
• And now, Microsoft Internet Information Server (IIS) with the Thoth Gateway module

The Thoth Gateway is implemented in C# as an ASP.NET HttpModule and uses the Oracle Data Provider for .NET (ODP.NET) to communicate with the database.

The Thoth Gateway supports almost all of mod_plsql's features, and even adds a few more (such as CLOB parameter support for values over 32K). Check out the project page for the details.

It's also worth noting that the Thoth Gateway, being a volunteer project, is not officially supported by Oracle in any way. But at least now you have the option of using Microsoft's IIS as the web server for Oracle PL/SQL web applications. In fact, you can use any of the above listed gateways side-by-side for the same PL/SQL web application (all on the same box, or on different physical servers).

I am very interested in feedback if you decide to go ahead and try the Thoth Gateway, so feel free to add comments to this post about any bugs or problems you encounter, or use the project's issue tracker (adding issues requires a Google account).