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... ? 

MS Exchange API for PL/SQL

As mentioned in my earlier post, I've been working on a PL/SQL wrapper for the Microsoft Exchange Web Services (EWS) API. The code is now ready for an initial release!

Features

Using this pure PL/SQL package, you will be able not just to search for and retrieve emails and download attachments, but you will also be able to create emails and upload attachments to existing emails. You can move emails between folders, and delete emails. You can read and create calendar items. You can get the email addresses of the people in a distribution (mailing) list, and more.

Prerequisites

• You need access to an Exchange server, obviously! I've done my testing against an Exchange 2010 server, but should also work against Exchange 2007.
• If the Exchange server uses HTTPS, then you need to add the server's SSL certificate to an Oracle Wallet. See this page for step-by-step instructions.

Usage

The API should be fairly self-explanatory. The only thing you need to do is to call the INIT procedure at least once per session (but remember that in Apex, each page view is a new session, so place the initialization code in a before-header process or a page 0 process).


begin
  ms_ews_util_pkg.init('https://thycompany.com/ews/Exchange.asmx', 'domain\user.name', 'your_password', 'file:c:\path\to\Oracle\wallet\folder\on\db\server', 'wallet_password');
end;

Note also that there are two varieties of most functions: One pipelined function intended for use from plain SQL, and a twin function suffixed with "AS_LIST" that returns a list intended for use with PL/SQL code.

Code Examples

-- resolve names


declare
  l_names ms_ews_util_pkg.t_resolution_list;
begin
  debug_pkg.debug_on;
  l_names := ms_ews_util_pkg.resolve_names_as_list('john');
  for i in 1 .. l_names.count loop
    debug_pkg.printf('name %1, name = %2, email = %3', i, l_names(i).mailbox.name, l_names(i).mailbox.email_address);
  end loop;
end;




-- resolve names (via SQL)


select *
from table(ms_ews_util_pkg.resolve_names ('john'))




-- expand distribution list


declare
  l_names ms_ews_util_pkg.t_dl_expansion_list;
begin
  debug_pkg.debug_on;
  l_names := ms_ews_util_pkg.expand_public_dl_as_list('some_mailing_list@your.company');
  for i in 1 .. l_names.count loop
    debug_pkg.printf('name %1, name = %2, email = %3', i, l_names(i).name, l_names(i).email_address);
  end loop;
end;




-- get folder


declare
  l_folder ms_ews_util_pkg.t_folder;
begin
  debug_pkg.debug_on;
  l_folder := ms_ews_util_pkg.get_folder (ms_ews_util_pkg.g_folder_id_inbox);
  debug_pkg.printf('folder id = %1, display name = %2', l_folder.folder_id, l_folder.display_name);
  debug_pkg.printf('total count = %1', l_folder.total_count);
  debug_pkg.printf('child folder count = %1', l_folder.child_folder_count);
  debug_pkg.printf('unread count = %1', l_folder.unread_count);
end;




-- find up to 3 items in specified folder


declare
  l_items ms_ews_util_pkg.t_item_list;
begin
  debug_pkg.debug_on;
  l_items := ms_ews_util_pkg.find_items_as_list('inbox', p_max_rows => 3);
  for i in 1 .. l_items.count loop
    debug_pkg.printf('item %1, subject = %2', i, l_items(i).subject);
  end loop;
end;




-- get items in predefined folder


select *
from table(ms_ews_util_pkg.find_folders('inbox'))




-- get items in predefined folder, and search subject


select *
from table(ms_ews_util_pkg.find_items('inbox', 'the search term'))




-- get items in user-defined folder


select *
from table(ms_ews_util_pkg.find_items('the_folder_id'))




-- get items in user-defined folder, by name


select *
from table(ms_ews_util_pkg.find_items(
             ms_ews_util_pkg.get_folder_id_by_name('Some Folder Name', 'inbox')
            )
          )




-- get item (email message)


declare
  l_item ms_ews_util_pkg.t_item;
begin
  debug_pkg.debug_on;
  l_item := ms_ews_util_pkg.get_item ('the_item_id', p_include_mime_content => true);
  debug_pkg.printf('item %1, subject = %2', l_item.item_id, l_item.subject);
  debug_pkg.printf('body = %1', substr(l_item.body,1,2000));
  debug_pkg.printf('length of MIME content = %1', length(l_item.mime_content));
end;




-- get item (calendar item)


declare
  l_item ms_ews_util_pkg.t_item;
begin
  debug_pkg.debug_on;
  l_item := ms_ews_util_pkg.get_item ('the_item_id', p_body_type => 'Text', p_include_mime_content => true);
  debug_pkg.printf('item %1, class = %2, subject = %3', l_item.item_id, l_item.item_class, l_item.subject);
  debug_pkg.printf('body = %1', substr(l_item.body,1,2000));
  debug_pkg.printf('length of MIME content = %1', length(l_item.mime_content));
  debug_pkg.printf('start date = %1, location = %2, organizer = %3', l_item.start_date, l_item.location, l_item.organizer_mailbox_name);
end;




-- create calendar item


declare
  l_item ms_ews_util_pkg.t_item;
begin
  debug_pkg.debug_on;
  l_item.subject := 'Appointment added via PL/SQL';
  l_item.body := 'Some text here...';
  l_item.start_date := sysdate + 1;
  l_item.end_date := sysdate + 2;
  l_item.item_id := ms_ews_util_pkg.create_calendar_item (l_item);
  debug_pkg.printf('created item with id = %1', l_item.item_id);
end;




-- create task item


declare
  l_item ms_ews_util_pkg.t_item;
begin
  debug_pkg.debug_on;
  l_item.subject := 'Task added via PL/SQL';
  l_item.body := 'Some text here...';
  l_item.due_date := sysdate + 1;
  l_item.status := ms_ews_util_pkg.g_task_status_in_progress;
  l_item.item_id := ms_ews_util_pkg.create_task_item (l_item);
  debug_pkg.printf('created item with id = %1', l_item.item_id);
end;




-- create message item


declare
  l_item ms_ews_util_pkg.t_item;
begin
  debug_pkg.debug_on;
  l_item.subject := 'Message added via PL/SQL';
  l_item.body := 'Some text here...';
  l_item.item_id := ms_ews_util_pkg.create_message_item (l_item, p_to_recipients => t_str_array('recipient1@some.company', 'recipient2@another.company'));
  debug_pkg.printf('created item with id = %1', l_item.item_id);
end;




-- update item
-- item id and change key can be retrieved with following query:
-- select item_id, change_key, subject, is_read from table(ms_ews_util_pkg.find_items('inbox'))


declare
  l_item_id varchar2(2000) := 'the_item_id';
  l_change_key varchar2(2000) := 'the_change_key';
begin
  ms_ews_util_pkg.update_item_is_read (l_item_id, l_change_key, p_is_read => true);
end;




-- get list of attachments


select *
from table(ms_ews_util_pkg.get_file_attachments('the_item_id'))




-- download and save 1 attachment


declare
  l_attachment ms_ews_util_pkg.t_file_attachment;
begin
  debug_pkg.debug_on;
  l_attachment := ms_ews_util_pkg.get_file_attachment ('the_attachment_id');
  file_util_pkg.save_blob_to_file('DEVTEST_TEMP_DIR', l_attachment.name, l_attachment.content);
end;




-- create attachment (attach file to existing item/email)


declare
  l_attachment ms_ews_util_pkg.t_file_attachment;
begin
  debug_pkg.debug_on;
  l_attachment.item_id := 'the_item_id';
  l_attachment.name := 'Attachment added via PL/SQL';
  l_attachment.content := file_util_pkg.get_blob_from_file('DEVTEST_TEMP_DIR', 'some_file_such_as_a_nice_picture.jpg');
  l_attachment.attachment_id := ms_ews_util_pkg.create_file_attachment (l_attachment);
  debug_pkg.printf('created attachment with id = %1', l_attachment.attachment_id);
end;

Download

The MS_EWS_UTIL_PKG package is included in the Alexandria Utility Library for PL/SQL.

Known Issues

The CREATE_ITEM functions don't seem to return the ID of the created item (which do get created in Exchange). I think the issue is with the XML parsing of the returned results; I will look into this later.

Please report any bugs found via the issue list.

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 ?

NTLM for PL/SQL

NTLM, or more properly NTLMSSP is a protocol used on Microsoft Windows system as part of the so-called Integrated Windows Authentication.

Integrated Windows Authentication is also known as HTTP Negotiate authentication, NT Authentication, NTLM Authentication, Domain authentication, Windows Integrated Authentication, Windows NT Challenge/Response authentication, or simply Windows Authentication.

In Microsoft Internet Information Server (IIS), the system administrator can protect a website or folder with "Integrated Windows Authentication". When you browse to this website or folder, you must enter your Windows (domain) username and password to get access (although Internet Explorer will, depending on your security settings, send your credentials automatically without showing a login dialog box). Note that unlike Basic Authentication, which sends the password as plaintext to the web server, the NTLM protocol does not send the password but rather performs a cryptographic "handshake" with the server to establish your identity.

Use of Integrated Windows Authentication via NTLM on IIS is very common inside many companies (ie on intranets and internal web servers), where both the client and web server computers are part of the same, or trusting, domains.


Using NTLM from PL/SQL with UTL_HTTP

Unfortunately, from the PL/SQL developer's perspective, Oracle's UTL_HTTP package does not support NTLM authentication (it only supports Basic authentication via the SET_AUTHENTICATION procedure).

So, if you wanted to retrieve information from your intranet or call a web service (protected by Integrated Windows Authentication) from the database via PL/SQL and UTL_HTTP, you were out of luck.

Until now, that is... :-)


A pure PL/SQL implementation of the NTLM protocol

I came across a Python implementation of the NTLM protocol, and I decided that it should be possible to port this code to PL/SQL. Assisted by a couple of good friends and colleagues, and after a lot of bit-fiddling, reverse-engineering, study of protocol specifications, and liberal use of network packet sniffers, we got it working!

A pure PL/SQL implementation of the NTLM protocol is now available and included in the Alexandria Utility Library for PL/SQL.

The code is organized into two packages: NTLM_UTIL_PKG, which contains protocol-specific functions, and NTLM_HTTP_PKG, which is the package you actually use to make HTTP callouts, and which handles the initial NTLM "handshaking" with the web server.


Example 1: Simple request

This code simply grabs the page you direct it towards, and returns the contents as a CLOB. (What is really going on behind the scenes is a series of requests and responses to establish the authenticated connection, before the actual URL contents is served.)


declare
  l_clob clob;
begin
  debug_pkg.debug_on;
  l_clob := ntlm_http_pkg.get_response_clob('http://servername/page', 'domain\username', 'password');
  debug_pkg.print(substr(l_clob, 1, 32000));
end;




Example 2: Web service call

Here, a (persistent) connection is explicitly established before making one or more requests to the server. Note the returnvalue from the BEGIN_REQUEST function, which is the authorization string which must be passed along in the "Authorization" HTTP header on any subsequent requests. The connection is then is closed. Note that NTLM is a connection-based protocol, and will not work without the use of persistent connections.



declare
  l_url           varchar2(2000) := 'http://servername/page';
  l_ntlm_auth_str varchar2(2000);
  l_xml           xmltype;
  l_soap_env      clob := 'your_soap_envelope_here';
  
begin
  debug_pkg.debug_on;


  -- perform the initial request to set up a persistent, authenticated connection
  l_ntlm_auth_str := ntlm_http_pkg.begin_request (l_url, 'domain\username', 'password');


  -- pass authorization header to next call(s)
  apex_web_service.g_request_headers(1).name := 'Authorization';
  apex_web_service.g_request_headers(1).value := l_ntlm_auth_str;


  -- perform the actual call
  -- NOTE: for this to work, you must be using a version of apex_web_service that allows persistent connections (fixed in Apex 4.1 ???)
  --       see http://jastraub.blogspot.com/2008/06/flexible-web-service-api.html?showComment=1310198286769#c8685039598916415836
  l_xml := apex_web_service.make_request(l_url, 'soap_action_name_here', '1.1', l_soap_env);


  -- or use the latest version of flex_ws_api
  -- flex_ws_api.g_request_headers(1).name := 'Authorization';
  -- flex_ws_api.g_request_headers(1).value := l_ntlm_auth_str;
  -- l_xml := flex_ws_api.make_request(l_url, 'soap_action_name_here', '1.1', l_soap_env);


  -- this will close the persistent connection
  ntlm_http_pkg.end_request;


  debug_pkg.print('XML response from webservice', l_xml);
end;




Remarks

• Tested successfully on Oracle 10g XE (with AL32UTF8 character set) and Oracle 10g EE (with WE8MSWIN1252 character set).
• Tested successfully against IIS 6.0 with non-SSL "plain" website and SSL-enabled Sharepoint website (both set up with Integrated Windows Authentication, obviously).
• The current version ignores cookies when setting up the connection. If you depend on cookies being present, you may have to deal with this specifically.

Given the diverse nature of network configuration, there may be bugs or unhandled cases in the code. So please test the code in your environment and leave a comment below, letting me me know if it works for you or not.