Thursday, March 1, 2012

Windows NTLM SSO with Apex

I see this question being asked now and then:

Is it possible to set up my Oracle Application Express (Apex) application with Single Signon (SSO) based on Windows Integrated Security (NTLM), so that the end-users are automatically logged into the Apex application with their Windows (Active Directory) account without entering their username and password?

The answer is yes, but the implementation depends on your infrastructure, and specifically your web server:

  • If you are using Apache (Oracle HTTP Server), the Embedded PL/SQL Gateway (DBMS_EPG) or  the Apex Listener, then this post is not for you. There are various options such as mod_ntlm, mod_auth_sspi, proxies, etc., but you must look elsewhere for more details about those.
  • If you are using (or can use) Microsoft Internet Information Server (IIS) to serve your Apex applications, then read on.

How to set up Single Signon for Oracle Apex applications on Microsoft IIS

Here are the high-level steps you need to take:

  1. Install (enable) Microsoft IIS on your Windows Server
  2. Download and install the Thoth Gateway, a mod_plsql replacement for IIS
  3. Configure the virtual directory of the gateway application (typically the "pls" folder) to use Integrated Windows Authentication
  4. Create an authentication scheme in your Apex application that checks the HTTP header called "LOGON_USER" and make that the current scheme of your application
Let's look at the two last steps in some more detail.

Configure virtual directory to use Integrated Windows Authentication

After you have installed the Thoth Gateway (see the installation instructions provided with the download), you can use the IIS management console to change the directory security settings of the virtual folder (application) where the gateway is installed.

In the following screenshot the folder is called "web-integrated-security", which just shows that you can really call it anything, but in a default installation of the Thoth Gateway the folder will be called "pls" (which mimics the naming convention used by Apache and mod_plsql).




Simply check "Integrated Windows Authentication" to enable SSO. Also remember to remove anonymous access to the website!

Create Apex authentication scheme that checks LOGON_USER

IIS will now capture the username of the current user and pass it along to the Thoth Gateway as the CGI variable called "LOGON_USER".

You can access this value from PL/SQL using

owa_util.get_cgi_env('LOGON_USER')


To use this value in an authentication scheme in Apex, you can use the code provided by Joel Kallmann in this post where he provides a "custom authentication scheme, which can be used with (...) really any environment which will securely set a header variable to an authenticated username." Joel describes five high-level steps, of which you can ignore steps 3 and 4, as this is now handled by IIS and the Thoth Gateway. Also remember to reference "LOGON_USER" rather than "REMOTE_USER".

If you are using Apex 4.1 or later, then there is no need to create a custom authentication scheme, as there is a new built-in "HTTP Header Variable" authentication scheme:


Be sure to make this authentication scheme the current scheme of your application.

Verify that it works

If you are logged into the network with a Windows (Active Directory) account and use Internet Explorer to access your website (which should be in the browser's list of Trusted Sites -- it will typically already be trusted if the website is in the Intranet zone), you should not be asked for a username and password, and your Apex username (APP_USER) should be automatically set to "DOMAIN\USERNAME".

That was easy, wasn't it ?

4 comments:

Benjamin said...

Hello Morten,

I have configured this ntlm authentication with the thoth-gateway for apex 4.2 and 11g database.
It works really good.

But when I am trying to configure it for apex 4.1 and 10g database, I can't access the application with NTLM (but works with other authentication).

The log files:
The iis log:
192.168.16.67 - DOMAIN\username [20/Nov/2012:11:24:38 +0100] "GET /pls/apex/f?p=4000:RUN_PAGE:5824724424201990:BRANCH_TO_PAGE_ACCEPT:NO::FB_FLOW_ID,FB_FLOW_PAGE_ID,F4000_P1_FLOW:100,1,100 HTTP/1.1" 302 500
192.168.16.67 - DOMAIN\username [20/Nov/2012:11:24:38 +0100] "GET /pls/apex/f?p=100:1:5824724424201990::::: HTTP/1.1" 302 685
192.168.16.67 - DOMAIN\username [20/Nov/2012:11:24:38 +0100] "GET /pls/apex/f?p=100:1:2776707887505852::::: HTTP/1.1" 302 683
...etc (same line with several session id)


Do you have any idea to resolve this problem?

Benjamin

Benjamin said...

and the The thothgateway log:
2012-11-20 11:24:38,011 [6] INFO PLSQLGatewayModule.PLSQLHttpModule Request: GET /pls/apex/f
2012-11-20 11:24:38,011 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Parsed module = pls, dad = apex, proc = f
2012-11-20 11:24:38,011 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Parameter 1: p = 4000:RUN_PAGE:5824724424201990:BRANCH_TO_PAGE_ACCEPT:NO::FB_FLOW_ID,FB_FLOW_PAGE_ID,F4000_P1_FLOW:100,1,100
2012-11-20 11:24:38,011 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Cookies: ORA_WWV_REMEMBER_UN=USERNAME:WORKSPACE; ORA_WWV_USER_63415164076637=80EDA8E3CDBE8032FE8C7C1347FEA498; ORA_WWV_USER_63127273658882=3345BAC5E2AF6741; WWV_CUSTOM-F_1005905120909186_100=E1B1848A98582282; ORA_WWV_APP_103=434F7F9DAFB2FFF9E4506FAB4EB2D8A9
2012-11-20 11:24:38,011 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Current Windows user name (LOGON_USER) = DOMAIN\USERNAME
2012-11-20 11:24:38,011 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Connecting with user APEX_PUBLIC_USER to //192.168.16.174:1521/dngvm...
2012-11-20 11:24:38,011 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Connected to Oracle 10.2.0.4.0
2012-11-20 11:24:38,011 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Executing SQL: alter session set nls_language='AMERICAN' nls_territory='AMERICA'
2012-11-20 11:24:38,011 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Executing SQL: begin dbms_session.modify_package_state(dbms_session.reinitialize); end;
2012-11-20 11:24:38,011 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Executing SQL: declare dummy_num_vals integer; begin owa.ip_address(1) := :p_ip1; owa.ip_address(2) := :p_ip2; owa.ip_address(3) := :p_ip3; owa.ip_address(4) := :p_ip4; owa.hostname := :p_hostname; owa.set_user_id (:p_user_id); owa.set_password (:p_password); dummy_num_vals := owa.initialize; owa.init_cgi_env(:ecount, :namarr, :valarr); htp.init; htp.htbuf_len := 63; end;
2012-11-20 11:24:38,011 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Executing SQL: begin if wwv_flow_epg_include_modules.authorize (:p_proc_name) then f (p => :b1); if wpg_docload.is_file_download then :p_is_download := 1; else :p_is_download := 0; end if; else raise_application_error (-20000, 'Procedure call forbidden by request validation function (wwv_flow_epg_include_modules.authorize)'); end if; end;
2012-11-20 11:24:38,042 [6] INFO PLSQLGatewayModule.PLSQLHttpModule Gateway procedure executed successfully.
2012-11-20 11:24:38,042 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Committing database transaction...
2012-11-20 11:24:38,042 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Commit completed.
2012-11-20 11:24:38,042 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Fetch buffer size = 200
2012-11-20 11:24:38,042 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Executing SQL: begin owa.get_page(:linearr, :nlines); end;
2012-11-20 11:24:38,058 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Processing header X-ORACLE-IGNORE: IGNORE
2012-11-20 11:24:38,058 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Processing header Location: f?p=100:1:5824724424201990:::::
2012-11-20 11:24:38,058 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Closing database connection...
2012-11-20 11:24:38,058 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Database connection closed.
2012-11-20 11:24:38,058 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Compression of dynamic content is enabled, client accepts gzip, deflate
2012-11-20 11:24:38,058 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Converting relative path (f?p=100:1:5824724424201990:::::) to absolute path
2012-11-20 11:24:38,058 [6] DEBUG PLSQLGatewayModule.PLSQLHttpModule Redirecting to /pls/apex/f?p=100:1:5824724424201990:::::
...the same block

thanks,

Benjamin

Anonymous said...

Hi,
Thank you so much for your wonderful utility and I really appreciate you detail posting on how to use it.
I've these two questions since we are on Windows Server 2012 R2 64bit with IIS 8.

1) Are there any new setting/issues I should be aware of, since I see instructions for IIS6 and IIS7?

2) I already have Oracle Instant client 12.1 on this server. Can you please advise which ODP.NET I should download?

Thank you,
Sam

Morten Braten said...

@Sam:

1) The setup for IIS8 is the same as IIS7 (for setting up a folder with Integrated Windows Authentication), although the IIS admin console might look at bit different between versions.

2) If you are using the latest version of the Thoth Gateway (version 1.4.1), you don't even need the Oracle Instant Client, because the ODP.NET Managed Driver is included with the gateway (it's just a single DLL that you put in the same "bin" folder as the gateway). See http://ora-00001.blogspot.no/2014/11/thoth-gateway-140-beta-release-1-available.html

- Morten