1 2 3 Previous Next 37 Replies Latest reply: Feb 18, 2013 5:03 PM by 703377 Go to original post RSS
      • 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.
        • 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.
              • 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
                      • 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