Wednesday, August 3, 2011

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.

20 comments:

frædrik said...

Fantastic, Morten!

x said...

Good job !

Tony said...

The timing of this couldn't be better. Just yesterday I had resigned myself that interacting with windows authenticated SharePoint web services was a lost cause and was seeking alternatives. Then this wonderful package came along, an hour later I am importing documents.

Thanks to you and your colleagues for tackling this issue and coming up with a nice solution.

Anonymous said...

Hello Morton,

Having problems installing the Thoth Gateway on a Windows 7 64 Pro edition.


Is it possible to contact you on this by e-mail?

My e-mail is:
a.lopez@home.nl


Thx.

Regards,

Anibal

Marco Gralike said...

Keep up the cool work Morton. Maybe an idea to extend the Alexandria PL/SQL toolset with the XML utilities set called "XFILES" / "XDBPM"

Oracle XML DB Utility Scripts (July 2008)

XFiles Demo for Oracle Database 11g Release 1 XML DB Oct 2008

Free stuff, maintained by the Sr. Prod. Manager, which can be found here:

http://www.oracle.com/technetwork/database/features/xmldb/index.html

Have a look at the PL/SQL code, I am sure you will like the code

;-)

Marco Gralike
AMIS Services, The Netherlands.

Marco Gralike said...

Second attempt regarding the URL

www.oracle.com/technetwork
/database/features
/xmldb/index.html

Can't give you a hard link due to all the changes going on nowadays on the Oracle (OTN) site.

Anonymous said...

Where do i get the apex_web_service Package, to make the call..!!

Morten Braten said...

@Marco: Thanks for the tip, I'll have a look at it for sure.

- Morten

Morten Braten said...

@Anonymous: The APEX_WEB_SERVICE package is included with Apex 4:

http://apex.oracle.com/i/doc/AEAPI/apex_web_service.htm

However, due to a bug with persistent connections (see my comment in the example code), you need to use the FLEX_WS_API package (which is more or less the same package) until the Apex package has been fixed (possibly in Apex 4.1).

https://flex-ws-api.samplecode.oracle.com/



- Morten

Omar Sawalhah said...

Hello Morton,
I am not sure if my question applicable to this post, but your prompt is highly appreciated. How could this help in
"Using Windows Login Credentials for Single Sign On (NTLM authentication)" for Apex applications.

thanx

Stefan said...

Hi,
very nice job by the way !

I used 1.50, but needed to disable the debug package, because some apex_application package was missing.

But now, easy !!

Thanks,

From Switzerland

Neil Allen said...

Hi, Morten-
On a related note, is there a way I can write an Excel file from Apex TO Sharepoint? Automatically?

Thanks.

-Neil

Morten Braten said...

@Neil: If Sharepoint has a web service that allows you to upload files, then yes, you should be able to use that to upload your Excel files.

You would need to look at the Sharepoint API documentation and find out what the SOAP request needs to look like to call the specific web service operation.

From a quick Google search, it looks like there is a CopyIntoItems method that you can use, and there are some details on what the SOAP message should look like here:

http://stackoverflow.com/questions/7976329/sharepoint-copyintoitems-soap-message-in-objective-c

So basically:
1. Authenticate with the NTLM package against the Sharepoint server.
2. Construct a valid SOAP request containing your file.
3. Use the APEX_WEB_SERVICE package to POST your SOAP envelope to the Sharepoint server.


- Morten

Morten Braten said...

@Neil: Here is another link with detailed information about the SOAP request:

http://www.sharepointsdev.com/sharepoint--development-programming-presharepoint-2010/using-copyintoitems-from-copyasmx-from-a-remote-soap-client-75982.shtml

- Morten

Magnus Börjesson said...

Thank you so much for this!

I had a case where I desperately needed just this feature. (I had to connect to a password-protected web service on a Microsoft server from within PL/SQL.)

We didn't have Apex, but I managed to get it to work with SOAP_API with only a few minor tweaks. Basically, I added the NTLM authentication string explicitly as a SOAP header, using UTL_HTTP.set_header.

Oh, and I also had to change the prefix 'NTLM' to 'Negotiate' in NTLM_HTTP_PKG, because that was what this server wanted. Easy enough to do, but NOT very easy to figure out, at least not for me! :)

Thank you again!

jed said...

Hi Morten, just wondering if we can use this in utl_dbws? Have you tried it?

Thanks!

jed said...

Hi Morten,

I was able to apply your solution using PL/SQL and UTL_HTTP (without using Apex).

Thank you so much for your help!

Cheers!

Satheesh said...

I am not able to see that package. Please somebody give me the link to download that package.

Morten Braten said...

@Satheesh: The link to the Alexandria Library is http://code.google.com/p/plsql-utils/

Download the latest zip file from there, unzip and you'll find the NTLM packages.

- Morten

sachin jayashekhar said...

hi tony

could you pls mail me your package on rfquotes@gmail.com wherein you are preparing your soap envelope and using the NTLM package set out by Morten? I am building one and am having difficulties in getting it right. much appreciated.

thanks
regards
sac