Oracle DBMS_LDAP.open_ssl in PL/SQL shows error: ORA-31202: SSL handshake failed

Question

We have switched to new Microsoft ADFS server and now we have to use LDAPS (LDAP over SSL on port 636). However in PL/SQL packages by adding DBMS_LDAP.open_ssl (based on here ) I get :

ORA-31202: DBMS_LDAP: LDAP client/server error: SSL handshake failed

My troubleshooting guided me to do a tcpdump debug of connection and I have found Oracle (12.1.0.2) DBMS_LDAP use only below three cipher suites on SSL handshake which all are very old and not secure and not support by latest Microsoft AD. Even I tried on another 12c db (12.1.0.2.0) with ldap.google.com:636 and received same error and same cipher suites.

Version: TLS 1.2 (0x0303)
Cipher Suite: TLS_DH_anon_WITH_3DES_EDE_CBC_SHA (0x001b)
Cipher Suite: TLS_DH_anon_WITH_RC4_128_MD5 (0x0018)
Cipher Suite: TLS_EMPTY_RENEGOTIATION_INFO_SCSV (0x00ff)

Oracle knowledge shows so many people had and have this issue (Doc ID 19285025.8, Doc ID 1561121.1) and only advice is patches "19285025". We have done this patch but still no progress. We have found Oracle document (here) to shows how configure Secure Sockets Layer and add cipher suite by help of "netmagr" but in the end even I know this is only for oracle DB connection but it not effected. Now questions are? 1. How to solve this "SSL handshake failed" for DBMS_LDAP.open_ssl? How we can set different cipher suite for DBMS_LDAP.open_ssl? 2. Any alternative PL/SQL package for using LDAPS? Or we have to use a Java package and load it in DB?

Answer 1

I have switched to java procedure instead of Oracle DBMS_LDAP. It working well and no more SSL issue (beside power of java for extending it).

    SET SERVEROUTPUT ON SIZE 5000;
CALL dbms_java.set_output(5000);

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED LDAP AS
import javax.naming.*;
import javax.naming.directory.*;
import java.util.Hashtable;
/**
 * Java LDAP Package by ATK, 9/9/2016
 */
class ldap {
    public static int ldap_auth(String username, String my_password, String ldap_server) {

    Hashtable env = new Hashtable(11);
    env.put(Context.INITIAL_CONTEXT_FACTORY, 
        "com.sun.jndi.ldap.LdapCtxFactory");
    env.put(Context.PROVIDER_URL, ldap_server ); //example "ldap://ldap.yourcompany.com:636"

    env.put(Context.SECURITY_PROTOCOL, "ssl");

    env.put(Context.SECURITY_AUTHENTICATION, "simple");
    env.put(Context.SECURITY_PRINCIPAL, "uid=" + username + ",OU=users,DC=company,DC=com");
    env.put(Context.SECURITY_CREDENTIALS, my_password);

    try {
        // Create initial context
        DirContext ctx = new InitialDirContext(env);

        System.out.println("Connection Successful.");

        ctx.close();
      return 0;
    } catch (NamingException e) {
        System.out.println("LDAP Connection: FAILED"); 
        e.printStackTrace();
      return -1;
    }
    }
};

--show errors java source ldap ; -- to check class compile

CREATE OR REPLACE FUNCTION ldap_auth (username in varchar2, my_password in varchar2, ldap_server in varchar2) RETURN NUMBER
AS LANGUAGE JAVA 
NAME 'ldap.ldap_auth (java.lang.String, java.lang.String, java.lang.String) return int';


call dbms_java.grant_permission( 'MySchema', 'SYS:java.net.SocketPermission', 'ldap.companyDomain.com', 'resolve' );
call dbms_java.grant_permission( 'MySchema', 'SYS:java.net.SocketPermission', 'ldapqa.companyDomain.com', 'resolve' );
call dbms_java.grant_permission( 'MySchema', 'SYS:java.net.SocketPermission', 'ldapdev.companyDomain.com', 'resolve' );
call dbms_java.grant_permission( 'MySchema', 'SYS:java.net.SocketPermission', '10.14.10.53:636', 'connect,resolve' );
call dbms_java.grant_permission( 'MySchema', 'SYS:java.net.SocketPermission', '10.14.10.54:636', 'connect,resolve' );
call dbms_java.grant_permission( 'MySchema', 'SYS:java.net.SocketPermission', '10.14.10.55:636', 'connect,resolve' );

And now you can test it with:

SET SERVEROUTPUT ON SIZE 5000
CALL dbms_java.set_output(0);
declare 
  l_ret int ;
begin
  l_ret := ldap_auth ('myUser', 'myPassword', 'ldap://lds.companyDomain.com:636');
  DBMS_OUTPUT.put_line('Return = ' || l_ret );
end;