• 15. Re: LDAP to Active Directory= 'invalid login credentials'
      Tom Petrus
      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.
      1 пользователь считает эту информацию полезной
      • 16. Re: LDAP to Active Directory= 'invalid login credentials'
        Rambo79
        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
          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
            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.
            1 пользователь считает эту информацию полезной
            • 19. Re: LDAP to Active Directory= 'invalid login credentials'
              Rambo79
              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-Oracle
                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
                  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-Oracle
                    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
                    1 пользователь считает эту информацию полезной
                    • 23. Re: LDAP to Active Directory= 'invalid login credentials'
                      Rambo79
                      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
                        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-Oracle
                          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
                            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-Oracle
                              Did the dbms_ldap.simple_bind_s succeed now?

                              Regards,
                              Christian
                              • 28. Re: LDAP to Active Directory= 'invalid login credentials'
                                Rambo79
                                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-Oracle
                                  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