NTLM Authentication in PL/SQL -- Part 1 Project Overview

Goal:
Be able to call a SQL Server Web Service (SSRS) that used NTLM authentication from a PL/SQL package. End goal is to communicate from an Oracle 11g Database to SQL Server Reporting Server using SOAP (Simple Object Access Protocol) to return data that we cant currently get due to the expensive driver required to create a DB link between the Oracle and SQL Server.

Problem:
NTLM is a Microsoft proprietary authentication system and doesn't play nice with PL/SQL developers. Currently (11g r2) Oracle does not offer support for NTLM authentication, in Java however, that is a different story.

Steps I've taken:
-Use SoapUI to send requests to the web service. SoapUI is a handy program that provides an XML template for creating your SOAP requests and then displays the response in cleanly formatted XML. SoapUI also supports various authentication methods including Basic Authentication (plain text password exchange) and NTLM v1, which is what the web service I'm working with requires.

-Use Wireshark (a great open-source packet analyzer) to observe the NTLM authentication process and get an understanding of what needs to be done. Wireshark can decode the NTLM auth tokens pretty well but I ran into some quirks, mostly incorrectly showing the location in the packet where certain information was derived from. Despite some initial frustration due to skewed data, Wireshark was absolutely instrumental in this project.

-Start the long tedious process of replicating the NTLM auth tokens. There are three important tokens in this process that make up the authentication 'handshake.' The client sends the first token with basic information about itself excluding the password, the servers responds with a token with a challenge (a seemingly random 8-byte hex string), and then the client takes obfuscates the password with this challenge and send the third token back with it. This process requires lots of low-level programming, and we will be attempting in a very high-level language, PL/SQL.

-Fail. Yep, after a week of programming and testing, I can replicate the NTLM auth tokens down to the BIT. Yet I still get an 'Unauthorized' response. Bummer, I fully believe that this can work but I am getting held up by either a network firewall that is protecting the Oracle DB and not protecting my desktop, or the Microsoft web service simply refuses to accept requests from a Linux box (where the DB resides) but will from my Windows 7 desktop. I think this needs to revisited but in order to keep this project moving forward I will try switching to Java to for the authentication.

-Success! From PL/SQL I call a stored Java source and pass it my credentials along with the soap message I want to sent the web service. Java handles the authentication by creating an SSL connection and java.net.Authenticator does the rest of the NTLM magic for you. It's too bad really how simple it is in Java.

Note: Some responses from web services are larger then a PL/SQL VARCHAR2 (32k) for this I used a clob. The constructor for a clob is protected in Java because they want you to use it in conjunction with a java.sql.ResultSet. In order to overcome this problem I passed an initialized clob from PL/SQL to Java, wrote in it, then passed it back.