NTLM Authentication in PL/SQL -- Part 2 Type1 Message

The first of three NTLM tokens in the authentication handshake is called a Type 1 message, easy enough. In standard practice, the client will make a request and the server will return a packet that has a status of 401 (unauthorized) and checks the headers for supported authentication methods. If the client finds a header named “WWW-Authenticate” with the value “NTLM” it proceeds to build the first part of the NTLM handshake.

A Type1 message is sent from the client to the server as a header in another HTTP request. The Type1 message contains information on the domain and workstation along with flags denoting the version of NTLM supported and content type.

Update:
I have not been able to get authenticated via this method, I can build the NTLM tokens seemingly perfectly, yet no success. I think the problem is with network permissions from my oracle DB to the called web service.

The following is a function that will build a Type 1 message. It uses other simpler functions such a SWAP_ENDIAN and DEC2HEX that I will post below.

FUNCTION BUILD_TYPE1
(
DOMAIN      VARCHAR2
,WORKSTATION VARCHAR2
) RETURN VARCHAR2 IS
SIGNATURE        VARCHAR2(1000);
INDICATOR        VARCHAR2(1000);
FLAGS            VARCHAR2(1000);
DOMAIN_BUFF      VARCHAR2(1000);
WORKSTATION_BUFF VARCHAR2(1000);
OS_VERSION       VARCHAR2(1000);
WORKSTATION_STR  VARCHAR2(1000);
DOMAIN_STR       VARCHAR2(1000);

HEX_RESULT  VARCHAR2(4000);
NTLM_RESULT VARCHAR2(4000);
SIGNATURE_R RAW(16);
NTLM_TYPE   NUMBER := 1;
-- use default flags                            
NTLM_FLAGS VARCHAR2(50) := '06520000';

-- Bytes used for specific parts of type1 message, used to find offsets. 1 Byte = 2 CHARs 
NTLM_SIGNATURE_LENGTH  NUMBER := 16;
NTLM_TYPE_LENGTH       NUMBER := 8;
NTLM_FLAG_LENGTH       NUMBER := 8;
NTLM_BUFFSIZE_LENGTH   NUMBER := 4;
NTLM_BUFFOFFSET_LENGTH NUMBER := 8;

DOMAIN_LENGTH      INTEGER;
WORKSTATION_LENGTH INTEGER;
DOMAIN_OFFSET      INTEGER;
WORKSTATION_OFFSET INTEGER;

BEGIN

DOMAIN_LENGTH := LENGTH(DOMAIN);
WORKSTATION_LENGTH := LENGTH(WORKSTATION);

DOMAIN_OFFSET := (NTLM_SIGNATURE_LENGTH 
+ NTLM_TYPE_LENGTH 
+ NTLM_FLAG_LENGTH 
+ NTLM_BUFFSIZE_LENGTH * 4 
+ NTLM_BUFFOFFSET_LENGTH * 2) / 2 
+ WORKSTATION_LENGTH;

WORKSTATION_OFFSET := (NTLM_SIGNATURE_LENGTH 
+ NTLM_TYPE_LENGTH 
+ NTLM_FLAG_LENGTH 
+ NTLM_BUFFSIZE_LENGTH * 4 
+ NTLM_BUFFOFFSET_LENGTH * 2) / 2;

--signature
SIGNATURE_R := UTL_RAW.CAST_TO_RAW('TlRMTVNTUAA');
SIGNATURE := UTL_ENCODE.BASE64_DECODE(SIGNATURE_R);

--type indicator
INDICATOR := NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(NTLM_TYPE, NTLM_TYPE_LENGTH));

--flags
FLAGS := NTLM_FLAGS;

--domain buffer
DOMAIN_BUFF := NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(DOMAIN_LENGTH, NTLM_BUFFSIZE_LENGTH)) ||
NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(LENGTH(DOMAIN), NTLM_BUFFSIZE_LENGTH)) ||
NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(DOMAIN_OFFSET, NTLM_BUFFOFFSET_LENGTH));

--UTL_RAW.CAST_FROM_NUMBER
WORKSTATION_BUFF := NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(WORKSTATION_LENGTH, NTLM_BUFFSIZE_LENGTH)) ||
NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(LENGTH(WORKSTATION), NTLM_BUFFSIZE_LENGTH)) ||
NTLM.SWAP_ENDIAN(NTLM.DEC2HEX(WORKSTATION_OFFSET, NTLM_BUFFOFFSET_LENGTH));

--workstation
WORKSTATION_STR := UTL_RAW.CAST_TO_RAW(UPPER(WORKSTATION));

--domain
DOMAIN_STR := UTL_RAW.CAST_TO_RAW(UPPER(DOMAIN));

HEX_RESULT := SIGNATURE 
|| INDICATOR 
|| FLAGS 
|| DOMAIN_BUFF 
|| WORKSTATION_BUFF 
|| NVL(OS_VERSION, '') 
|| NVL(WORKSTATION_STR, '') 
||NVL(DOMAIN_STR, '');

NTLM_RESULT := UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(HEX_RESULT));

RETURN NTLM_RESULT;

END BUILD_TYPE1;

The following is the makings of an anonymous block that will call the preceding function. It is incomplete because you need to build your own SOAP message to send as the packet contents and of course use your own credentials, workstation, domain, and action.

DECLARE

USERNAME VARCHAR2(100) := 'username';
PASSWORD VARCHAR2(100) := 'password';
--this is the workstation that you are calling.
CALLING_WORKSTATION VARCHAR2(100) := 'workstation';
--this is the domain that your account belongs to.
DOMAIN    VARCHAR2(100) := 'domain';
USERAGENT VARCHAR2(1000) := 'Mozilla/4.0';
URL       VARCHAR2(2000) := 'http://url_of_host.asmx';

TYPE1 VARCHAR2(4000);
REQ   UTL_HTTP.REQ;
RESP  UTL_HTTP.RESP;
--I have a function in my package that I will post later that builds my soap 
--message but that is a different topic.
ENVELOPE VARCHAR2(4000);
--check your server WSDL for the correct action for your SOAP call.
ACTION       VARCHAR2(4000) := 'http://action';
ENVELOPE_TAG VARCHAR2(50) := 'soap';

--for use in printing headers
NAME  VARCHAR2(1000);
VALUE VARCHAR2(4000);

BEGIN
--This is where we will call the BUILD_TYPE1 function, I have mine in a PL/SQL package called NTLM
TYPE1 := 'NTLM ' || NTLM.BUILD_TYPE1(DOMAIN => DOMAIN, WORKSTATION => CALLING_WORKSTATION);

REQ := UTL_HTTP.BEGIN_REQUEST(URL, 'POST', UTL_HTTP.HTTP_VERSION_1_1);

UTL_HTTP.SET_HEADER(REQ, 'Accept-Encoding', 'gzip,deflate');
UTL_HTTP.SET_HEADER(REQ, 'Content-Type', 'text/xml;charset=UTF-8');
UTL_HTTP.SET_HEADER(REQ, 'SOAPAction', ACTION);
UTL_HTTP.SET_HEADER(REQ, 'User-Agent', USERAGENT);
UTL_HTTP.SET_HEADER(REQ, 'Authorization', TYPE1);
UTL_HTTP.SET_HEADER(REQ, 'Content-Length', LENGTH(ENVELOPE));

--send and recieve
UTL_HTTP.WRITE_TEXT(REQ, ENVELOPE);
RESP := UTL_HTTP.GET_RESPONSE(REQ);

DBMS_OUTPUT.PUT_LINE('HTTP response status code: ' || RESP.STATUS_CODE);
DBMS_OUTPUT.PUT_LINE('HTTP response reason phrase: ' || RESP.REASON_PHRASE);

--at this point you will still be unauthorized, but should have a type 2 message in the response header

--print response headers
FOR I IN 1 .. UTL_HTTP.GET_HEADER_COUNT(RESP)
LOOP
UTL_HTTP.GET_HEADER(RESP, I, NAME, VALUE);
DBMS_OUTPUT.PUT_LINE(NAME || ': ' || VALUE);
END LOOP;

--close response
UTL_HTTP.END_RESPONSE(RESP);
END;

The following function treats the input string as a string of bytes, and swaps to and from big and little endian using a chunksize of two bytes.

FUNCTION SWAP_ENDIAN(INITIAL_STR VARCHAR2) RETURN VARCHAR2 IS
RESULT_STR VARCHAR2(1000);
PLACE      INTEGER := 1;
BEGIN
IF MOD(LENGTH(INITIAL_STR), 2) <> 0
THEN
--error
RETURN NULL;
END IF;

WHILE (PLACE < LENGTH(INITIAL_STR))
LOOP
RESULT_STR := SUBSTR(INITIAL_STR, PLACE, 2) || RESULT_STR;
PLACE := PLACE + 2;
END LOOP;
RETURN RESULT_STR;

EXCEPTION
WHEN OTHERS THEN
--error
RETURN NULL;
END SWAP_ENDIAN;

The following function originated from the following location:
http://www.orafaq.com/wiki/Hexadecimal
I edited it slightly for use in this project so the return value would be certain size.

FUNCTION DEC2HEX
(
N        IN NUMBER
,STR_SIZE IN NUMBER
) RETURN VARCHAR2 IS
HEXVAL   VARCHAR2(64);
N2       NUMBER := N;
DIGIT    NUMBER;
HEXDIGIT CHAR;
BEGIN
WHILE (N2 > 0)
LOOP
DIGIT := MOD(N2, 16);
IF DIGIT > 9
THEN
HEXDIGIT := CHR(ASCII('A') + DIGIT - 10);
ELSE
HEXDIGIT := TO_CHAR(DIGIT);
END IF;
HEXVAL := HEXDIGIT || HEXVAL;
N2 := TRUNC(N2 / 16);
END LOOP;
HEXVAL := LPAD(HEXVAL, STR_SIZE, '0');

RETURN HEXVAL;
END DEC2HEX;

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.