This discussion is archived
1 2 3 Previous Next 37 Replies Latest reply: Feb 18, 2013 3:03 PM by 703377 Go to original post RSS
  • 15. Re: LDAP to Active Directory= 'invalid login credentials'
    Tom Petrus Expert
    Currently Being Moderated
    Rambo79 wrote:
    But when trying to run

    BEGIN
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl => 'ldapacl.xml',
    principal => 'APX',
    is_grant => TRUE,
    privilege => 'connect',
    position => null);
    COMMIT;
    END;
    /

    I am getting the following error when running this as sysdba

    Error at Line 1:
    ORA-44416: Invalid ACL: Unresolved principal 'APX'
    ORA-06512: at "SYS.DMBS_NETWORK_ACL_ADMIN", line 384
    ORA-06512: at line 2
    You should change the principal to the schema user of the schema you are using as parsing schema for your application, APX was just an example of mine.

    Now, please try to add your parsing schema user again
    BEGIN
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl => 'ldapacl.xml', 
    principal => 'PARSING_SCHEMA_USER',
    is_grant => TRUE, 
    privilege => 'connect',
    position => null);
    COMMIT;
    END;
    /
    Then run the dbms_bind code again, from the SQL workshop, with the parsing schema of your application. You probably are still having the error in the workshop since you did not actually add the parsing schema user, but rather tried to put user 'APX' in which does not exist.
    If that succeeds, then the authentication scheme in your app should work too, provided the settings are still the same as in your OP.
  • 16. Re: LDAP to Active Directory= 'invalid login credentials'
    Rambo79 Newbie
    Currently Being Moderated
    Hi

    The first part worked correctly

    However when running the following in SSL workshop I am still getting

    ORA-24247: network access denied by access control list (ACL)


    declare
    l_host varchar2(80) := 'localhost';
    l_port number := 389;
    l_user varchar2(80) := 'domain\myactivedirectoryusername';
    l_password varchar2(80) := 'myactivedirectorypassword';
    --
    l_session dbms_ldap.session;
    l_result pls_integer;
    begin
    dbms_ldap.use_exception := true;
    l_session := dbms_ldap.init(l_host, l_port);
    l_result := dbms_ldap.simple_bind_s (
    ld => l_session,
    dn => l_user,
    passwd => l_password );
    dbms_output.put_line('result='||l_result);
    l_result := dbms_ldap.unbind_s(l_session);
    end;
  • 17. Re: LDAP to Active Directory= 'invalid login credentials'
    742417 Newbie
    Currently Being Moderated
    When creating the access control list, the principal should be the schema of your Apex installation (APEX_040100).
  • 18. Re: LDAP to Active Directory= 'invalid login credentials'
    Tom Petrus Expert
    Currently Being Moderated
    Christoph wrote:
    When creating the access control list, the principal should be the schema of your Apex installation (APEX_040100).
    True, for the authentication scheme to work in your application. But if you want to run a plsql code block from say sqlplus or the workshop, you'll need to grant that schema user the rights aswell. So ideally, the ACL here should contain 2 users, APEX_040100 and the application parsing schema user. If running the PLSQL code then works, then the authentication should work too, but only when the parameters are the same, and indeed the ACL contains user APEX_040100.
    I'm not sure what exactly is going wrong here. Is it the 'resolve' right? I'm using a servername and no IP for example, with only connect rights, and it works fine. Does the ACL here grant connect to both users?
    When i execute
    select acl , principal , privilege , is_grant from DBA_NETWORK_ACL_PRIVILEGES;
    as system, i will see
    /sys/acls/ad_ldap.xml
    APEX_040100
    connect true
    
    /sys/acls/ad_ldap.xml
    APX
    connect true
    I can run the PLSQL block, connected as user APX on schema APX, and my authentication scheme works too. When there were/are troubles, i can just run the PLSQL block and check for errors. Solving those has so far always worked to 'fix' the ldap authentication.
  • 19. Re: LDAP to Active Directory= 'invalid login credentials'
    Rambo79 Newbie
    Currently Being Moderated
    Still no luck

    I have run the select as described by Tom and the 3 users are showing up as connect true - so I should have access to the AD server - but when running the script suggested for the SQL workshop I am getting ORA-24247: network access denied by access control list (ACL) which leads me to believe that although it is showing connect true it does not have access.

    I have browsed to the folder on the APEX server where the ldapacl.xml file is located and below is what it contains. The workspace of my APEX app is called RAMBO



    <a:acl description="LDAP Authentication for adservername.domain.co.uk" xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:plsql="http://xmlns.oracle.com/plsql" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd" shared="true">
    <a:security-class>plsql:network</a:security-class>
    <a:ace>
    <a:grant>true</a:grant>
    <a:principal>APEX_040100</a:principal>
    <a:privilege>
    <plsql:connect xmlns:plsql="http://xmlns.oracle.com/plsql"/>
    <plsql:resolve xmlns:plsql="http://xmlns.oracle.com/plsql"/>
    </a:privilege>
    </a:ace>
    <a:ace xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd">
    <a:grant>true</a:grant>
    <a:principal>SYSTEM</a:principal>
    <a:privilege>
    <plsql:connect xmlns:plsql="http://xmlns.oracle.com/plsql"/>
    </a:privilege>
    </a:ace>
    <a:ace xmlns:a="http://xmlns.oracle.com/xdb/acl.xsd">
    <a:grant>true</a:grant>
    <a:principal>RAMBO</a:principal>
    <a:privilege>
    <plsql:connect xmlns:plsql="http://xmlns.oracle.com/plsql"/>
    </a:privilege>
    </a:ace>
    </a:acl>
  • 20. Re: LDAP to Active Directory= 'invalid login credentials'
    Christian Neumueller Expert
    Currently Being Moderated
    Rambo,

    does networking work in general?
    select httpuritype('http://apex.oracle.com/i/index.html').getClob()
    from dual
    Regards,
    Christian
  • 21. Re: LDAP to Active Directory= 'invalid login credentials'
    Rambo79 Newbie
    Currently Being Moderated
    Hi

    I have just run the SQL from the workshop in APEX and am getting this error


    ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1819 ORA-24247: network access denied by access control list (ACL)
  • 22. Re: LDAP to Active Directory= 'invalid login credentials'
    Christian Neumueller Expert
    Currently Being Moderated
    Hi Rambo79,

    something must be missing in your acl setup. Can you compare your code with my test case?
    SYS@XE> select banner from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    CORE     11.2.0.2.0     Production
    TNS for Linux: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production
    
    SYS@XE> create user rambo79 identified by x;
    
    User created.
    
    SYS@XE> grant create session to rambo79;
    
    Grant succeeded.
    
    SYS@XE> conn rambo79/x
    Connected.
    
    RAMBO79@XE> select length(httpuritype('http://apex.oracle.com/i/index.html').getclob()) from dual;
    select length(httpuritype('http://apex.oracle.com/i/index.html').getclob()) from dual
                                                                            *
    ERROR at line 1:
    ORA-29273: HTTP request failed
    ORA-06512: at "SYS.UTL_HTTP", line 1819
    ORA-24247: network access denied by access control list (ACL)
    ORA-06512: at "SYS.HTTPURITYPE", line 34
    
    
    RAMBO79@XE> conn / as sysdba
    Connected.
    
    SYS@XE> begin
      2  dbms_network_acl_admin.create_acl('ldapacl.xml',null,'APEX_040200',true,'connect');
      3  dbms_network_acl_admin.add_privilege('ldapacl.xml','APEX_040200',true,'resolve');
      4  dbms_network_acl_admin.add_privilege('ldapacl.xml','RAMBO79',true,'connect');
      5  dbms_network_acl_admin.add_privilege('ldapacl.xml','RAMBO79',true,'resolve');
      6  dbms_network_acl_admin.assign_acl('ldapacl.xml','*');
      7  end;
      8  /
    
    PL/SQL procedure successfully completed.
    
    SYS@XE> conn rambo79/x
    Connected.
    
    RAMBO79@XE> select length(httpuritype('http://apex.oracle.com/i/index.html').getclob()) from dual;
    
    LENGTH(HTTPURITYPE('HTTP://APEX.ORACLE.COM/I/INDEX.HTML').GETCLOB())
    --------------------------------------------------------------------
                                                                   12896
    I used '*' to grant access to all hosts when calling dbms_network_acl_admin.assign_acl. You may want to restrict this to the LDAP server in your case.

    Regards,
    Christian

    Edited by: Christian Neumueller on Nov 16, 2012 3:01 AM
  • 23. Re: LDAP to Active Directory= 'invalid login credentials'
    Rambo79 Newbie
    Currently Being Moderated
    Hi Christian

    Thanks I followed you down ok until

    SYS@XE> begin
    2 dbms_network_acl_admin.create_acl('ldapacl.xml',null,'APEX_040200',true,'connect');
    3 dbms_network_acl_admin.add_privilege('ldapacl.xml','APEX_040200',true,'resolve');
    4 dbms_network_acl_admin.add_privilege('ldapacl.xml','RAMBO79',true,'connect');
    5 dbms_network_acl_admin.add_privilege('ldapacl.xml','RAMBO79',true,'resolve');
    6 dbms_network_acl_admin.assign_acl('ldapacl.xml','*');
    7 end;
    8 /

    Then I am getting the following error?

    Error at line 1;
    ORA-31003: Parent /sys/acls/ already contains child entry ldapac1.xml
    ORA-06512: at "SYS.DBMS._NETWORK_ACL_ADMIN", line 252
    ORA-06512: at line 2

    When running select * from dba_network_acls;

    I get back

    HOST
    ________________

    LOWER_PORT UPPER_PORT
    ________________

    ACL

    ACLID
    _________________
    adservername.domain.co.uk

    /sys/acls/ldapacl.xml
    35FBFF3171C246179C234FB32E471C80

    Edited by: Rambo79 on 20-Nov-2012 06:38
  • 24. Re: LDAP to Active Directory= 'invalid login credentials'
    Rambo79 Newbie
    Currently Being Moderated
    any ideas guys? or is it a limitation with Apex locally and MS Active Directory?

    Edited by: Rambo79 on 21-Nov-2012 06:46
  • 25. Re: LDAP to Active Directory= 'invalid login credentials'
    Christian Neumueller Expert
    Currently Being Moderated
    Hi Rambo79,
    $ oerr ora 31003
    31003, 00000, "Parent %s already contains child entry %s"
    // *Cause:   An attempt was made to insert a duplicate child into
    //           the XDB hierarchical resolver.
    // *Action:  Insert a unique name into the container.
    The ACL already exists. Either use another name or call
    begin dbms_network_acl_admin.drop_acl('ldapacl.xml'); end;
    before attempting to create it again...

    Regards,
    Christian
  • 26. Re: LDAP to Active Directory= 'invalid login credentials'
    Rambo79 Newbie
    Currently Being Moderated
    Created the ACL again set the correct parsing schema


    BEGIN
    DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl => 'ldapacl.xml',
    host => 'adservername.domain.co.uk',
    lower_port => 389,
    upper_port => 389);
    commit;
    end;
    /

    BEGIN
    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
    acl => 'ldapacl.xml',
    principal => 'MYPARSINGSCHEMA',
    is_grant => TRUE,
    privilege => 'Resolve',
    position => null);
    COMMIT;
    END;
    /

    But when using the LDAP test tool I am still getting

    Authentication failed!

    LDAP Host: adservername.domain.co.uk
    Port: 389
    No SSL
    Use exact DN: Yes
    DN String: cn=%LDAP_USER%,dc=domain,dc=co.uk

    And when trying to log into the actual APEX app the authentication does not work at all, if you just enter anything into the username field it lets you into the application?
  • 27. Re: LDAP to Active Directory= 'invalid login credentials'
    Christian Neumueller Expert
    Currently Being Moderated
    Did the dbms_ldap.simple_bind_s succeed now?

    Regards,
    Christian
  • 28. Re: LDAP to Active Directory= 'invalid login credentials'
    Rambo79 Newbie
    Currently Being Moderated
    Hi

    When running the following in SQL Workshop I am getting the error

    ORA-31202: DBMS_LDAP: LDAP client/server error: Invalid credentials. 80090308: LdapErr: DSID-0C090334, comment: AcceptSecurityContext error, data 525, vece

    declare
    l_host varchar2(80) := 'adname.domain.co.uk';
    l_port number := 389;
    l_user varchar2(80) := 'ou=it, ou=copy users, cn=Joe Bloggs, dc=mydomain, dc=co, dc=uk';
    l_password varchar2(80) := 'MyADPassword';
    --
    l_session dbms_ldap.session;
    l_result pls_integer;
    begin
    dbms_ldap.use_exception := true;
    l_session := dbms_ldap.init(l_host, l_port);
    l_result := dbms_ldap.simple_bind_s (
    ld => l_session,
    dn => l_user,
    passwd => l_password );
    dbms_output.put_line('result='||l_result);
    l_result := dbms_ldap.unbind_s(l_session);
    end;

    I have also tried
    l_user varchar2(80) := 'domain\ian123';




    Looking into AD it looks as though the details are stored, so I am not sure if I am calling the correct syntax above hence the reason for the error I am getting?


    Within AD distinguishedname is in the following structure
    CN=Joe Bloggs, OU=Copy Users, OU=IT, DC=mydomain, DC=co, DC=uk

    Its the sAMAccountName that contains our login credentials ian123 , CN name just contains the employees name

    There are other references of ian123 in the following fields in AD

    sAMAccountName = ian123
    Description = ian123
    UserPrincipalName = ian123@mydomain.co.uk

    Edited by: Rambo79 on 29-Nov-2012 03:04

    Edited by: Rambo79 on 29-Nov-2012 03:05
  • 29. Re: LDAP to Active Directory= 'invalid login credentials'
    Christian Neumueller Expert
    Currently Being Moderated
    Hi,

    order matters in the DN, just like in DNS names. You specify a path. Oracle.forums.com won't work either, if you want to access Oracle's forums. You could try
    declare
        l_host varchar2(80) := 'adname.domain.co.uk';
        l_port number := 389;
        l_user varchar2(80) := 'CN=Joe Bloggs, OU=Copy Users, OU=IT, DC=mydomain, DC=co, DC=uk';
        l_password varchar2(80) := 'MyADPassword';
        --
        l_session dbms_ldap.session;
        l_result pls_integer;
    begin
        dbms_ldap.use_exception := true;
        l_session := dbms_ldap.init(l_host, l_port);
        l_result := dbms_ldap.simple_bind_s (
            ld => l_session,
            dn => l_user,
            passwd => l_password );
        dbms_output.put_line('result='||l_result);
        l_result := dbms_ldap.unbind_s(l_session);
    end;
    Regards,
    Christian

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points