7 Replies Latest reply: Dec 28, 2007 2:49 AM by 512680 RSS

    LDAP error SELECT distinguishedName FROM 'LDAP://dc=mydomain,dc=com

    595516
      I am using the following code to authenticate to the active directory and its works perfectly

      declare
      l_retval pls_integer;
      l_retval2 pls_integer;
      l_session dbms_ldap.session;
      l_ldap_host varchar2(256);
      l_ldap_port varchar2(256);
      l_ldap_user varchar2(256) := 'firstname lastname-IT';
      l_ldap_passwd varchar2(256) := 'password';
      l_ldap_base varchar2(256);
      begin
      l_retval := -1;
      dbms_ldap.use_exception := TRUE;
      l_ldap_host := 'mydomain.com';
      l_ldap_port := '389';
      l_ldap_user := 'cn='||l_ldap_user||',cn=Users,dc=nydomain,dc=com';

      l_session := dbms_ldap.init( l_ldap_host, l_ldap_port );
      l_retval := dbms_ldap.simple_bind_s( l_session, l_ldap_user, l_ldap_passwd );
      dbms_output.put_line( 'Return value: ' || l_retval );
      l_retval2 := dbms_ldap.unbind_s( l_session );

      exception when others
           then
                dbms_output.put_line (rpad('ldap session ',25,' ') || ': ' ||
                     rawtohex(substr(l_session,1,8)) || '(returned from init)');
                dbms_output.put_line( 'error: ' || sqlerrm||' '||sqlcode );
                dbms_output.put_line( 'user: ' || l_ldap_user );
                dbms_output.put_line( 'host: ' || l_ldap_host );
                dbms_output.put_line( 'port: ' || l_ldap_port );
                l_retval := dbms_ldap.unbind_s( l_session );
      end;



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

      but my problem is instead of giving username as firstname and lastname for authenication i want to use the nick name for username like amitcho
      instead of amit kumar-IT......

      is there any code anyway to solve this issue ?
        • 1. Re: LDAP error SELECT distinguishedName FROM 'LDAP://dc=mydomain,dc=com
          135285
          Neon,

          instead of using a full DN search string, for MS Active directory you can also use the format.

          l_ldap_user := 'your-windows-login-domain\'||l_ldap_user;

          In that case l_ldap_user would be your username for the Windows login.

          BTW, you can also use the built-in LDAP authentication scheme, just enter

          your-windows-login-domain\%LDAP_USER%

          or

          %LDAP_USER% (in case if the user should also enter the domain)

          into the "LDAP DN String" property.

          Regards
          Patrick
          ----------------------------------------------------------------------------------------------------
          My APEX Blog: http://inside-apex.blogspot.com
          The ApexLib Framework: http://apexlib.sourceforge.net
          The APEX Builder Plugin: http://apexplugin.sourceforge.net/ New!
          • 2. Re: LDAP error SELECT distinguishedName FROM 'LDAP://dc=mydomain,dc=com
            595516
            I try with both ways but still it does not works ?
            When i give full domain name\nickname it gives error authenication fails. ?
            • 3. Re: LDAP error SELECT distinguishedName FROM 'LDAP://dc=mydomain,dc=com
              595516
              I searched in whole forum but no answer to my query
              • 4. Re: LDAP error SELECT distinguishedName FROM 'LDAP://dc=mydomain,dc=com
                595516
                any suggestions from apex guru's
                • 5. Re: LDAP error SELECT distinguishedName FROM 'LDAP://dc=mydomain,dc=com
                  512680
                  Hi Neon,

                  For that, I make a function which return DN with nickname for input parameter.
                  This function requires a valid account in the LDAP which can make a request to
                  the LDAP server to find the distinguishedName for a samAccountName(nickname)
                  (see l_ldap_user and l_ldap_password : I have make a request to the LDAP Administrator in order to create a valid LDAP account only for this function)

                  The function is like this :
                  CREATE OR REPLACE FUNCTION RETRIEVE_DN(p_username in varchar2) return varchar2 is
                  begin
                  DECLARE
                    l_ldap_host    VARCHAR2(256) := 'Your LDAP Server';
                    l_ldap_port    VARCHAR2(256) := '389';
                    l_ldap_user    VARCHAR2(256) := 'cn=********,cn=Users,dc=hopital,dc=net';
                    l_ldap_passwd  VARCHAR2(256) := '*******';
                    l_ldap_base    VARCHAR2(256) := 'cn=Users,dc=hopital,dc=net';
                     l_retval       PLS_INTEGER;
                    l_session      DBMS_LDAP.session;
                    l_attrs        DBMS_LDAP.string_collection;
                    l_message      DBMS_LDAP.message;
                    l_entry        DBMS_LDAP.message;
                    l_attr_name    VARCHAR2(256);
                    l_ber_element  DBMS_LDAP.ber_element;
                    l_vals         DBMS_LDAP.string_collection;
                    l_res          VARCHAR2(256);
                  BEGIN
                    -- Choose to raise exceptions.
                    DBMS_LDAP.USE_EXCEPTION := TRUE;
                    -- Connect to the LDAP server.
                    l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                                                portnum  => l_ldap_port);
                    l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                                        dn     => l_ldap_user,
                                                        passwd => l_ldap_passwd);
                    -- Get all attributes
                    l_attrs(1) := 'distinguishedName'; -- retrieve  attribute : distinguishedName
                    l_retval := DBMS_LDAP.search_s(ld       => l_session,
                                                   base     => l_ldap_base,
                                                   scope    => DBMS_LDAP.SCOPE_SUBTREE,
                                                   filter   => 'sAMAccountName='||upper(p_username),
                                                   attrs    => l_attrs,
                                                   attronly => 0,
                                                   res      => l_message);
                    IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
                      -- Get all the entries returned by our search.
                      l_entry := DBMS_LDAP.first_entry(ld  => l_session,
                                                       msg => l_message);
                      << entry_loop >>
                      WHILE l_entry IS NOT NULL LOOP
                        -- Get all the attributes for this entry.
                        DBMS_OUTPUT.PUT_LINE('-------------------------------------');
                        l_attr_name := DBMS_LDAP.first_attribute(ld        => l_session,
                                                                 ldapentry => l_entry,
                                                                 ber_elem  => l_ber_element);
                        << attributes_loop >>
                        WHILE l_attr_name IS NOT NULL LOOP
                          -- Get all the values for this attribute.
                          l_vals := DBMS_LDAP.get_values (ld        => l_session,
                                                          ldapentry => l_entry,
                                                          attr      => l_attr_name);
                          FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
                           -- DBMS_OUTPUT.PUT_LINE('ATTRIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
                                l_res:= SUBSTR(l_vals(i),1,256);
                          END LOOP values_loop;
                          l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
                                                                  ldapentry => l_entry,
                                                                  ber_elem  => l_ber_element);
                        END LOOP attibutes_loop;
                        l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                                        msg => l_entry);
                      END LOOP entry_loop;
                     END IF;
                    -- Disconnect from the LDAP server.
                    l_retval := DBMS_LDAP.unbind_s(ld => l_session);
                    return l_res;
                  END;
                  END;
                  On the authentication scheme, for LDAP DN String , I put %LDAP_USER%
                  and for LDAP USER Name Edit function, I put
                  return RETRIEVE_DN;

                  You have to make some changes for your LDAP environment and some improvements (no loop needed for one attribute...). For debug, I use sometimes l_attrs(1)='*' for watching the different attributes on the LDAP (and not only distinguishedName).

                  And for security, you have to wrap the function....


                  Hope this helps

                  Mike
                  • 6. Re: LDAP error SELECT distinguishedName FROM 'LDAP://dc=mydomain,dc=com
                    595516
                    thanks thanks mickyway it works ...

                    Now How I can fetch other records of the user like name and e-mail address etc
                    of the user. once he is authenticated user.
                    • 7. Re: LDAP error SELECT distinguishedName FROM 'LDAP://dc=mydomain,dc=com
                      512680
                      Hi Neon,

                      You can select other attributes of the LDAP.
                      I have a procedure for that , this procedure returns infos like name, first name,
                      mail, department, description and company.
                      CREATE OR REPLACE PROCEDURE RETURN_INFO_LDAP(p_username in varchar2,p_nom out varchar2,p_prenom out varchar2,p_mail out varchar2,p_department out varchar2,p_description out varchar2,p_company out varchar2)
                      is
                      begin
                      DECLARE
                        l_ldap_host    VARCHAR2(256) := 'Your LDAP Server';
                        l_ldap_port    VARCHAR2(256) := '389';
                        l_ldap_user    VARCHAR2(256) := 'cn=XXXXXXXXXXX,cn=Users,dc=hopital,dc=net';
                        l_ldap_passwd  VARCHAR2(256) := 'XXXXXXXXX';
                        l_ldap_base   VARCHAR2(256) := 'cn=Users,dc=hopital,dc=net';
                        l_retval       PLS_INTEGER;
                        l_session      DBMS_LDAP.session;
                        l_attrs        DBMS_LDAP.string_collection;
                        l_message      DBMS_LDAP.message;
                        l_entry        DBMS_LDAP.message;
                        l_attr_name    VARCHAR2(256);
                        l_ber_element  DBMS_LDAP.ber_element;
                        l_vals         DBMS_LDAP.string_collection;
                      BEGIN
                        -- Choose to raise exceptions.
                        DBMS_LDAP.USE_EXCEPTION := TRUE;
                        -- Connect to the LDAP server.
                        l_session := DBMS_LDAP.init(hostname => l_ldap_host,
                                                    portnum  => l_ldap_port);
                        l_retval := DBMS_LDAP.simple_bind_s(ld     => l_session,
                                                            dn     => l_ldap_user,
                                                            passwd => l_ldap_passwd);
                        -- Get all attributes
                        l_attrs(1) := 'sn'; -- retrieve all attributes
                        l_attrs(2) := 'givenName';
                        l_attrs(3) := 'mail';
                        l_attrs(4) := 'department';
                        l_attrs(5) := 'description';
                        l_attrs(6) := 'company';
                        l_retval := DBMS_LDAP.search_s(ld       => l_session,
                                                       base     => l_ldap_base,
                                                       scope    => DBMS_LDAP.SCOPE_SUBTREE,
                                                       filter   => 'sAMAccountName='||upper(p_username),
                                                       attrs    => l_attrs,
                                                       attronly => 0,
                                                       res      => l_message);
                        IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
                          -- Get all the entries returned by our search.
                          l_entry := DBMS_LDAP.first_entry(ld  => l_session,
                                                           msg => l_message);
                          << entry_loop >>
                          WHILE l_entry IS NOT NULL LOOP
                            -- Get all the attributes for this entry.
                            DBMS_OUTPUT.PUT_LINE('-------------------------------------');
                            l_attr_name := DBMS_LDAP.first_attribute(ld        => l_session,
                                                                     ldapentry => l_entry,
                                                                     ber_elem  => l_ber_element);
                            << attributes_loop >>
                            WHILE l_attr_name IS NOT NULL LOOP
                              -- Get all the values for this attribute.
                              l_vals := DBMS_LDAP.get_values (ld        => l_session,
                                                              ldapentry => l_entry,
                                                              attr      => l_attr_name);
                              FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
                                  case l_attr_name
                                  when 'sn' then p_nom := SUBSTR(l_vals(i),1,200);
                                  when 'givenName' then p_prenom := SUBSTR(l_vals(i),1,200);
                                  when 'mail' then p_mail := SUBSTR(l_vals(i),1,200);
                                  when 'department' then p_department := SUBSTR(l_vals(i),1,200);
                                  when 'description' then p_description := SUBSTR(l_vals(i),1,200);
                                  when 'company' then p_company := SUBSTR(l_vals(i),1,200);
                                  end case;
                               -- DBMS_OUTPUT.PUT_LINE('ATTIBUTE_NAME: ' || l_attr_name || ' = ' || SUBSTR(l_vals(i),1,200));
                              END LOOP values_loop;
                              l_attr_name := DBMS_LDAP.next_attribute(ld        => l_session,
                                                                      ldapentry => l_entry,
                                                                      ber_elem  => l_ber_element);
                            END LOOP attibutes_loop;
                            l_entry := DBMS_LDAP.next_entry(ld  => l_session,
                                                            msg => l_entry);
                          END LOOP entry_loop;
                          END IF;
                        -- Disconnect from the LDAP server.
                        l_retval := DBMS_LDAP.unbind_s(ld => l_session);
                      END;
                      end;
                      /
                      As you can see, it is similar to the function RETRIEVE_DN .
                      The important point is the variable l_attrs where you define the different attributes to fetch( here 6 attributes) If you want fetch other attributes than this procedure, you can use l_attrs(1)='*' and use dbms_output to display the variable l_attr_name
                      and its value l_vals(i) in order to find the exact name of the LDAP attribute.
                      And you should see all the LDAP attributes of an account.
                      Of course you have to make some changes for your environment.


                      You can create an application process On New Session: After authentication
                      which calls the procedure with application items , like this
                      return_info_ldap(:APP_USER,:NAME,:FNAME,:MAIL,:DEPARTMENT,:DESCRIPTION,:COMPANY);
                      where NAME, FNAME,MAIL ,DEPARTMENT, DESCRIPTION and COMPANY are application items.



                      Mike