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.

32 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

Unknown 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

Unknown 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

Miguel said...

Hi Morten,

i'm hitting an ORA-6502 on the parse_challenge_message, i'm using US7ASCII charset. could this be related with that?

thx

PRASHANT MISHRA said...

Do we have the same implementation for POST method? As one can see this implememtation is for GET REQUEST.

PRASHANT MISHRA said...

Here ,I can see in code that you have used 'GET' for sending the request.
Can we use 'POST' for hitting web services??
Someone please respond!!

Morten Braten said...

@Prashant: The examples provided show both a GET and a POST, because apex_web_service.make_request in fact does a POST when it invokes a SOAP web service.

Also check out the docs for apex_web_service.make_request_request which allows you to specify the HTTP method yourself:

http://docs.oracle.com/cd/E37097_01/doc.42/e35127/apex_web_service.htm#AEAPI1955

Of course, you can also use pure utl_http calls to do a POST.

- Morten

Eric Olson said...

This is really great and exactly the sort of package I need, except there is only a CLOB version of get_response. I'm retrieving a binary (Excel) file. Is there any easy way to get the response as a BLOB?

Antony said...

Hi Morten,

Is this package just for authentication, and for request/reponse I should use some other package like Apex web service or FLEX_WS_API:

//first authenticate
l_clob := ntlm_http_pkg.get_response_clob('http://servername/page', 'domain\username', 'password');

//now send request and get reponse using apex or flex ws
...

Thanks,
Antony

Morten Braten said...

@Antony: Please see example 1 and 2, respectively. The get_response_clob function in example 1 is a convenience function that does both authentication and returns whatever response is received from the remote server.

If you need to authenticate and do multiple requests, see example 2, where it is stated "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." You could use apex_web_service, flex_ws_api, or even raw utl_http for subsequent requests, as long as you can pass the specified headers along with the request.

- Morten

Saeed Hassanpour said...

Hi Morten,

Thank you so much for sharing this library.
I'v used the example 1 without proxy server so I could get result but with the proxy server in third step of Response got Unauthorized result.

----
http://test.com/
Response Status Code: 401
#1 Server : nginx/1.8.0
#2 Date : Thu, 17 Sep 2015 04:53:09 GMT
#3 Content-Type : text/html
#4 Content-Length : 1293
#5 Connection : close
#6 WWW-Authenticate : Negotiate
#7 WWW-Authenticate : NTLM
#8 X-Powered-By : ASP.NET
Body length = 1293
Persistent connection count: 0
Persistent connection count (should be zero): 0
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
....
401 - Unauthorized: Access is denied due to invalid credentials
...


Regards,
Saeed.

Unknown said...

HI Morten,

First of all thank you so much for the NTLM explanation, I was able to successfully use this to call the web service.

But now, I have one issue, which is a very confusing to debug it. At present when the web service call was working , we were using WWXXX Domain, later all our Domains got migrated to ADXXX. Now, when we trying to send the credentials with AD Domain the call is failing at the after establishing the connection to the website.

Saying: 401: Unauthorized error/invalid credentials. I am sure this is related to the Active Directory Domain (AD), but we are not able to figure it out, where the issue is.

Your help is very much essential and very much appreciated in this scenario. Thank you so much.

Nikolas said...

Hi Morten,

thank you for this implementation of NTLM. Unfortunately, this package supports only NTLM not NTLMv2, so we cannot connect to our Sharepoint server. Is there a chance, that you also implement NTLMv2?

I think that this is also the reason why so many users get the 401 when they update their servers with the latest servicepacks. Microsoft products now require NTLMv2.

Nevertheless this is a very impressive library.

Morten Braten said...

@Nikolas: As mentioned in the blog post, the PL/SQL implementation was a port of a Python library, and the Python library does not support NTLMv2 either. Theoretically it is just a matter of reading up on the new protocol version and implementing it, but it would be a lot of work, and there are no current plans to do so.

- Morten

Unknown said...

Hi Morten,

Thanks for explanation. Is there any other work around which you can suggest for this kind of problem which can be implemented to fix the issue. Thank you.

Jaswanth