setup apex ldap works but with twist
James Xia, October 06, 2022 - 8:26 pm UTC
I am setting up APEX with LDAP. It works but with twist. Say I have a user call "John Doe" His sAMAccountName is jdoe and his cn is Doe, John.
I can only login with Doe, John/password but not jdoe/password.
What I did wrong here?
October 07, 2022 - 3:00 am UTC
Can you double check your work/setup against MOS note 2212921.1 and let us know if there are differences.
James Xia, October 07, 2022 - 3:18 pm UTC
I did everything according to the note 2212921.1 , But I cannot connect with jxia/pwd but I CAN connect if my username use Xia, James/pwd.
Essentially it is following function.
1 begin
2 IF APEX_LDAP.AUTHENTICATE(
3 p_username =>'Xia, James',
4 p_password =>'unifi27410#',
5 p_search_base => 'OU=Users,OU=Greensboro,DC=unifi,DC=local',
6 p_host => 'gsowdc3.unifi.local',
7 p_port => 389) THEN
8 dbms_output.put_line('authenticated');
9 ELSE
10 dbms_output.put_line('authentication failed');
11 END IF;
12* end;
SQL> /
authenticated
PL/SQL procedure successfully completed.
====================================
1 begin
2 IF APEX_LDAP.AUTHENTICATE(
3 p_username =>'jxia',
4 p_password =>'unifi27410#',
5 p_search_base => 'OU=Users,OU=Greensboro,DC=unifi,DC=local',
6 p_host => 'gsowdc3.unifi.local',
7 p_port => 389) THEN
8 dbms_output.put_line('authenticated');
9 ELSE
10 dbms_output.put_line('authentication failed');
11 END IF;
12* end;
SQL> /
authentication failed
PL/SQL procedure successfully completed.
October 10, 2022 - 2:09 am UTC
What if you try something simpler - just a basic auth request?
create or replace function authenticate_aduser(
p_username in varchar2,
p_password in varchar2)
return boolean
is
l_user varchar2(256);
l_ldap_server varchar2(256) := 'yourADserver.your.domain.com';
l_domain varchar2(256) := 'your.domain.com';
l_ldap_port number := 389;
l_retval pls_integer;
l_session dbms_ldap.session;
l_cnt number;
begin
l_user := p_username||'@'||l_domain;
l_session := dbms_ldap.init( l_ldap_server, l_ldap_port ); -- start session
l_retval := dbms_ldap.simple_bind_s( l_session, l_user, p_password ); -- auth as user
l_retval := dbms_ldap.unbind_s( l_session ); -- unbind
return true;
exception when others then
l_retval := dbms_ldap.unbind_s( l_session );
return false;
end;
/
James Xia, October 07, 2022 - 3:28 pm UTC
Here is my distinguishedName
CN=Xia\, James,OU=Users,OU=Greensboro,DC=unifi,DC=local
My SA told me, this is the distinguishedName should look like. I was asking can you make it look like CN=JXia,OU=Users,OU=Greensboro,DC=unifi,DC=local
Does anybody know any doc that talk about APEX linux in house server work with SSO with MS Active directory.
setup apex ldap works but with twist
A reader, October 11, 2022 - 2:03 pm UTC
SQL> l
1 CREATE OR REPLACE FUNCTION apex_ldap_auth.ldap_auth(
2 p_username IN VARCHAR2,
3 p_password IN VARCHAR2
4 )
5 return boolean
6 is
7 l_user varchar2(256);
8 l_ldap_server varchar2(256) := 'gsowdc3.unifi.local';
9 l_domain varchar2(256) := 'unifi.com';
10 l_ldap_port number := 389;
11 l_retval pls_integer;
12 l_session dbms_ldap.session;
13 l_cnt number;
14 begin
15 l_user := p_username||'@'||l_domain;
16 l_session := dbms_ldap.init( l_ldap_server, l_ldap_port ); -- start session
17 l_retval := dbms_ldap.simple_bind_s( l_session, l_user, p_password ); -- auth as user
18 l_retval := dbms_ldap.unbind_s( l_session ); -- unbind
19 return true;
20 exception when others then
21 l_retval := dbms_ldap.unbind_s( l_session );
22 return false;
23* end;
SQL> /
Function created.
SQL> l
1 begin
2 if apex_ldap_auth.ldap_auth('jxia','hidepassword') then
3 dbms_output.put_line('You are authenticated');
4 else
5 dbms_output.put_line('You are Not authenticated');
6 end if;
7* end;
SQL> /
You are Not authenticated
PL/SQL procedure successfully completed.
Reply to "What if you try something simpler - just a basic auth request?"
James Xia, October 11, 2022 - 6:46 pm UTC
SQL> l
1 begin
2 if apex_ldap_auth.ldap_auth('jxia','hidepassword') then
3 dbms_output.put_line('You are authenticated');
4 else
5 dbms_output.put_line('You are Not authenticated');
6 end if;
7* end;
SQL> /
You are authenticated
PL/SQL procedure successfully completed.
Only change is from unifi.com to unifi.local.
Thanks,
James
Now I need to find single sign on for APEX and AD. we are not use azure at all.