Link to home
Start Free TrialLog in
Avatar of Super222
Super222

asked on

LDAP Athentication Oracle Apex

Hi guys,

I have a serious problem with Apex LDAP Authentication.
I have surfed the internet to know sth about LDAP and now i think  my dn string should be like this:

cn=adminapex,cn=users,dc=apc,dc=info

But when i want to run the attached query to check if it's connect successfully to LDAP or not,I faced this error massage.

<b>
Error report:
ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials. 80090308: LdapErr: DSID-0C090334, comment: AcceptSecurityContext error, data 525, vece
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_LDAP", line 1455
ORA-06512: at "SYS.DBMS_LDAP", line 79
ORA-06512: at line 26
31202. 00000 -  "DBMS_LDAP: LDAP client/server error: %s"
*Cause:    There is a problem either on the LDAP server or on the client.
*Action:   Please report this error to the LDAP server administrator or
           your Database administrator.
</b>

I have searched this error but i think it's so general and i can't find a suitable answer for my case.

In additional i attached a print screen of my user properties in active directory. I should say that this user is created in countainer named Users under apc domain. Also active directory computer name is apc-dc and it's full name is apc-dc@apc.info.

As i know first cn in dn string should be First name + Last name. is it correct?

Should i use apc-dc@apc.info for parameter  l_ldap_host or computer name is correct?

How can i check my dn string to sure if it's correct or not via command prompt?

Is there a way to get dn string for a user directly from active directory?


Thanks in advance
Neda
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  -- Adjust as necessary.
  l_ldap_host    VARCHAR2(256) := 'apc-dc.apc.info';
  l_ldap_port    VARCHAR2(256) := '389';
  l_ldap_user    VARCHAR2(256) := 'cn=adminapex';
  l_ldap_passwd  VARCHAR2(256) := 'password';
  l_ldap_base    VARCHAR2(256) := 'cn=Users,dc=apc,dc=info';

  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) := '*'; -- retrieve all attributes 
  l_retval := DBMS_LDAP.search_s(ld       => l_session, 
                                 base     => l_ldap_base, 
                                 scope    => DBMS_LDAP.SCOPE_SUBTREE,
                                 filter   => 'objectclass=*',
                                 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);
        << values_loop >>
        FOR i IN l_vals.FIRST .. l_vals.LAST LOOP
          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);
  DBMS_OUTPUT.PUT_LINE('L_RETVAL: ' || l_retval);
END;

Open in new window

apex.JPG
ASKER CERTIFIED SOLUTION
Avatar of jwilleke
jwilleke
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Super222
Super222

ASKER

Thanks a lot  to your quick response Jim.
I tested it. It returns following string:

"CN=admin apex,CN=Users,DC=APC,DC=INFO"
I'v tested it with this string but it repeatedly shows the same error!!!

l_ldap_host    VARCHAR2(256) := 'apc-dc.apc.info';
  l_ldap_port    VARCHAR2(256) := '389';
  l_ldap_user    VARCHAR2(256) := 'CN=apex admin';
  l_ldap_passwd  VARCHAR2(256) := 'password';
  l_ldap_base    VARCHAR2(256) := 'CN=Users,DC=APC,DC=INFO';

Can it be a connection problem between active directory and apex server?

Any other idea?
SOLUTION
Avatar of jwilleke
jwilleke
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of gatorvip
gatorvip
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Super222
Super222

ASKER

Hi there,
Thanks a lot for your replies.

@Jim:
1-  I'm 100% sure that password is correct and i'v checked username from active directory server, it exists :) But could you explain me more about item 3. I describe more about my situation and please tell me if something is wrong.
We have an Active Directory server that have LDAP active on port 389. I don't know if its name is Active Directory LDAP server or not. Could you tell me What kind of server i need to test LDAP connection? Should i have different server for LDAP??!!

2- I changed a part of my query to

-- Adjust as necessary.
  l_ldap_host    VARCHAR2(256) := 'APC-DC.APC.INFO';
  l_ldap_port    VARCHAR2(256) := '389';
  l_ldap_user    VARCHAR2(256) := 'CN=admin apex,CN=Users,DC=APC,DC=INFO';
  l_ldap_passwd  VARCHAR2(256) := 'password';
  l_ldap_base    VARCHAR2(256) := 'CN=Users,DC=APC,DC=INFO';

and following error occurred:

  -- Disconnect from the LDAP server.
  l_retval := DBMS_LDAP.unbind_s(ld => l_session);
  DBMS_OUTPUT.PUT_LINE('L_RETVAL: ' || l_retval);
END;
Error report:
ORA-12703: this character set conversion is not supported
ORA-06512: at "SYS.DBMS_LDAP", line 1417
ORA-06512: at "SYS.DBMS_LDAP", line 579
ORA-06512: at line 55
12703. 00000 -  "this character set conversion is not supported"
*Cause:    The requested conversion between two character sets in the CONVERT
           function is not implemented
*Action:
---------------------------------------
ATTIBUTE_NAME: objectClass = top
ATTIBUTE_NAME: objectClass = container
ATTIBUTE_NAME: cn = Users
ATTIBUTE_NAME: description = Default container for upgraded user accounts
ATTIBUTE_NAME: distinguishedName = CN=Users,DC=APC,DC=INFO
ATTIBUTE_NAME: instanceType = 4
ATTIBUTE_NAME: whenCreated = 20100127130032.0Z
ATTIBUTE_NAME: whenChanged = 20100217130332.0Z
ATTIBUTE_NAME: uSNCreated = 4304
ATTIBUTE_NAME: uSNChanged = 1890751
ATTIBUTE_NAME: showInAdvancedViewOnly = FALSE
ATTIBUTE_NAME: name = Users
ATTIBUTE_NAME: objectGUID = ??W
I?;????
ATTIBUTE_NAME: systemFlags = -1946157056
ATTIBUTE_NAME: objectCategory = CN=Container,CN=Schema,CN=Configuration,DC=APC,DC=INFO
ATTIBUTE_NAME: isCriticalSystemObject = TRUE
ATTIBUTE_NAME: dSCorePropagationData = 20100127144311.0Z
ATTIBUTE_NAME: dSCorePropagationData = 16010101000001.0Z
---------------------------------------
ATTIBUTE_NAME: objectClass = top
ATTIBUTE_NAME: objectClass = person
ATTIBUTE_NAME: objectClass = organizationalPerson
ATTIBUTE_NAME: objectClass = user
ATTIBUTE_NAME: cn = Administrator
ATTIBUTE_NAME: description = Built-in account for administering the computer/domain
ATTIBUTE_NAME: distinguishedName = CN=Administrator,CN=Users,DC=APC,DC=INFO
ATTIBUTE_NAME: instanceType = 4
ATTIBUTE_NAME: whenCreated = 20100127130034.0Z
ATTIBUTE_NAME: whenChanged = 20100127164930.0Z
ATTIBUTE_NAME: displayName = Administrator
ATTIBUTE_NAME: uSNCreated = 8194
ATTIBUTE_NAME: memberOf = CN=Group Policy Creator Owners,CN=Users,DC=APC,DC=INFO
ATTIBUTE_NAME: memberOf = CN=Domain Admins,CN=Users,DC=APC,DC=INFO
ATTIBUTE_NAME: memberOf = CN=Enterprise Admins,CN=Users,DC=APC,DC=INFO
ATTIBUTE_NAME: memberOf = CN=Schema Admins,CN=Users,DC=APC,DC=INFO
ATTIBUTE_NAME: memberOf = CN=Administrators,CN=Builtin,DC=APC,DC=INFO
ATTIBUTE_NAME: uSNChanged = 28424
ATTIBUTE_NAME: homeMTA = CN=Microsoft MTA,CN=APC-DC,CN=Servers,CN=First Administrative Group,CN=Administrative Groups,CN=First Organization,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=APC,DC=INFO
ATTIBUTE_NAME: proxyAddresses = SMTP:Administrator@APC.INFO
ATTIBUTE_NAME: proxyAddresses = X400:c=US;a= ;p=First Organizati;o=Exchange;s=Administrator;
ATTIBUTE_NAME: proxyAddresses = smtp:postmaster@APC.INFO
ATTIBUTE_NAME: homeMDB = CN=Mailbox Store (APC-DC),CN=First Storage Group,CN=InformationStore,CN=APC-DC,CN=Servers,CN=First Administrative Group,CN=Administrative Groups,CN=First Organization,CN=Microsoft Exchange,CN=Services
ATTIBUTE_NAME: mDBUseDefaults = TRUE
ATTIBUTE_NAME: mailNickname = Administrator
ATTIBUTE_NAME: name = Administrator
ATTIBUTE_NAME: objectGUID = :/??
ATTIBUTE_NAME: userAccountControl = 66048
ATTIBUTE_NAME: badPwdCount = 0
ATTIBUTE_NAME: codePage = 0
ATTIBUTE_NAME: countryCode = 0
ATTIBUTE_NAME: badPasswordTime = 129156061788750000
ATTIBUTE_NAME: lastLogoff = 0
ATTIBUTE_NAME: lastLogon = 129156064213125000
ATTIBUTE_NAME: pwdLastSet = 129090764059843750
ATTIBUTE_NAME: primaryGroupID = 513
ATTIBUTE_NAME: objectSid = 
ATTIBUTE_NAME: adminCount = 1
ATTIBUTE_NAME: accountExpires = 9223372036854775807
ATTIBUTE_NAME: logonCount = 447
ATTIBUTE_NAME: sAMAccountName = Administrator
ATTIBUTE_NAME: sAMAccountType = 805306368
ATTIBUTE_NAME: showInAddressBook = CN=Default Global Address List,CN=All Global Address Lists,CN=Address Lists Container,CN=First Organization,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=APC,DC=INFO
ATTIBUTE_NAME: showInAddressBook = CN=All Users,CN=All Address Lists,CN=Address Lists Container,CN=First Organization,CN=Microsoft Exchange,CN=Services,CN=Configuration,DC=APC,DC=INFO
ATTIBUTE_NAME: legacyExchangeDN = /o=First Organization/ou=First Administrative Group/cn=Recipients/cn=Administrator
ATTIBUTE_NAME: objectCategory = CN=Person,CN=Schema,CN=Configuration,DC=APC,DC=INFO
ATTIBUTE_NAME: isCriticalSystemObject = TRUE
ATTIBUTE_NAME: textEncodedORAddress = c=US;a= ;p=First Organizati;o=Exchange;s=Administrator;
ATTIBUTE_NAME: mail = Administrator@APC.INFO
ATTIBUTE_NAME: msExchHomeServerName = /o=First Organization/ou=First Administrative Group/cn=Configuration/cn=Servers/cn=APC-DC
ATTIBUTE_NAME: msExchALObjectVersion = 51
ATTIBUTE_NAME: msExchMailboxSecurityDescriptor = 
ATTIBUTE_NAME: msExchUserAccountControl = 0
ATTIBUTE_NAME: msExchMailboxGuid = W??????K?
ATTIBUTE_NAME: msExchPoliciesIncluded = {9615BD0D-1C7F-4EED-906A-C586A5E3398D},{26491CFC-9E50-4857-861B-0CB8DF22B5D7}
---------------------------------------
ATTIBUTE_NAME: objectClass = top
ATTIBUTE_NAME: objectClass = person
ATTIBUTE_NAME: objectClass = organizationalPerson
ATTIBUTE_NAME: objectClass = user
ATTIBUTE_NAME: cn = Guest
ATTIBUTE_NAME: description = Built-in account for guest access to the computer/domain
ATTIBUTE_NAME: distinguishedName = CN=Guest,CN=Users,DC=APC,DC=INFO
ATTIBUTE_NAME: instanceType = 4
ATTIBUTE_NAME: whenCreated = 20100127130034.0Z
ATTIBUTE_NAME: whenChanged = 20100127130034.0Z
ATTIBUTE_NAME: uSNCreated = 8195
ATTIBUTE_NAME: memberOf = CN=Guests,CN=Builtin,DC=APC,DC=INFO
ATTIBUTE_NAME: uSNChanged = 8195
ATTIBUTE_NAME: name = Guest

Finally it seems that something happend!!! do you know this error? Do you have any idea about testing the LDAP port. I'v checked it from DNS server, It's 389. However may be it's a connection problem!!

@gatorvip:
Thanks for your suggestion. I know this tool and i'v worked with it but this tool have some problem in my mind. when you use this tool and your dn string is not correct or something else is wrong the only error message that you get is "Authentication failed". In fact you can't find what is wrong?

SOLUTION
Avatar of jwilleke
jwilleke
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Super222
Super222

ASKER

Hi Jim,

I 'v tried to test connectivity between LDAP and Active Directory via ldp.exe but it returns this error:

Result <1>: 00000000: LdapErr: DSID-0C090627,
comment: In order to perform this operation a successful bind must be completed on the connection.,
data 0, vece
Matched DNs:
Getting 0 entries:

Does it mean that LDAP couldn't bind on AD server?
SOLUTION
Avatar of jwilleke
jwilleke
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Super222
Super222

ASKER

Hi Jim,

I really appreciate your help ..
I guessed that DBMS_LDAP package have a problem therefor i reloaded it and the error has changed!!

Error report:
ORA-31203: DBMS_LDAP: PL/SQL - Init Failed.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_LDAP", line 50
ORA-06512: at line 23
31203. 00000 -  "DBMS_LDAP: PL/SQL - Init Failed."
*Cause:    There has been an error in the DBMS_LDAP Init operation.
*Action:   Please check the host name and port number, or report
           the error number and description to Oracle Support.

I'v searched this error. It seems that parameter l_ldap_host return null ..
Could you tell me how can i fix it?
SOLUTION
Avatar of jwilleke
jwilleke
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Super222
Super222

ASKER

Ok Jim. I'll download it. Should i install this software on my oracle database server and check the connectvity or I have to install it just on AD server?

Thanks a lot
SOLUTION
Avatar of jwilleke
jwilleke
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of gatorvip
gatorvip
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Super222
Super222

ASKER

Hi Jim,

Finally it works!!!!! The problem is that LDAP authentication in apex use Full name to sign in instead of username !!!!! It's the most silliest that i'v ever seen :/ I have to customize it myself.
But i am going to award you because I'v learned so much from you :)

Thanks a lot and Good Luck