Monday, March 17, 2008

NTLM HTTP Authentication and Application Express

I think for my first blog post ever, I should start with a light topic, such as NTLM Authentication in Application Express...

Many customers have expressed interest in using NTLM with Application Express. The argument is that they are already using this authentication in their .NET intranet applications and users of those applications do not have to supply their domain credentials again, the application simply knows who they are. I know many customers have deployed Apache and mod_ntlm, and used a custom authentication scheme described in the following paper:

http://www.greenit.li/website/content/OracleApplicationExpressProofOfConceptNTLM.doc

There have been some problems reported with using mod_ntlm such as configuration and users getting prompted for username and password periodically. I decided to do some investigation to see if there have been any Java or .NET code examples of doing NTLM authentication to see if I could rewrite the code in PL/SQL. I found the following JSP:

http://www.rgagnon.com/javadetails/java-0441.html

There is a problem with the JSP implementation when you are using a browser that won't support NTLM. You get prompted for a username and password and the JSP will just accept whatever is typed in. Luckily though, you can detect that the user was prompted by the size of the token. I kept that in mind when trying to reverse engineer into a PL/SQL solution.

Through some brute force debugging and examination the HTTP traffic, I was able to successfully write some PL/SQL that does essentially the same thing as the JSP. I used the code in the mod_ntlm page sentry function from the white paper referenced above as a starting point. Unlike the JSP, this function will set the username to "nobody" if it detects that the browser prompted the user for their credentials instead of just silently negotiating them. You can then write authorization schemes that deny access to the "nobody" user.

First you need to configure your DAD used for Application Express so that mod_plsql can be aware of a CGI environment variable called "Authorization." To do this:


  1. Find the file that contains the DAD description used for Application Express (most likely $OH/Apache/modplsql/conf/dads.conf)
  2. Edit the DAD entry for Application Express adding PlsqlCGIEnvironmentList AUTHORIZATION
  3. Save the file
  4. Stop and start Apache/ Oracle HTTP Server

Now you can access the CGI environment variable "Authorization." Next you compile a function that will be used as a page sentry function for a custom authentication scheme. Compile this function in the same schema as your application.


create or replace function ntlm_page_sentry
return boolean
is
l_username varchar2(512);
l_session_id number;
l_raw raw(1000);
l_domain varchar2(128);
l_user varchar2(128);
l_auth varchar2(512);
l_decode varchar2(2000);
l_off pls_integer := 0;
l_length pls_integer;
l_offset pls_integer;
l_htp_buffer htp.htbuf_arr;
l_htp_rows INTEGER;
l_url VARCHAR2(500);
l_charset VARCHAR2(128);
begin
-- check to ensure that we are running as the correct database user.
if user != 'APEX_PUBLIC_USER' then
return false;
end if;
-- get sessionid.
l_session_id := wwv_flow_custom_auth_std.get_session_id_from_cookie;
-- check application session cookie.
if wwv_flow_custom_auth_std.is_session_valid then
apex_application.g_instance := l_session_id;
l_username := wwv_flow_custom_auth_std.get_username;
wwv_flow_custom_auth.define_user_session(p_user => l_username,
p_session_id => l_session_id);
return true;
else
-- get username using NTLM
l_auth := owa_util.get_cgi_env('AUTHORIZATION');
if l_auth is null then
owa_util.status_line(nstatus => 401,
creason => 'Unauthorized',
bclose_header => false);
htp.p('WWW-Authenticate: NTLM');
owa_util.mime_header('text/html', false, 'utf-8');
owa_util.http_header_close;
wwv_flow.g_unrecoverable_error := TRUE;
return false;
end if;
if substr(l_auth,1,5) = 'NTLM ' then
l_decode := utl_encode.text_decode(buf => substr(l_auth,6), encoding => UTL_ENCODE.BASE64);
l_raw := utl_raw.cast_to_raw(l_decode);
if utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,14,1)) != 130 then
if utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,9,1)) = 1 then
owa_util.mime_header('text/html', false, 'utf-8');
owa_util.status_line(nstatus => 401,
creason => 'Unauthorized',
bclose_header => false);
htp.p('WWW-Authenticate: NTLM TlRMTVNTUAACAAAAAAAAACgAAAABggAAAAICAgAAAAAAAAAAAAAAAA==');
owa_util.http_header_close;
wwv_flow.g_unrecoverable_error := TRUE;
return false;
end if;
-- Determine DB charset and convert raw to WE8MSWIN1252, thanks to Andrew Barbaccia
select value into l_charset from nls_database_parameters where parameter='NLS_CHARACTERSET';
l_length := utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,32,1))*256 + utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,31,1));
l_offset := utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,34,1))*256 + utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,33,1));
l_domain := replace(replace(substr(convert(utl_raw.cast_to_varchar2(l_raw),l_charset,'WE8MSWIN1252'),l_offset + 1,l_length),chr(0),null),chr(15),null);
l_length := utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,40,1))*256 + utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,39,1));
l_offset := utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,42,1))*256 + utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,41,1));
l_user := replace(substr(convert(utl_raw.cast_to_varchar2(l_raw),l_charset,'WE8MSWIN1252'),l_offset,l_length),chr(0),null);
l_username := l_domain'\'l_user;
else
l_username := 'nobody';
end if;
end if;
-- application session cookie not valid --> define a new apex session.
wwv_flow_custom_auth.define_user_session(p_user => l_username,
p_session_id => wwv_flow_custom_auth.get_next_session_id);
-- tell apex engine to quit.
apex_application.g_unrecoverable_error := true;
if owa_util.get_cgi_env('REQUEST_METHOD') = 'GET' then
wwv_flow_custom_auth.remember_deep_link(p_url => 'f?'
wwv_flow_utilities.url_decode2(owa_util.get_cgi_env('QUERY_STRING')));
else
wwv_flow_custom_auth.remember_deep_link(p_url => 'f?p='
to_char(apex_application.g_flow_id)':'
to_char(nvl(apex_application.g_flow_step_id, 0))':'
to_char(apex_application.g_instance));
end if;
-- register the session in apex sessions table, set cookie, redirect back.
wwv_flow_custom_auth_std.post_login(p_uname => l_username,
p_session_id => nv('APP_SESSION'), p_flow_page => apex_application.g_flow_id
':'nvl(apex_application.g_flow_step_id, 0), p_preserve_case => true);
-- get HTP output wwv_flow_custom_auth_std.post_login has written,
-- it contains the session cookie we need.
-- Thanks to Patrick Wolf for the following code
l_htp_rows := 15; /* where and how to get an actual value for irows???? */
htp.get_page
( thepage => l_htp_buffer
, irows => l_htp_rows
);
-- reset the HTP buffer so that we can write our own header, ...
htp.init;
-- See http://www.nabble.com/Empty-POST-requests-on-IE-td15332680.html
-- We have to trick IE that he thinks the authentication fails, otherwise
-- he doesn't send any data when issueing a POST because he wants to
-- do the NTLM stuff again
owa_util.status_line
( nstatus => 401,
creason => 'Unauthorized',
bclose_header => FALSE
);
-- write the session cookie into our output
FOR ii IN 1 .. l_htp_rows
LOOP
IF l_htp_buffer(ii) LIKE 'Set-Cookie:%'
THEN
htp.p(rtrim(l_htp_buffer(ii), CHR(10)));
END IF;
END LOOP;
--
l_url := 'f?p='
apex_application.g_flow_id':'
nvl(apex_application.g_flow_step_id, 0)':'
apex_application.g_instance;
--
IF WWV_Flow.get_browser_version = 'NSCP'
THEN
-- Firefox: redirect can be set with a HTTP header attribute
htp.p('Location: 'l_url);
owa_util.http_header_close;
ELSE
-- For IE: The javascript is required so that we are redirected to the page as
-- the wwv_flow_custom_auth_std.post_login would normally do with the
-- HTTP 302 redirect
owa_util.http_header_close;
htp.p('<html><head>');
htp.p('<script type="text/javascript">');
htp.p(' location.href="'l_url'";');
htp.p('</script>');
htp.p('<noscript>');
htp.p('<meta http-equiv="Refresh" content="0; URL="'l_url'">');
htp.p('</noscript>');
htp.p('</head>');
htp.p('<body>');
htp.p('You were logged in successfully. Click <a href="'l_url'">here</a> to continue.');
htp.p('</body>');
htp.p('</html>');
END IF;
return false;
end if;
end ntlm_page_sentry;
/




The last step is to create a custom authentication scheme that uses the above function as the page sentry function. To create a custom authentication scheme:

  1. Click Shared Components from the Application Builder home page
  2. Click Authentication Schemes under Security
  3. Click Create >
  4. Choose From scratch and click Next >
  5. Enter NTLM in the Name field and click Next >
  6. Enter return ntlm_page_sentry in the Page Sentry Function text area and click Next >
  7. Click Next > until the Confirm step
  8. Click Create Scheme
  9. Click Change Current
  10. Choose NTLM and Click Next >
  11. Click Make Current

Run the application and you should see your username in the format of DOMAIN\username provided you are using a browser that is configured to support NTLM negotiation.

Now, a couple of notes about browser support and NTLM. (Of course if you are already using NTLM for authentication with other applications, you are well aware of these notes). In order for Internet Explorer to automatically negotiate NTLM, the security settings of the browser must be set to Medium-low or Low. By default, IE is set to Medium-low for local intranet sites, and this authentication really only makes sense for local intranet sites.

Firefox will work with NTLM, but each browser has to be configured to trust each server where you want to employ NTLM. To configure Firefox to negotiate NTLM with a specific server:

  1. Type about:config in the address bar
  2. Type ntlm in the filter text box
  3. Double click the preference network.automatic-ntlm-auth.trusted-uri's and enter a comma separated list of trusted servers on your network

Vista has local security policies that, by default, do not allow browser negotiation of NTLM authentication. (Again, you already know this if you have Vista and NTLM auth employed with applications in your environment). The link that follows contains information on how to change this.

http://www.jimmah.com/vista/Networking/ntlm.aspx

If you are now thinking to yourself, "wow, I can't believe I would have to change this setting on every Vista client in my organization," then you should familiarize yourself with the notion of group policy and the following document:

http://msdn2.microsoft.com/en-us/library/ms814176.aspx

Again, NTLM authentication is really only relevant for clients that are part of an Active Directory domain, and therefore, group policy would apply.

Finally, it is possible (with any application that authenticates with NTLM, not just this example) for someone to sniff traffic on your network, see the NTLM authorization token for a specific user, and then use that token to spoof the identity of someone and use your application. You should:

  1. Find out who these people are and fire them or get them fired
  2. Use SSL


Update 3/19/2008: I should mention that this solution only works with Apache/ Oracle HTTP Server and is not supported by the XDB HTTP Server with the embedded PL/SQL gateway (EPG), yet...

Update 4/17/2008: For more information on why this solution will not work with the embedded PL/SQL gateway, see the section titled "Configuring Static Authentication with DBMS_EPG" in the following document:

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_web.htm#BGBCFIIB

"The database rejects access if the browser user attempts to connect explicitly with the HTTP Authorization header."

Update 5/8/2008: Patrick Wolf discovered an issue described at the following post:

http://forums.oracle.com/forums/thread.jspa?messageID=2511974&#2511974

He also came up with a very elegant solution described in the same post. I guess I should have tested this method with a more complex application (like one that posts a page). ;) Anyway, thanks to Patrick and I have included his fix in an updated version of the function.

Update 5/14/2008: John Scott may have discovered a way to use this authentication mechanism with the EPG, using Apache to proxy requests to EPG and rewriting the Authorization header:

http://forums.oracle.com/forums/thread.jspa?threadID=652805&start=15&tstart=0

Update 8/20/2008: It seems that checking the length of the NTLM token has proven unreliable to detect the case where the browser prompted for a username and password. I have found that when the browser prompts the user, the token "NTLM TlRMTVNTUAABAAAAB4IIAAAAAAAAAAAAAAAAAAAAAAA=" is consistently passed by the client. I have altered the PL/SQL code to test for this token instead of the token length.


Update 11/17/2008: I have updated the function to include two changes. The first is a suggestion from Andrew Barbaccia about character set conversion. See the comments below and the referenced forum discussion.


The second modification is how we detect when the browser prompted for username and password. I noticed that recenlty, the token changed in this case when using IE7, although the token was the same in FF. Ilmar in his comments below has come accross the same issue. I did a little more investigation and have found that the binary integer equivalent of the 14th byte of the NTLM token is equal to 130 when the browser is prompted. I will go with that for now.

Update 07/13/2009: It seems that Microsoft published the following which will make the ntlm_page_sentry function no longer work:


"Cumulative Security Update for Internet Explorer 7 for Windows Vista (KB963027)Security issues have been identified that could allow an attacker to compromise a system that is running Microsoft Internet Explorer and gain control over it. You can help protect your system by installing this update from Microsoft. After you install this item, you may have to restart your computer. This update is provided to you and licensed under the Windows Vista License Terms.

More information: http://go.microsoft.com/fwlink/?LinkId=146659

Help and Support: http://support.microsoft.com/"


One workaround is to de-install this update. I don't recommend that option. Another workaround listed in the comments below is to comment out the following check:


if utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,14,1) != 130


I don't recommend that option either. The purpose of the check above is to detect the case where the browser prompted for Username and Password. This will happen if someone visits your site using the ntlm_page_sentry function and your site is not listed as in the local intranet. If the above is commented out, users that visit your application where the browser thinks it is not the local intranet will be able to type in any username they want and be that user.I have spent some time trying to figure out a workaround but I don't have one. If any of you have any ideas, please post a comment.

Update 07/14/2009: A registry hack was provided at the following forum post which can be applied via group policy:

http://forums.oracle.com/forums/thread.jspa?forumID=137&threadID=921524

Update 08/14/2009: I also want to point out some text from the whitepaper based on this article to make it clear what this function does (decodes an NTLM token) and does not do (negotiate anything with any domain controller).

"This paper presents a pure PL/SQL code solution for decoding an NTLM token and using that decoded value as the authenticated user in APEX applications. The function will set the username to "nobody" if it detects that the browser prompted the user for their credentials instead of just silently negotiating them. You can then write authorization schemes that deny access to the "nobody" user. Note that unlike the mod_ntlm Apache module, this solution does not pass along credentials to a domain controller for authentication. This solution requests that the browser present an NTLM authentication token and decodes the username and domain from that token."

98 comments:

Dimitri Gielis said...

Hi Jason,

Welcome to the blogging sphere! Nice post.
I added you to apexblogs.info

Dimitri

Paulo Vale said...

Hello Jason,
Congratulations for your first blog post, the first of many, I hope :)

Paulo

Tony said...

Jason -

Nice post. This is a pretty big topic to bit off out of the box.

Your code is getting cut off. Can you post a link to the SQL?

onepanda said...

Jason,

I may have overlooked this, but does this method also require NTLM to be installed and configured on the server?

Thanks

Chris

Jason Straub said...

Chris:

No it should not. All the NTLM negotiation should happen in the PL/SQL page sentry code.

Regards,

Jason

Patrick Wolf said...

Hi Jason,

great work! Makes NTLM integration much easier. Probably I shouldn't show that to one of our SysAdmins who fought with mod_ntlm during the last few days and had big problems with compile errors. :-)

About the code:

It looks like that all the concatenation symbols (||) have been stripped out in the code you posted.

Looking through the code, I noticed that a new APEX session is created for each request during NTLM negotiation. Add a

wwv_flow.g_unrecoverable_error := TRUE;
RETURN FALSE;

after each

owa_util.http_header_close;

call to create just one for the final sucessful APEX redirect.

Great work and welcome in the APEX blogging community!

Patrick

Jason Straub said...

Great idea Patrick! I will add that soon. Also, I hooked up a SyntaxHighlighter that I saw Carl use. The code may still be hard to read on the page, but you can click View Plain or Copy to Cliboard for the code.

Jason

Patrick Wolf said...

BTW, is there any chance to grab the username/password if the user does a manual login with the dialog? Eg. this could be used to do a LDAP authentication.

Patrick

Jason Straub said...

Patrick:

That should be possible, but it is an exercise left for the reader ;)

Let me think about that some more.

Jason

Jason Straub said...

Patrick:

I did some testing and I can't figure out a way to grab the password that is supplied if the user is prompted with the dialog. I think one way to handle it would be to just redirect to a login page that would authenticate against the AD directory through LDAP.

Jason

Patrick Wolf said...

Too bad :-(

The problem with the redirect to a login page is that the user is prompted a 2nd time for username/password. Not very user friendly.

Thanks for looking into it
Patrick

Henner Ohrndorf said...

Hi Jason,

thanks for this great PL/SQL function for easy authentication via NTLM!
I have tested it in our environment, and first it did not work (it always returned "nobody" instead of the username).

Finally, I found that the string l_auth has only a length of 73 characters here, and after modifying the condition in the IF-clause it retrieved the Domain\Username from the string as expected.

Any idea why in our environment the string does have a length different from the one you found?

Jason Straub said...

Henner,

I do not know off hand, but it may be due to OS version? I was testing Windows XP and Windows Vista against Windows Server 2003.

What you will want to do is set up a client where you get prompted for username/password (Firefox without the NTLM setting). Then examine the length of l_auth when the user types in any old username/password combination. You will want to set the user to nobody when you detect (by examining the length of l_auth) that they were prompted for username/password.

There may be a better way to definitively detect this case, but I have not found it yet.

Tony said...

Pardon my ignorance, but is there a way to use NTLM authentication and actually log someone into the database so that select user from dual returns the OS User instead of the APEX_PUBLIC_USER?

Jason Straub said...

Tony:

You can authenticate to the Oracle database using Windows users. It is described in the following document:

http://download.oracle.com/docs/cd/B28359_01/win.111/b32010/authen.htm#i1007802

Jason

Ank, Molly y Chloe said...

hi Jason,

Thank you for sharing this. Any chance this could work with Oracle XE and APEX 3.1?

Thank you!
Anca

Jason Straub said...

Anca:

It would definatley work with Application Express 3.1, but could only work with XE if you use Oracle HTTP Server as the Web server. XE uses the EPG by default, and as I have mentioned, EPG doesn't support custom authentication.

Regards,

Jason

Ank, Molly y Chloe said...

Thank you very much for the quick reply. I will try to install Apex on our Oracle server so we can use this.
I've tried to run the script and got error here: " l_username := l_domain'\'l_user; ".
As Patrick mentioned looks like some || are missing..

Thanks again!
Anca

Jason Straub said...

Anca/Patrick:

I finally got around to fixing the || issue. I guess I missed it.

Thanks,

Jason

Ank, Molly y Chloe said...

Hi Jason,

Thanks for the updates. I managed to get our server ready with Oracle Enterprise Edition and when trying to create the function I get this error:

Error at line 128: PLS-00103: Encountered the symbol "APEX_APPLICATION" when expecting one of the following:

* & = - + ; < / > at in is mod remainder not rem
<> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ between || multiset member SUBMULTISET_

1. create or replace function ntlm_page_sentry
2. return boolean
3. is


I think some || might be still missing.. can you please check again?

Thanks a lot!
Anca

Jason Straub said...

Sorry Anca, when I added Patrick's fix, the || went away again. I have manually added them back in and tested the function to make sure it compiles. Thanks for reporting the issue.

Jason

Tony R said...

Jason,
Great paper. I have a question. We have two servers. One has dads.conf configuration file on Apache, for this server the Function is working. But the other one has marvel.conf, and when I try to login I got the following message:
"Enter any string to be used as your user identifier for this session"

Any ideas?

Thanks a lot
Tony R.

Rich J said...

I have the same issue as tony_r. It looks like the app is needing to populate APP_USER, but in my newbieness, I'm not yet able to determine exactly where.

I'm thinking that APP_USER would ideally already be populated with the domain username. Thoughts?

Rich

Wazza said...

Hi Jason,
Great article. Have tried to implement you solution without success. The login box appears with my domain\username appearing, but when I enter the password for the account it does not authenticate. The Authorization Required error appears after several attempts are made to authenticate.

Jason Straub said...

Wazza:

I have experienced a similar issue on an instance where I did not edit the DAD entry and added:

PlsqlCGIEnvironmentList AUTHORIZATION

Did you do that step?

Jason

Ank, Molly y Chloe said...

Hi Jason,

My previous attempts failed but today I've tried again and it's working perfectly, both on IE & Firefox.

Thank you very much for sharing!!!! :)

Anca

Unknown said...

HI Jason,
Great Post! I was working on incorporating your ntlm_page_sentry function into my application, but I'm using a 9i database that doesn't have the utl_encode.decode_text function. Do you have any idea what I could use in place of that function in order to make it work in 9i?

Thanks,
Mark

Jason Straub said...

Mark,

Unfortunately I don't have any good ideas except to locate the source of utl_encode in a later database release, and compile it on your 9i database.

Jason

Tony R said...

Hello,
I am using the page sentry function in HTTP 9i, and it is working fine; but when I try to use it in HTTP 10.2.0.1
the function always return nobody as user; and the login page is prompt. Any ideas?
Thanks a lot
Tony R.

Bhavesh said...
This comment has been removed by the author.
Bhavesh said...

Hi Jason,
I am trying to use ntlm with apex and using the greenit white paper for the same purpose. But somehow I seem to get apex_public_user instead of windows user name? I do not have dads.conf file but we have wdbsvr.app. Do you have any idea how to setup this file for ntlm and apex?

Thanks,
Bhavesh

Jason Straub said...

Bhavesh:

Check out: http://download.oracle.com/docs/cd/A97335_02/apps.102/a90099/feature.htm#1007231

I think you would just need to add

cgi_env_list=AUTHORIZATION

to your wdbsvr.app file, in the definition of the apex dad.

Jason

Jason Straub said...

Tony R:

I am not sure why it would work with HTTP server 9i and not 10.2. Did you add PlsqlCGIEnvironmentList AUTHORIZATION in dads.conf for the definition of the APEX dad?

Jason

Tony R said...

Thanks Jason,

Now it is working with http 9i and 10.2; but the first time that I call the application it shows the following message; and I need to refresh the page to get access. after that everything works fine.

"Authorization Required
This server could not verify that you are authorized to access the document requested. Either you supplied the wrong credentials (e.g., bad password), or your browser doesn't understand how to supply the credentials required.


--------------------------------------------------------------------------------

Oracle-Application-Server-10g/10.1.2.0.0 Oracle-HTTP-Server Server at hq-ecs03.cbi.intl Port 7781"


Thanks in advance

Christian Ropposch said...

first, thx for the interesting blog. i tried the function, but i have one problem: when i try to log in in apex i get always the oracle error-message "ORA-06503: PL/SQL: Function returned without value
Error ERR-1082 Error in executing authorization scheme code."

the problem is for sure in this part of the code:
if wwv_flow_custom_auth_std.is_session_valid then...
-> is_session_valid = true, but with return true in the case i get the above error-message. if i change the return to false i get no error-message?!? (just the login in the apex-application.)

has anyone an idea what could be the problem?

i use oracle 10.2 and apex 3.1.

thx and bye,
christian

Christian Ropposch said...

hi all together!

is it possible to redirect the user to a custom login-page, if the ntlm authentication fails? thx!

christian

Jason Straub said...

Christian:

Yes, that should be possible and is a good idea. First, you will want to set up a page that does LDAP authentication agains Active Directory. This is well documented on the forums. Let's say that page number is 101. Now, I would add the following code in the else block where the user is set to nobody:

else

l_username := 'nobody';
owa_util.redirect_url('f?p='||v('APP_ID')||':101:'||v('APP_SESSION'));

Regards,

Jason

Dean Attewell said...

Hi Jason

can you help me on this problem I am having...

http://forums.oracle.com/forums/thread.jspa?messageID=2741858&#2741858

Also, is your function correct I had to add some missing || to make it work?

Tx
Dean

Andrew James said...
This comment has been removed by the author.
Andrew James said...

Jason,

Excellent post! I used this function for a project I was recently working on.

I wanted to point out to you the following so you can update this post:

You assume the default charset for the database is set to WE8MSWIN1252! When I migrated to a new environment, the encoding was set to a different charset which threw off the conversion from raw->varchar2!

Using oracles convert function and the default charset, you can cast the varchar at runtime.

I posted a thread on OTN where you can pull the modified function from and update this entry.

Excellent work again and keep up the posts!

http://forums.oracle.com/forums/thread.jspa?messageID=2952957&#2952957

--
Andrew Barbaccia

Unknown said...

I don't understand where's the security? Since you don't authenticate against a domain controller in the end, I could write a piece of software that simulates the way internet explorer does the ntlm 3-way handshake and I would be authenticated. Where's the security in that?
How could I modify this, so that I pass the final credentials to be authenticated by a domain controller?

Ilmar's Blog said...

I confirm that this procedure does not work, if database is using multibyte charset. You need to convert the charset like Andrew suggested.

Also, in my case (XP SP3 + Win2003 SBS) browser returns a slightly different NTLM token, when NTLM fails and user is prompted the username and password. So I changed the code to:
l_auth NOT LIKE 'NTLM TlRMTVNTUAABAAAAB4II%AAAAAAAAAAAAAAAAAAA%='
Seems to work for now, bu I haven't done much testing with this.

Jason Straub said...

Ilmar:

I recently began noticing something similar. All of the sudden, using IE7 and Windows Vista, the token passed when prompted for a password changed to 'TlRMTVNTUAABAAAAB4IIogAAAAAAAAAAAAAAAAAAAAAGAHEXAAAADw=='. When I use FF, the token is the same as it was. Must have been an IE7 patch.

I did some more testing and I think I have found that the binary integer value of the 14th byte in the token is 130 when the user was promted, and something else when they were not prompted. I will update the function soon along with the changes suggested by Andrew Barbacci after I do some testing.

Regards,

Jason

Jason Straub said...

123.pls.spam.me:

The Holy Grail would to be able to communicate with the domain controller and verify the NTLM token passed all in PL/SQL. Below is my understanding of the list of steps that would take place in such a handshake with the domain controller.

1. Browser makes request through mod_plsql
2. Function responds with 401, and header WWW-Authenticate: NTLM
3. Browser makes request again, this time passing and NTLM token in the Authorization header
4. Function passes that token to a domain controller, domain controller responds with another token
5. Function sends 401 back to browser, set WWW-Authenticate header with token that domain controller provided
6. Browser makes request again, setting Authorization header with another NTLM token based on the token it was given from domain controller
7. Function takes this new token from browser, passes that to domain controller, domain controller responds with whether authentication passed and the domain\username of that person
8. Function authenticates user as user authenticated by domain controller

The parts that I don't know how to do in PL/SQL are numbers 4 and 7. Does anyone have an idea how this can happen (and perhaps provide a code example)?

The example posted here was meant as a PL/SQL only solution for tricking the browser into providing an NTLM token, and the decoding that token and using that value as the user in an Application Express application. It was meant to be used in some Intranet scenarios, and is obviously not meant to be a security solution.

Regards,

Jason

Bubba Blogs said...

Jason,
Great post mate! Easy to follow, worked immediately without any hassles.. Thanks again!

Ariel
http://www.web-backlinks.com/

Unknown said...

Hello!
I have a problem when using Firefox to access my applications. I'm able to move around an application (go from page to page) as long as I'm not doing a 'submit' function. I've seen posts that have a problem with ntlm and IE, but nothing related to Firefox. I followed all of the steps you listed, but I still get a "Method Not Implemented" error with a reference to "/pls/apex/wwv_flow.accept not supported" and "mod_plsql: request method not supported".

Do you have any idea what the problem could be? I've searched the forums and internet but found no solution!
Thank you!
Tara

Paul Randall said...

Jason, this has been a great solutions for us, thank you.

We did have one issue; the "p=" parameter had values missing from URLs the first time they were used. You would have to refresh the page or try a 2nd time to get the URL to work with any values appearing after the position that holds the SESSION ID (that is, everything after the 3rd colon in the "p=" param is lost).

Example:
http://localhost:7777/pls/apex/f?p=10:2:::NO::P2_COUNTRY:United%20States

To get a URL like this to work we added the following statement after the assignment to l_url on line 118, but before the IF-ELSE statement currently starting on line 123:

IF OWA_UTIL.get_cgi_env ('REQUEST_METHOD') = 'GET'
THEN
l_param_string := wwv_flow_utilities.url_decode2
(OWA_UTIL.get_cgi_env ('QUERY_STRING'));
IF (INSTR (l_param_string, 'p=', 1) = 1)
THEN
l_param_string :=
REGEXP_REPLACE (l_param_string, '^p=[^:]*:?[^:]*:?[^:]*(.*)', '\1');
l_url := l_url || l_param_string;
END IF;
END IF;

This has been working for us so far.

Paul

Jason Straub said...

Paul:

That is interesting, I am not sure why the URL was getting truncated in your environment. I have not experienced that.

Thanks for posting your solution. It may help others who experience the same thing.

Jason

Unknown said...

Tara:
I have the same problem when using Firefox. My Firefox version is 3.0.7.
Have you found any solution?
Thank you Bernhard

Unknown said...

Hi Bernhard! I soooo wish I had a solution to this problem but unfortunately I haven't received a response from any forum I posted to.

Jason, do you have any idea what the problem could be?

If by some miracle I find the solution I will post it!
Thank you

Jason Straub said...

Tara and Bernhard:

I will try to reproduce the problem and see if I can offer a solution.

Regards,

Jason

Unknown said...

Thank you, Jason!!

Jason Straub said...

Tara and Bernhard:

I was not able to reproduce using FF 3.0.7 on Windows Vista.

Do you have any issues when you try this with IE?

What operating system is the client running on?

Do you have any values listed in the Cookie Attributes of the NTLM authentication scheme?

Regards,

Jason

Unknown said...

Hi Jason!
We are using Windows XP and FF 3.0. We don't have any problems running the APEX applications in IE. We've tried using your pl/sql solution and the "modNtlmPageSentry" function from the greenIT website and neither will work in FF. I read this thread "http://forums.oracle.com/forums/thread.jspa?threadID=652805&start=15&tstart=0" and about 3/4 of the way down Patrick explained one of the problems he ran into (a wwv_flow.accept error when clicking a button which issues a submit), which is similar to what we're experiencing (but we get a "Method Not Implemented" error)...except he experienced it while using IE. I turned HTTP Live Headers on to see if I could troubleshoot from there but nothing jumps out at me (but then again I expected to see a big "ERROR" message). :) Would it help if I sent you our dads.conf file and/or httpd.conf file and/or the live header info? Thank you for looking into this...we're really stumped.

Ron M said...

Hi Jason,

I have followed your instructions. My environment is unix Oracle 9i. I managed to get a copy of the UTL_ENCODE package from a 10g database.

I arranged our DBA to replace the 9i package with the one from 10g but I now get “ORA-04063: has errors ORA-04063: package body "SYS.UTL_ENCODE" has errors ORA-06508: PL/SQL: could not find program unit being called”

Is there any other packages that had to be updated so it works with 9i?

Thanks

Ron

Jason Straub said...

Ron:

I looked at UTL_ENCODE dependencies and there does not seem to be any. I never personally tried installing the 10g version in a 9i database. Perhaps that suggestion was wrong. This solution may require 10g or higher.

Regards,

Jason

Jacco H. Landlust said...

Hi Jason,

I followed your howto and it works great, except that for some sort of reason the autorization cgi environment variable contains no data.

I found this by dumping all env vars to screen when l_auth is null instead of sending the 401 error using owa_util.print_cgi_env;

Do you have any pointers where to look for the cause of the absence of this data? (and yes, the machine is part of a domain ;) )

Cheers,

Jacco

Jason Straub said...

Jacco:

Are you sure you followed the step to modify that DAD that points to Application Express and add the following:

PlsqlCGIEnvironmentList AUTHORIZATION

Then you would need to stop and restart Apache.

Regards,

Jason

Jacco H. Landlust said...

Hi Jason,

I am not that much of an APEX programmer, but I do know some stuff about HTTP-servers and AS ;) Yes I am sure that I added the PlsqlCGIEnvironmentList. I also restarted the http-server (multiple times, also because of other configuration).

Next I tried the same exercise in a different domain (at an other customer, I am starting to get curious ;) ) and I noticed that the variable was still null. Might this have something to do with the user running the HTTP server?

Currently I am looking into a mod_ntlm solution, but this seems to contain a lot of coding in APEX.

Cheers,

Jacco

Ron M said...

Hi,

Thoses that have implemented this code on Oracle 9i. Can you advise how you got it to work?

The issue I have is around the UTL_ENCODE package.

Thanks

Ron

Unknown said...

Hi Jason,

I have followed the instructions and the NTLM function works well as long as I don't connect through an SSL connection.

When we connect to our application with http://triton.tue.nl:7778/pls/apex/f?p=124:1 the welcome page of the application is displayed and the username is displayed on top of the browser window by our application.

When we connect through SSL, with the URL https://triton.tue.nl/pls/apex/f?p=124:1 the browser throws in a Page Cannot be Displayed error.

Are any additional setup steps required to get the PL/SQL NTLM solution running through a SSL connection?

With best regards,

Willem van Leeuwen
Email: w.v.leeuwen@tue.nl

Unknown said...

I am get a not authorized and I have narrowed it to the following lines in the code:

if utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,14,1)) != 130 then
if utl_raw.cast_to_binary_integer(utl_raw.substr(l_raw,9,1)) = 1 then

the first binary int is 178 and the second is 1. What do these values represent? The function had previously work but is not now.

Any ideas?

Thanks

Mark

Rich J said...

Hi Jason,

We have rolled out a new MS security patch and it appears to have changed the NTLM token when using XP/IE6 and Vista/IE7. Oddly enough, FF3 still works, at least on XP (not tested on Vista).

Basically, after this security patch, byte 14 now has a value of 130 for us, so logins from IE6 are as "nobody". Commenting out that "if" on line 48 (and it's corresponding "else" block) is my workaround.

Putting some debugging in, I see that the new NTLM tokens are much different, having a length of 61 bytes -- 14 fewer than FF3 or pre-patched IE.

I thought you might like to know.

Enjoy!
Rich

Unknown said...

Hi Jason,

we use your NTLM Authentification function and it works - partially.

On most computers in our W2k3 Domain it doesn't work and nobody is displayed when starting an application which uses NTLM.

On some computers it works and DOMAIN\USERNAME is displayed.

When analyzing the NTLM String we found out that the flag is 130 when it doesn't work, and the flag is 178 (and 0 for the Type 3 Message).
Also in the 130-String [NOT-WORKING] no username or domain is included (In the 178-String [WORKING] it is).

Do you know what the 130 exactely means?
Do you have any idea what could be done or what is wrong with most computers?
Could this be Windows policy which prevents NTLM authentification?

Thank you very much!

djbckr said...

Just wanted to let you know that we are implementing this routine (with a couple of minor tweaks for our domain). We ran into a problem though when the UserAgent string coming from IE was too long (over 256 characters) - and thought maybe you would like to share it with people.

declare
...snip...
  vBrowserVersion long;
begin
...snip...
  declare
    eBuffTooSmall exception;
    pragma exception_init(eBuffTooSmall, -06502);
  begin
    vBrowserVersion := wwv_flow.get_browser_version;
  exception
    when eBuffTooSmall then
    vBrowserVersion := 'Must be IE';
  end;

  if vBrowserVersion = 'NSCP' then
...snip...
end;

Unknown said...

Hi Jason,

i found an issue in your Authentication routine!

When you try to call the application link with attached parameters (Something like "f?p=123:1:::::P1_ID:234") they disapper.

I changed the function and it works - Maybe you can update the function:
[...]
l_parameter VARCHAR2(1000);
[...]
-- New entry at function start
SELECT NVL2 (get_parameters_from_apex_url(owa_util.get_cgi_env('QUERY_STRING')), '::::' || get_parameters_from_apex_url(owa_util.get_cgi_env('QUERY_STRING')), '') INTO l_parameter FROM DUAL;
[...]
-- Change the following
if ( l_username = 'nobody' ) then
[...]
-- When doesn't work do custom authentication (Page 101)
htp.p(' location.href="' || 'f?p='||v('APP_ID')||':101:'||v('APP_SESSION') || l_parameter || '";');

[...]
-- and this too
l_url := 'f?p='||
apex_application.g_flow_id||':'||
nvl(apex_application.g_flow_step_id, 0)||':'||
apex_application.g_instance || l_parameter;

---
Of course you need the function get_parameters_from_apex_url:

function get_parameters_from_apex_url(complete_url VARCHAR2)
return VARCHAR2
as
x NUMBER;
begin

x := instr(complete_url, ':', 1, 6);

if x = 0 then
return null;
else
return substr(complete_url, x+1);
end if;
end;



DANIEL

Jason Straub said...

Daniel:

Thanks for the tip. That is a good improvement.

Regards,

Jason

SATHEESH KUMAR said...

Thanks for the tip. users are able to login using this authentication . But now we are facing one issue with IE7 , each new login session user need to press refresh or F5 for authentication. Otherwise IE7 shows information bar ""This content might not be displayed correctly. The file was restricted becuase the content doesn't match the security information....".

Sam Khalaf said...

Just a question, does the code showing in this page is the latest and greatest ? because I have seen many updates in the comments but am not sure if you updated the code with it.

Thanks much,
Mike A.

Jason Straub said...

Mike A.

Some of the early comments were incorporated, and those should be included in the comments in the function itself, or in the updates at the bottom of this post. Many of the comments here have been environment specific, so they have not been included. I encourage you to read all of the updates at the bottom of this post.

Regards,

Jason

Anonymous said...

Hi Jason,

in the Oracle Forum a user of your sentry found out that the behaviour of the OHS seems to have changed with 11gR2.
We haven't found out definetly what changed, but FF users get a 401 and have to refresh manually to get into the application, similar to what SATHEESH KUMAR commented here in January.
Probably the response-handler in the OHS starts sending before the header is actually submitted and the ff sees the 401 and stops, since you do the JS-refresh-hack only in IE.
I wondered if the 401 is necessary in FF at all, which seems not to be the case. At least skipping it for the NSCP makes the login work as intended.
See http://forums.oracle.com/forums/message.jspa?messageID=4540833#4540833

Regards,
Udo

Jason Straub said...

Udo:

Thanks for sharing your findings and helping the user on the forum!

Regards,

Jason

Unknown said...

Does this work for Apex 4.0.2

Jason Straub said...

gfraq:

I just tested it with a 4.0.2 instance from a Windows 7 client running IE8 and it worked perfectly.

Regards,

Jason

Patrick Wolf said...

Hi gfraq,

if you have installed 4.0.2, don't forget to apply the patch for bug# 10347091. See our <a href='http://www.oracle.com/technetwork/developer-tools/apex/downloads/apex402knownissues-189793.html">known issues list</a> for details.

Regards
Patrick

Rich J said...

Hi Jason,

Testing from Win7 using IE8 or Firefox 3.6 is blowing up the authentication with "ORA-06502: PL/SQL: numeric or value error: character string buffer too small". After debugging, the size of l_auth on line 09 is the issue. While it's fine for XP, our Win7 is returning a 661-byte string. I boosted the length of l_auth to 1024 and all's right again with the world.

Thought you might like to know. I looked through the other replies here and am a little curious that no one else has come across this (yet?).

Thanks!

Rich

Andreas said...

Hi Jason,

I am having difficulties getting this to work with Firefox.
I can see post from "Tara and Bernhard" who are experiencing exactly the same problem "Method Not Implemented" and "mod_plsql: request method not supported".
My platform is APEX 4.0.2.00.07 on Oracle 10gR2 (AIX) and Oracle HTTP Server 10.1.2 on Windows 2003. The NTLM module (mod_ntlm-1.3) is installed as described in "OracleApplicationExpressProofOfConceptNTLM.doc" from greenIT.
As I understand, solution is known to be working with IE and Firefox?

Any suggestions?
Could this be related to APEX 4 and Apache 1.3?

Help appreciated
/Andreas...

Tony said...

Does it matter what OS your database or http server is running on? For instance, if you are running the http server and database on linux, does this affect your ability to have SSO from a windows box.

Jason Straub said...

Andreas:

This solution *will not* work in combination with mod_ntlm as described in the whitepaper from greenIT. It was meant as a simple token decoding "trick" in PL/SQL. With releases of new browser versions, and operating systems, this "trick" is becoming less reliable. It does not actually do any negotiation with the domain controller so it should not be considered a secure solution.

Regards,

Jason

Jason Straub said...

Tony, the OS of the HTTP Sever or database server does not matter. The OS of the client and the browser version does matter. Check the comments listed here where people have posted their experiences with Windows 7, IE > 7 and various windows udpates.

Regards,

Jason

Andreas said...

Hi Jason,

Thanks a lot for your reply. It makes sense after reading the blog and papers again.
My Problem does persist, but in a different form.

I note: "this solution does not pass along credentials to a domain controller for authentication. This solution requests that the browser present an NTLM authentication token and decodes the username and domain from that token".

Printing all cgi variables I can see the AUTHORIZATION, but it is always empty - is that expected?

The variable is empty regardless of browser. IE does state "local instranet". Any way to verify that the browser represent a NTLM token?

Thanks again
/Andreas...

Jason Straub said...

Andreas:

Yes, I have confirmed in my environment that when I do a owa_util.print_cgi_env on the page, it does appear empty.

I believe I used ProxyTrace, http://www.pocketsoap.com/tcptrace/pt.aspx, to view the traffice between the browser and Application Express instance.

Regards,

Jason

Martin Köb said...

Hi Jason,

thanks a lot for your blog post.

I want now to implement NTLM Authentication via MOD_PLSQL.

What i did:
1. Added PlsqlCGIEnvironmentList AUTHORIZATION in the DADS File.
2. Restarted the Oracle HTTP-Server (Oracle Fusion Middleware Web Tier Utilities 11.1.1).
3. Added as first Statement vAuthorization := owa_util.get_cgi_env('AUTHORIZATION'); in the modNtlmPageSentry function.
4. Log the content of vAuthorization in my log table in the database.

What i can see is that the variable vAuthorization is allways NULL with IE and Firefox browser.

Do you have an ideaa what's wrong?
Thanks in advance

Best regards
Martin
greenIT Est.

Jon Trøstheim said...

Hi Jason

Have you tested NTLM on IE9?

Jeorg said...

Hello Jason,
I've choosen an architecture using APEX 4.1 with Oracle GlassFish and APEX-Listener. Now I want to use NTLM for authentication but I got stuck with the the first point: the DAD. I cannot find such a configuration file in APEX-Listener or GlassFish.
APEX is working via APEX Listener under GlassFish; the Embedded PL/SQL Gateway is not installed.
Can you give me an advice how to move on with GlassFish? Do I have to install other components like EPG?
Best regards, joerg.

Didier B said...

I used you ntlm code and it works fine in IE but I had a small bug in Google Chrome: at first attempt, I get a 401 error. and I found the solution here(apparently this occur also in FF).

the solution is to replace
-- See http://www.nabble.com/Empty-POST-requests-on-IE-td15332680.html
-- We have to trick IE that he thinks the authentication fails, otherwise
-- he doesn't send any data when issueing a POST because he wants to
-- do the NTLM stuff again
owa_util.status_line
( nstatus => 401,
creason => 'Unauthorized',
bclose_header => FALSE
);

by this:
IF WWV_Flow.get_browser_version != 'NSCP'
THEN
-- See http://www.nabble.com/Empty-POST-requests-on-IE-td15332680.html
-- We have to trick IE that he thinks the authentication fails, otherwise
-- he doesn't send any data when issueing a POST because he wants to
-- do the NTLM stuff again
owa_util.status_line
( nstatus => 401,
creason => 'Unauthorized',
bclose_header => FALSE
);
END IF;

Jason Straub said...

Didier:

Thanks for posting the workaround.

Regards,

Jason

Christian Ropposch said...

hello!

first thank you for the post, was really helpful.

anyone tested the authentication with apex 4.1.1? after upgrading our testsystem from 4.1 to 4.1.1 the function "apex_custom_auth.get_session_id_from_cookie" (used in ntlm_page_sentry) returns null for the session and so authentication is not possible. i also tried "apex_custom_auth.get_session_id_from_cookie", but this doesn't change the behaviour.

best regards,
christian

Jason Straub said...

Christian:

As you have probably already seen, Christian posted a possible work-around. I verified the fix myself, and it worked for me. I used F105 as my cookie name.

https://forums.oracle.com/forums/thread.jspa?threadID=2350625

Regards,

Jason

Mark said...

Hi Jason,
the workaround by setting the Cookie-Name is also working for me in APEX 4.1.1.

Are you working on a fix for this problem so we can use the ntlm_page_sentry function without a workaround?

Still I cant believe that there is no function from Oracle to use the windows-user in the APEX-application. Thank you for the good work with the ntlm_page_sentry function!

Kind Regards,
Mark

Vemp said...

Hi Jason and hi all!

I've been using this fine script for a long time and on over 350 computers and until today.

did anyone found a way to work around the 14th byte issue?
I know the managing the 14th byte isn't secure but it would be a workaround for the time being?

Ty for good function!

Unknown said...

I have a fresh version of Apex (v4.2) running using glassfish on linux. Ideally I would like to be able to grab the users "user id" from their browser session which is set using IWA/NTLM when they first log in to their machines. Whilst the solution posted here appears to work for the majority of users, it does not lend itself to explaining how one can set this up if traditional webservices are not underpinning apex anymore.
In my simple glassfish/apex environment, using a PLSQL REGION to dump CGI envs does not appear to bear any fruit about NTLM browser settings.


declare
begin
owa_util.print_cgi_env;
end;


Can this post be updated to guide people through this when using GF implementations?

Jason Straub said...

Hi Phil,

It should work as long as Glassfish will pass through the HTTP Header named Authorization. In Oracle HTTP Server, you modify the dads.conf file to ensure that header is passed. I am not sure there is a way to do the same for Glassfish.

Regards,

Jason

Mark said...

Hello,
the NTLM_PAGE_SENTRY-function is not working if the compatibility-mode for the IE9 is disabled. Per default the compatibility-mode is enabled for the local intranet.

Can you confirm that this is a problem in the NTML_PAGE_SENTRY-function?

Kind regards,
Mark

Jason Straub said...

Hi Mark,

I have not personally used or tested this function since about IE8. I don't know of any workaround needing to have IE9 compatibility mode set to enabled.

Regards,

Jason

Mark said...

Hi Jason,
it is very strange that nobody else has reported this problem. Maybe because the default setting for IE9 is "compatibility mode"="enabled" for the local intranet and the most user are using APEX in their local network.

With some external support we found a solution for us. In the last ELSE-section we had to add two lines.
Right after the ELSE-line we set the mime_header to
OWA_UTIL.mime_header('text/html', false, 'utf-8');
and before the line
meta http-equiv="Refresh" content="0; URL="
we activate the compatibility-mode:
meta http-equiv="X-UA-Compatible" content="IE=8"

I am not able to post the whole code because this is in this blog not possible.

Kind regards,
Mark

Unknown said...

Hi,
I'm on Apex 4.2.2 and Internet Explorer 9 on the client PC. After I've created the page sentry function and new authentication scheme as advised, when I login from the client PC, I see "nobody" in place of logged in user on the top right corner. Is there anything I need to change?
I've tried with and without the two modifications Mark suggested, but getting the same results. Any help is greatly appreciated.

Thanks,
Sam

Jason Straub said...

Hi Sam,

Unfortunately, I do not even have an environment anymore where I test this. It should be considered only as a proof of concept that my no longer work with newer APEX versions, browsers, and Windows OS. To do "real" Windows authentication one of the methods described in the following papers/posts should be followed:

http://de.slideshare.net/nielsdb/mt-ag-howtosingle-signonforapexapplicationsusingkerberos-46435415

https://community.oracle.com/thread/3637062

Regards,

Jason