Oracle PL/SQL LDAP - Unable to open wallet

Question

I'm trying to authenticate against an SSL secured LDAP server internally for my application running on Oracle 11g on Windows server using PL/SQL. I've got the certificate loaded into a wallet and stored in d:\wallets\ and can verify that it's valid / loads in oracle wallet manager (ewallet.p12 opens with the password I configured correctly). However, no matter what I do I cannot get it working using the PL/SQL functions. Here is the code:

create or replace FUNCTION ldap_auth(
    p_username IN VARCHAR2,
    p_password IN VARCHAR2
)
RETURN varchar2 IS
    l_ldap_host         VARCHAR2(256) := 'ldapserver.internal.net';
    l_ldap_port         number := 636;
    l_dn                        VARCHAR2(512);

    l_retval                PLS_INTEGER;
    l_session               DBMS_LDAP.session;

    l_wallet_loc        varchar2(256) := 'file:D:\wallets';
    l_wallet_pwd        varchar2(256) := 'pa55w0rd';
    l_wallet_ssl        number := 3;

BEGIN
    DBMS_LDAP.USE_EXCEPTION := TRUE;
    l_dn := 'cn='||p_username||',ou=People,dc=internal,dc=net';

    BEGIN
        l_session := DBMS_LDAP.init(l_ldap_host, l_ldap_port);
    EXCEPTION
        when others then
        raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
        RETURN 'exception1';
    END;

    BEGIN
        l_retval := DBMS_LDAP.open_ssl (l_session, l_wallet_loc, l_wallet_pwd, l_wallet_ssl);
    EXCEPTION
        when others then
        raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
        RETURN 'exception2';
    END;

    BEGIN
        l_retval := DBMS_LDAP.simple_bind_s(l_session, l_dn, p_password);
  EXCEPTION
    when others then
    raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
    RETURN 'exception3';
  END;

    return 'pass';
END;

And here's what happens when I run it:

select ldap_auth('userID','userPassword') as results1 from dual

ORA-20001: An error was encountered - -31202 -ERROR- ORA-31202: DBMS_LDAP: LDAP client/server error: Unable to open wallet ORA-06512: at "DBUSERNAME.LDAP_AUTH", line 33

I'm stuck at this point, I can't find any reference online as to how to make this work.

Answer 1

It turns out I did everything fine, and my wallet was working for utl_http to do https calls out of the database.

At the end it was the wallet-location that has to be defined differently!

For utl_http use ssl_wallet_loc VARCHAR2(256) := 'D:\OracleXE18g\dbhomeXE\owm\wallets\TestXE' ;

for ldap with ssl use ssl_wallet_loc VARCHAR2(256) := 'file:D:\OracleXE18g\dbhomeXE\owm\wallets\TestXE' ;

You see the difference is the word "file" in front. utl_tttp does NOT work with file: but in ldap you need to have it.

Answer 2

I got the orginal method to work finally. Follow the directions (mostly) from this discussion. It's a folder permissions problem on the wallet directory, needs to be added for the Oracle Server (service).

Add Folder Permissions to Virtual Account “NT SERVICE\MSSQLSERVER”
http://zarez.net/?p=3187

Your DBA will need to find out what the account is for the Oracle Server (service). For mine, using OracleXE to troubleshoot it was: NT SERVICE\OracleServiceXE Trying to set the folder permissions gave some pushback, as I think the Wallet Manager creates the wallet files with very limited access, and you have to be a Windows Admin to clear them, then reset them.

These are the sql code settings for the wallet, and I was able successfully query our LDAP server on port 636.

-- SSL Certificate Wallet Location (file path) and wallet password, as defined by the DBA. 
ssl_wallet_loc       VARCHAR2(256) := 'file:D:\OracleXE18g\dbhomeXE\owm\wallets\TestXE' ;     -- wallet location
ssl_wallet_passwd    VARCHAR2(256) := 'WaLLeT123$' ;     -- wallet password
ssl_wallet_auth      PLS_INTEGER := 2 ;         -- NO_AUTH: 1, ONE_WAY_AUTH: 2, TWO_WAY_AUTH: 3 

Answer 3

IDK if you managed to resolve this issue, but here are my answers based on deep analysis and a lot of googling about the same problem. I was trying to create PL/SQL function for SSL connection to AD server.

1) I think that the problem that you stated will be solved with this:

CREATE DIRECTORY mydir AS 'D:\wallets';

granting READ and WRITE in this case is not necessary because you automaticly get these grants. But you will face an other problem after this. It will be

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

2) If you are using 11g DB and latest versions of Microsoft AD server you won't be able to connect to AD through SSL connection because ciphers used in Oracle DB. They must match bouth in DB and in AD server, but Oracle is using old and IMHO not secure ciphers. They fixed this issue, partially in 12c link to documentation for 12c

2) The only way that I managed to fix this problem in 11g was through Java. Solution is this:

SET SERVEROUTPUT ON;
CALL dbms_java.set_output(1000);

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED LDAP AS
import java.net.InetAddress;
import java.net.UnknownHostException;
import java.sql.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.Hashtable;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.naming.Context;
import javax.naming.NamingException;
import javax.naming.directory.DirContext;
import javax.naming.directory.InitialDirContext;

public class ldap {
    public static boolean ldap_auth(String username, String password, String ldap_server, String application) throws SQLException, ClassNotFoundException, UnknownHostException{


        String keystore = "D:\\wallets";
        System.setProperty("javax.net.ssl.trustStore", keystore);
        System.setProperty("javax.net.ssl.trustStorePassword", "password");

        Class.forName("oracle.jdbc.driver.OracleDriver");

        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:SID", "USER", "password");

        Hashtable env = new Hashtable(11);
        env.put(Context.INITIAL_CONTEXT_FACTORY, "com.sun.jndi.ldap.LdapCtxFactory");
        env.put(Context.PROVIDER_URL, ldap_server);
        env.put(Context.SECURITY_PROTOCOL, "ssl");
        env.put(Context.SECURITY_AUTHENTICATION, "simple");
        env.put(Context.SECURITY_PRINCIPAL, username);
        env.put(Context.SECURITY_CREDENTIALS, password);

        InetAddress addr;
        addr = InetAddress.getLocalHost();
        String ip = addr.getHostAddress();



        try {
            DirContext ctx = new InitialDirContext(env);

            System.out.println("Connection Successful!");
            ctx.close();

            return true;
        } catch (Exception e) {
            String errorMsg = e.toString();
            String[] errorCode = {"data 525", "data 773", "data 52e", "data 775", "data 701", "data 533", "data 532"};
            String returnErrorCode = null;

            List<String> list = Arrays.asList(errorCode);
            for(String word : list){
               if(errorMsg.contains(word)) {
                   returnErrorCode = word;
               }
            }
            CallableStatement procin = conn.prepareCall("begin prc_log_action_java (:1,:2,:3,:4); end;");
            username = username.substring(0, username.indexOf('@'));
            procin.setString(1, username);
            procin.setString(2, returnErrorCode);
            procin.setString(3, ip);
            procin.setString(4, application);
            procin.execute();
            procin.close();

            System.out.println(errorMsg);
            return false;
        }

    }
};

Try catch section is for logging error messages generated by AD server that I'm catching and calling anonymous block and inserting in table, I think you don't need this code, but just for case. errorCode field are listed some of error codes generated by AD server link to AD error codes.

After that you need to create PL/SQL function in DB like this:

create or replace function ldap_auth (p_username in varchar2, p_password in varchar2, p_ldap_server in varchar2)return boolean
as language java
name 'ldap.ldap_auth (java.lang.String, java.lang.String, java.lang.String, java.lang.String) return boolean';

Last thing that is crucial are grant premissions in DB.

call dbms_java.grant_permission( 'USER', 'SYS:java.net.SocketPermission', 'AD server:636', 'connect,resolve' );
exec dbms_java.grant_permission( 'USER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.keyStore', 'write' );
exec dbms_java.grant_permission( 'USER', 'SYS:java.util.PropertyPermission', 'javax.net.ssl.trustStorePassword', 'write' );

commit;

Answer 4

You could try using pls_integer instead of number in the declaration of l_wallet_ssl. In addition, consider removing the when others exception to see if you can get a more helpful error (see Table 4-86 for the range of errors from this link) from the open_ssl function call.