This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Mar 1, 2011 2:11 PM by 573669 RSS

Active Directory and LDAP

481447 Newbie
Currently Being Moderated
Hi,

I'm trying to build an application using an LDAP authentication with MS ActiveDirectory.

I cannot get the LDAP test tool getting it work.

On the windows command line I'm able to successfully get a result by querying the following command:<br><br>
ldapsearch -h 10.0.0.1 -p 389 -b "dc=company_name,dc=com" -D "cn=MyFullName,ou=USR,dc=company,dc=com" -w password (objectclass=*)
In the LDAP test utility I put the IP adress of the AD-Server (LDAP) into the field LDAP-Host, the port into the field Port.

Now I tried using cn=%LDAP_USER%,ou=USR,dc=company,dc=com for the DN-String and putting my fullname into the below username field using my password. But it only results in a timeout.

Actually I want to use the login (sAMAccountName) to login instead of the fullname.

Does anybody have a hint how to get it work?

I'm using 11g with APEX 3.1.

Thank you.
  • 1. Re: Active Directory and LDAP
    607350 Journeyer
    Currently Being Moderated
    This thread might have some answers for you:
    Re: LDAP and Active Directory
  • 2. Re: Active Directory and LDAP
    481447 Newbie
    Currently Being Moderated
    I tried a litte bit and came along a bit closer to a solution.

    I created an authentication scheme based on one from the repository (login page + LDAP) and wrote a function as follows:
    CREATE OR REPLACE FUNCTION username_password_check (
          p_username IN VARCHAR2
        , p_password IN VARCHAR2
    )
    RETURN BOOLEAN
    AS
        l_ldap_host     VARCHAR2(100) := 'MYSERVERIP';
        l_ldap_port     VARCHAR2(4)   := '389';
        l_session       DBMS_LDAP.SESSION;
        l_retval        PLS_INTEGER;
        l_login_result  BOOLEAN := true;
    
        l_error VARCHAR2(4000);
    BEGIN
    
        IF p_password IS NULL THEN
    
            l_login_result := FALSE;
        ELSE
    
            --
            -- I. LDAP-Login 
            --
            BEGIN
    
                -- LDAP Exceptions ausgeben
                DBMS_LDAP.USE_EXCEPTION := TRUE;
    
                -- LDAP-Handle initialisieren ...
                l_session := DBMS_LDAP.INIT(
                      hostname  => l_ldap_host
                    , portnum   => l_ldap_port
                );
    
                l_retval := DBMS_LDAP.SIMPLE_BIND_S(
                      ld        => l_session
                    , dn        => 'MYDOMAIN\' || LOWER(p_username)
                    , passwd    => p_password
                );
    
                -- Abmelden vom LDAP Server.
                l_retval := DBMS_LDAP.UNBIND_S(ld => l_session);
                -- Anmeldung war erfolgreich.
                l_login_result := TRUE;
    
            EXCEPTION WHEN OTHERS THEN
                l_login_result := FALSE;
            END;
        END IF;
    
        RETURN l_login_result;
    END;
    /
    Calling the function from SQL*Plus with the following code works fine (for the post I used dummy values for username and password):
    DECLARE
     l_res BOOLEAN;
    BEGIN
     l_res := USERNAME_PASSWORD_CHECK('myusername', 'mysecretpassword');
    
     IF l_res THEN
       DBMS_OUTPUT.PUT_LINE('Success!');
     ELSE
       DBMS_OUTPUT.PUT_LINE('NO Success');
     END IF;
       
    END;
    /
    When I use the function for authentication in APEX after clicking the login button nothing happens. Not even leaving the passwort. Just a plain white page is shown with http://IP:8080/apex/wwv_flow.accept in the title bar.

    Could anyone help me figuring out where the problem might be?

    Thanks

    UPDATE:
    I found out that the problem is the call to DBMS_LDAP.SIMPLE_BIND_S.

    What I dont understand is why it works from SQL*Plus but not in APEX.

    Message was edited by:
    hopser2000
  • 3. Re: Active Directory and LDAP
    60437 Employee ACE
    Currently Being Moderated
    Don't put any ldap attributes in the authentication scheme if you are using your own authentication function. Simply put:

    return username_password_check;

    ...in the authentication function field. Select your login page as the Invalid Session Page, provide a logout URL and leave all other attributes empty.

    Scott
  • 4. Re: Active Directory and LDAP
    481447 Newbie
    Currently Being Moderated
    Thats exactly what I did.

    The anonymous block was just for testing on the console. Up to now i remains workin on SQL*Plus but not in APEX. Something's wrong with the bind function in my case.

    Do I have to consider some APEX specific configuration in network settings or somethng like this, e.g like modifying the epg???flow function in the flows-scheme when calling a procedure over url?
  • 5. Re: Active Directory and LDAP
    60437 Employee ACE
    Currently Being Moderated
    I would like to know the value of each attribute in your authentication scheme.

    Do I have to consider some APEX specific configuration in network settings or somethng like this, e.g like modifying the epg???flow function in the flows-scheme when calling a procedure over url?

    What procedure over URL?

    Are you using EPG?

    Scott
  • 6. Re: Active Directory and LDAP
    481447 Newbie
    Currently Being Moderated
    Hi Scott,

    here the values of each value in my authentication scheme set with values (don't know the exact wording since I have a german translation):

    NAME: CUSTOM_SCHEMA

    SUBSCRIPTION: - (Checkbox Refresh is checked)

    SESSION NOT VALID (PAGE): 101

    AUTHENTICATION FUNCTION: RETURN username_password_check; (The above described function)

    LOGOUT URL: wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=&APP_ID.:1

    As mentioned all other fields are empty.

    What procedure over URL?

    In another project I used PLPDF to generate PDFs by clicking on a button. Therefore I had to modify the wwv_flow_epg_include_mod_local function in the FLOWS_XXXXX scheme to allow my print function to be called over a URL.

    Andreas
  • 7. Re: Active Directory and LDAP
    60437 Employee ACE
    Currently Being Moderated
    Andreas,

    Thanks for those details. Everything looks in order to me. There are no steps that I know of having to do with EPG setup to make this work. (Are you using EPG in this project?)

    When you say you think the problem is the call to dbms_ldap.simple_bind_s, why do you think that? Is it simply returning false or does it raise an exception? If the latter, what is the exception?

    Scott
  • 8. Re: Active Directory and LDAP
    481447 Newbie
    Currently Being Moderated
    Thanks Scott.

    When calling the dbms_ldap.simple_bind_s function the page is not redirected anymore.

    Today I found out that there is also a problem with using simple reports based on a query over db links.

    Is it possible that there is a configuration error with some Oracle network stuff? I'm using APEX as an out-of-the-box installation of 11g 64 bit on Windows 2003 Server.

    I remember that I read some stuff about newly introduced 11g network security and restrictions. Do you think that might have something to do with this matter?
  • 9. Re: Active Directory and LDAP
    60437 Employee ACE
    Currently Being Moderated
    When calling the dbms_ldap.simple_bind_s function the page is not redirected anymore.

    Okay, but does it return false or raise an exception or does it just hang? Try creating a dynamic PL/SQL region on a public page that runs the code in your function inline and then run the page in debug mode and trace the session by putting &p_trace=YES in the URL. Then find the trace file and see what you can find.

    (I don't know the answer about possible 11g networking restrictions. )

    Scott
  • 10. Re: Active Directory and LDAP
    481447 Newbie
    Currently Being Moderated
    After calling thr functions it just hangs - not return of false or an exception.

    I'll create a page with a dynamic pl/sql region and test what you suggested. Do you have a hint where the trace file is stored?
  • 11. Re: Active Directory and LDAP
    481447 Newbie
    Currently Being Moderated
    All right. I created a page with a PL/SQL region and put in my LDAP code as anonymous block as region source.
    Additionally I added the parameter to the url like this:

    http://IP:8080/apex/f?p=110:2:6863359756970396::YES&p_trace=YES

    When calling the URL the page is displayed immediately. Now I only have to find the trace file on the server where the database (with APEX) is installed.
  • 12. Re: Active Directory and LDAP
    60437 Employee ACE
    Currently Being Moderated
    So it renders without appearing to hang? Can you put some htp.p statements in there to give you some progress messages?

    Run this to locate trace file directory:

    select value from v$parameter where name='user_dump_dest';

    Scott
  • 13. Re: Active Directory and LDAP
    481447 Newbie
    Currently Being Moderated
    Scott,

    meanwhile I went back to 10gXE and tested my ldap procedure there. Averything works fine, even the pre-defined LDAP-authentication in APEX 3.1.

    Hope it will someday work in APEX on 11g.

    Andreas
  • 14. Re: Active Directory and LDAP
    Tyler Expert
    Currently Being Moderated
    I suspect it's an issue with the 11g Network ACL security. Try this from the user your application parses as:
     SELECT utl_inaddr.get_host_address('localhost') FROM dual; 
    If that fails with an ORA-24247, it's the Network ACL. Take a look at the doc on it here to allow that user to make network calls.
1 2 Previous Next