PL/SQL LDAP over SSL – Please Test
Posted by Tyler Muth on January 30, 2009
I’ve been meaning to write this for quite a while now (measured in years), but never got around to it. None of the DBMS_LDAP code samples support SSL, including the APEX LDAP Authentication Scheme. Most LDAP directories require SSL for authentication, as you would otherwise send usernames and passwords in clear text. Depending on your network topography, the risk of someone capturing that data might be very low, but just the same, it should really be SSL.
One perceived barrier to making this work was my belief that this required an Oracle Wallet which are a bit of a hassle to setup. There was also some confusion on my part that Oracle Wallets required a license for the Advanced Security Option (ASO), so this would limit the use of this code to only the subset of our customers that have purchased this option. I’m still looking for the email on this but I believe we confirmed that the Oracle Wallet Manger by itself (such as for DBMS_LDAP or UTL_HTTP) does not require ASO. Only when you are using the explicitly defined features of ASO such as encrypted column / tablespaces / backups, network encryption, and a few others I can’t remember. If someone has more info on this, please feel free to comment as I’m too [ busy | lazy | (select adjective from adjectives where id = round(dbms_random.value(1, 100)))
] to find it [today | tomorrow | (select sysdate + round(dbms_random.value(1,365)) from dual)
]. When I finally sat down to write this for my current project, I quickly discovered that for SSL Mode 1 (which is what 99.999% of people want), no wallet is required! Can’t believe I put this off for so long based on a false assumption.
So what does this code do? It can:
- Bind to an LDAP directory using SSL or non-SSL
- Authenticate a user using their exact Distinguished Name (DN) such as: cn=tyler.muth, l=amer, dc=mycompany, dc=com
- Given a starting point (search base) and a username (tyler.muth), search for that user, retrieve their DN, then authenticate. This handles users in multiple sub-trees.
This is NOT production code! This is NOT production code! This is NOT production code! Why am I posting it here then? Because I want people to test it. I’ve tested it on the project I’m working on, but that doesn’t mean it’s bug free. I want people who are familiar with PL/SQL and LDAP to review and test my code. As I receive suggested changes to the code, I’ll update it. When I feel comfortable that enough people have tested, I’ll blog again and “release it” to the community to use. My goal is make this as flexible and easy as possible so it just becomes a utility that people can use when they need LDAP. I can’t make any promises, but if it makes it back into APEX, then you will have helped to improve a product. If you are not all that familiar with PL/SQL and LDAP, or are looking for a production ready solution, this is probably not for you. When it’s ready, this blog post will be mostly empty and point to the new location of the code.
I’ve only tested this against OID using an 11.something database, so I’d love to have people test this against Active Directory and any other LDAP directories they work with and at least 10gR2 and XE. Mark Wilcox is testing it with Oracle Virtual Directory in the next few days as well. I also ran Wireshark on the database server to capture the communitcation between the database and the LDAP directory. As expected, in non-ssl mode, my username and password were in the clear. With SSL enabled, everything was garbage. Please use Wireshark with caution as it may violate network policies of your organization, get you fired, and accelerate global warming. It’s a great tool when used ethicly.
I really appreciate the feedback I’ve recieved in the past from this community, especially in pointing out errors with my code. Thanks in advance for your help.
Customize the ldap_globals package with data from your environment. For most environments, you’ll only need to change g_host and g_search_base.
create or replace package ldap_globals authid definer is g_host constant varchar2(255) := 'ldap.mycompany.com'; g_port constant number := 636; g_search_base constant varchar2(255) := 'dc=mycompany,dc=com'; g_search_filter constant varchar2(255) := 'cn='; g_exact_dn constant boolean := false; g_use_ssl constant boolean := true; -- if g_exact_dn is false, then we will start at the search base and find the user to get their DN. -- if true then we will just bind with the DN and you should pass the fully qualified user DN into p_username -- SSL is hard-coded to use mode 1, as modes 2 and 3 are highly unlikely. See the DBMS_LDAP doc for more info. -- No wallet is needed for mode 1 end ldap_globals; / show errors
There’s no need (that I know of) to customize any code in this function.
create or replace function ldap_authenticate( p_username in varchar2, p_password in varchar2) return boolean authid definer is l_retval pls_integer; l_session dbms_ldap.session; l_attrs dbms_ldap.string_collection; l_message dbms_ldap.message; l_entry dbms_ldap.message; l_dn varchar2(1000); l_user_handle dbms_ldap_utl.handle; l_return pls_integer; l_user_type pls_integer; l_filter varchar2(256); function do_connect return pls_integer as l_ldap_user varchar2(256) := NULL; l_ldap_pwd varchar2(256) := NULL; begin dbms_ldap.use_exception := true; l_session := dbms_ldap.init( ldap_globals.g_host , ldap_globals.g_port); if ldap_globals.g_use_ssl = true then l_retval := DBMS_LDAP.open_ssl( ld => l_session, sslwrl => NULL, -- wallet location, ie file:/etc/ORACLE/WALLETS/oracle sslwalletpasswd => NULL, -- wallet password sslauth => 1); -- NO_AUTH :1, ONE_WAY_AUTH: 2, TWO_WAY_AUTH: 3 end if; return dbms_ldap.simple_bind_s( l_session, l_ldap_user, l_ldap_pwd ); end; procedure do_close as begin l_retval := dbms_ldap.unbind_s( l_session ); end do_close; procedure handle_error(p_message in varchar2) as begin do_close; $IF $$logger $THEN logger.log_error(p_message); $END RAISE_APPLICATION_ERROR (-20001,p_message); end; begin l_user_type := DBMS_LDAP_UTL.AUTH_SIMPLE; l_retval := do_connect; if ldap_globals.g_exact_dn = false then l_attrs(1) := 'dn'; l_filter := '('|| ldap_globals.g_search_filter|| p_username||')'; l_retval := dbms_ldap.search_s( l_session, ldap_globals.g_search_base, dbms_ldap.scope_subtree, l_filter, l_attrs, 0, l_message ); l_retval := DBMS_LDAP.count_entries(l_session, l_message); if l_retval > 1 then handle_error('More than one entry found when searching for users DN.'); end if; if l_retval = 0 then handle_error('No entries were found when searching for user DN'); end if; l_entry := dbms_ldap.first_entry( l_session, l_message ); l_dn := dbms_ldap.get_dn( l_session, l_entry ); else l_dn := p_username; end if; -- ldap_globals.g_exact_dn = false l_retval := null; l_retval := DBMS_LDAP_UTL.create_user_handle( user_hd => l_user_handle, user_type => DBMS_LDAP_UTL.TYPE_DN, user_id => l_dn); begin dbms_ldap.use_exception := false; l_return := null; l_return := dbms_ldap.simple_bind_s( ld => l_session, dn => l_dn, passwd => p_password ); if l_return != 0 then do_close; return false; end if; exception when others then do_close; raise; end; dbms_ldap.use_exception := true; do_close; if l_return is null then handle_error('simple_bind_s did not return a value.'); return false; end if; if l_return = 0 then return true; end if; exception when others then do_close; raise; return false; end ldap_authenticate; / show errors
A simple test script. If using SQL Developer, make sure you enable output on the DBMS_OUTPUT tab.
set serveroutput on declare l_return boolean; begin l_return := ldap_authenticate( p_username => 'first.last', p_password => ''); if l_return = true then dbms_output.put_line('Authenticated'); else dbms_output.put_line('Fail!'); end; end; /
Dan said
Tyler,
I’ll try to give this a go early next week.
Regards,
Dan
Salim said
Hi,
I will test this against active directory next week and i will give you my feedback
Joseph Charpak said
Nitpicking: You’ve got two lines of code that do “if boolean = true then…” or “if boolean = false then…”
You don’t need to do that. you can write “if boolean then…” or “if not boolean then…” I also recommend rewriting the boolean function name or variable to clearly indicate it’s a boolean.
E.g.
if ldap_globals.g_exact_dn = false then
could become:
if not ldap_globals.g_using_exact_dn then
and
if ldap_globals.g_use_ssl = true then
could become:
if ldap_globals.g_using_ssl then
Phil Clifford said
morphic resonance : just getting around to this myself “after several years” and google reader popped this up for me. Will be testing against oid and AD on 10gR2 and XE in a few days – meantime a slight correction :”None of the DBMS_LDAP code samples support SSL” – except the one on (see sslbind.sql in the DBMS_LDAP API package).
Tyler Muth said
Phil,
What I meant was the practical LDAP code snippets floating around the APEX forum and various blogs. I actually used the sample code you linked to when I wrote this code and that code has been around for years. It’s not all that useful in it’s current form, but it does do SSL.
Can’t wait to hear your results from AD. I’ve tested this on 10.2, 11.1 and 11.2, but only against OID and OVD.
Thanks,
Tyler
John Scott said
Hi Tyler,
Great post, I’ll test it against AD, OID, Sun Directory Server and also OpenLDAP when I get a sec.
John.
Pat Miller said
I would appreciate any feedback from other users who have succeeded with SunOne ldap.
Sriram said
Does not seem to work for 10.2.0.3 and AD combination.
declare
l_session dbms_ldap.session;
l_retval pls_integer;
begin
dbms_ldap.use_exception := true;
l_session := dbms_ldap.init( 'my_domain_controller.domain', 389 );
l_retval := dbms_ldap.simple_bind_s( l_session, 'domain\user', 'password');
l_retval := dbms_ldap.unbind_s( l_session );
end;
/
PL/SQL procedure successfully completed.
declare
l_session dbms_ldap.session;
l_retval pls_integer;
begin
dbms_ldap.use_exception := true;
l_session := dbms_ldap.init( 'my_domain_controller.domain', 389 );
l_retval := DBMS_LDAP.open_ssl( ld => l_session,
sslwrl => NULL,
sslwalletpasswd => NULL,
sslauth => 1);
l_retval := dbms_ldap.simple_bind_s( l_session, 'domain\user', 'password');
l_retval := dbms_ldap.unbind_s( l_session );
end;
/
declare
*
ERROR at line 1:
ORA-31202: DBMS_LDAP: LDAP client/server error: UnKnown Error Encountered
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_LDAP", line 1457
ORA-06512: at "SYS.DBMS_LDAP", line 1234
ORA-06512: at line 7
Pat Miller said
We also got similar ORA 31202 error just trying to do an ssl bind with 10.2.0.3 enterprise edition and SunOne LDAP. My suspicion is that the dbms_ldap package does not even support ssl bind to non-OID LDAPs. Can anyone confirm that? Someone on Apex forum suggested running open source stunnel on the Apex server to mediate.
Pat
Pat Miller said
Georger, Tyler,
I see that Georger was the one who suggested stunnel on the Apex forum. From another post there (Apex forum) regarding pl/sql to AD over SSL what is becoming clear are two options:
1.) You need certificate exchange from LDAP server to Oracle wallet on Apex server in order to have SSL bind from Oracle to 3rd party ldap. So dbms_ldap supports ssl bind to 3rd party if it has the LDAP server certificate.
2.) You can bypass the certificate exchange if you use stunnel on the Apex server.
Correct me if I am wrong!
Pat
Earl said
I’ve tested this against an AD and also found the call dbms_ldap.open_ssl is failing with the same error mentioned by Sriram.
I think we need to clarify something here, if nothing but my own understanding. If this is a function lives in the database and the database is being asked to do the LDAP authentication, shouldn’t the wallet referenced here be a wallet on the Oracle db host rather than the Apex web machine (assuming they are different – as in my case)?
Earl said
And then the follow-on to my earlier point: wouldn’t that mean that the Oracle instance would need to have Oracle Advanced Security in order to be able to talk SSL to other hosts?
And isn’t this what is leading people down the path of recommending something like stunnel?
Pat Miller said
We were able to get basic SSL authenticated bind to work with our SunOne LDAP. We had to do following:
1.) add our LDAP cert to the Oracle wallet on server and
2.) put that file location into open_ssl function 2nd parameter and the password into 3rd parameter, and sslauth type 1 in 4th parameter.
A bind with our ‘service user’ dn and password succeeded. We are not using advanced security.
I think we will have to adapt your authentication function since we cannnot do a search until we have done a ‘service user’ dn bind as a valid service followed by the search for the dn of the user based on the userid. In our environment, the SunOne LDAP must have registered our Apex service as a valid service dn (e.g., Cold Fusion server has a service dn, Websphere app server has a service dn, etc.).
Stay tuned.
Pat
Pat Miller said
Correction to my last post.
#2) should be type 2 in 4th parameter of the open_ssl call.
Pat
Pat Miller said
Tyler,
I don’t understand use of: l_ldap_user and l_ldap_pwd in this section of your authentication function:
function do_connect
return pls_integer
as
l_ldap_user varchar2(256) := NULL;
l_ldap_pwd varchar2(256) := NULL;
begin
dbms_ldap.use_exception := true;
l_session := dbms_ldap.init( ldap_globals.g_host , ldap_globals.g_port);
if ldap_globals.g_use_ssl = true then
l_retval := DBMS_LDAP.open_ssl( l_session, NULL, NULL, 1);
end if;
return dbms_ldap.simple_bind_s( l_session, l_ldap_user, l_ldap_pwd );
Pat
Pat Miller said
Hi,
Just succeeded in getting your function to work for us with our SunOne ldap server! Now just have to add it to Apex as our custom Auth function.
Keys to getting ours to work:
A.) had to get our SunOne LDAP server cert into the oracle wallet and identify that location in the open_ssl call; so we had a type 2 call — one way auth.
B.) Our LDAP requires an application dn so we had to first bind with that dn and password so: l_ldap_user varchar2(256)had to be set to our service dn and l_ldap_pwd varchar2(256) had to be set to our dn password. Then the search for user could succeed.
C.) The last problem I had was I was using the wrong search base in the ldap_globals package since our schema is so different from the exammples I had seen, but our LDAP administrator was very helpgul in this regard and gave me the correct search base for our LDAP. Once that was correct we were able to authenticate!
Thanks for providing this posting!!
Pat
Pat Miller said
Hi,
We were so happy to get Tyler’s code adapted and working against our SunOne LDAP. Then we upgraded to Apex 3.2 and now we cannot authenticate. The function still works when we run it thru sql*plus or sql developer with some test users. We made no change to the application but now those same test users who we were authenticating are not able to authenticate — we get invalid credentials. Has anyone else upgraded to 3.2 and are using custom LDAP authentication?
Pat
SE said
Sriram,
It didn’t work for AD because domain\user isn’t valid for ldap only kerberose. You still need to find the full DN just like other ldap systems. Also disable SSL initially and make sure it works then. AD can be difficult when it comes to SSL and on port 389 they use TLS also. 636 is LDAPS. By the way this works for Novell E-directory.
SE said
PAT,
I read on another forum that there is a bug. The solution was to change the authentication scheme and then re-apply the one you want.
Jeff said
Hello,
Does anyone know how the ldap certificate is managed if you do not use the Oracle Wallet? I have a 3rd party secure ldap which we are trying to authenticate against using Tyler’s code but we don’t want to install OID, Oracle Wallet, or Oracle Certificate Authority. I am getting an error on the dbms_ldap.open_ssl call which has session, null for the wallet location and null for the password, the type is 1.
thanks,
Jeff
TomS said
thanks for the code, i needed full dn search by the uid :)by the way, SSL doesn’t work on my 10.2.0.3.0.
Darragh Duffy said
Hi All – there is an Oracle Note via Oracles Metalink Site
Note.215532.1 Support of PL/SQL API ( DBMS_LDAP ) and C API with Third Party LDAP v3 Directory Servers
having spoken to Oracle in the last few days the 3 different SSL modes
1,2 and 3. Mode 1 will only wotk with OID, you will need to use mode 2 and 3 for SSL to work with 3rd party direcotories and Oracle will not support issues with 3rd party directories. It does appear AD will work mode 2, and as Pat above mode 2 appears to work with Sun One.
I will be testing Oracle DB 10.2.0.4 and sun One directory in mode 2 over a load balancer in the coming days and will let you the results.
Darragh
Darragh Duffy said
Hi Pat – I have a question for you please
what steps did you follow to get the SunOne Ldap Certificate into an oracle wallet ?
I know the steps for AD but am not familar with the SunOne directory.
Darragh.
Pat Miller said
Hi, Darragh,
Regarding wallet and cert for SunOne:
l_retval := DBMS_LDAP.open_ssl(l_session,’file:/var/opt/ORACLE/WALLETS/oracle’,’xxxxxxxxx’,2);
We had to load the cert from our SunOne server into the Oracle wallets directory on the server as referenced above in dbms_ldap call. We got the cert from our SunOne ldap administrator. The xxxxxxxx is the wallet password as described by Tyler.
Pat
darragh duffy said
Hi Pat – thanks for your rely – one or two questions if you dont mind:
1. when you loaded the cert from sun, did you use Oracle Wallet Manager or simple place into oracle_home/wallets/oracle ?
2. Did you have to get an additional Cert fom Verisifn / Twarte etc. or simply did you just us the one issued by the Sun One Administrartor ?
I have DBMS_LDAP working without the ldapS part, I have been trying to get the ldapbind.exe command to work first i.e.
ldapbind -h smusdir.bms.com -p 636 -D “uid=core_sso,ou=nonpeople,o=bms.com” -w -U 2 -W “file:c:\coressl” -P
but I get the Error Unknown Error –
and if I get that to work I was going to progress to amending my PLSQL code, did you try the above LDABIND command that comes with Oracle DB / Oracle AS etc?
thanks in advance for your time, Darragh
Pat Miller said
Answers below each question
>1. when you loaded the cert from sun, did you use Oracle Wallet >Manager or simple place into oracle_home/wallets/oracle ?
I believe it was just a matter of copying into the wallets directory, but I will double check.
The following helpful hint was posted at oracle apex (applies to us even though this one relates to AD):
“The most important trick is that you have an oracle wallet with the root CA certificates in it so that the SSL bind can validate the certificates issued by your AD servers…. To set up ldap via pl/sql in a new server, we simply copy over our ldap wallet and use the exact same code in each instance. The ldap wallet holds nothing of value, just the root certificate authority certs, which aren’t a secret because they automatically get propagated to every windows machine in our domain.”
>2. Did you have to get an additional Cert fom Verisifn / Twarte etc. >or simply did you just us the one issued by the Sun One >Administrartor ?
NO, we just used the cert that was provided us by our SunOne ldap administrator– it was Equifax.
>I have DBMS_LDAP working without the ldapS part, I have been trying >to get the ldapbind.exe command to work first i.e.
>ldapbind -h smusdir.bms.com -p 636 -D >“uid=core_sso,ou=nonpeople,o=bms.com” -w -U 2 -W “file:c:\coressl” -P
>but I get the Error Unknown Error –
>and if I get that to work I was going to progress to amending my >PLSQL code, did you try the above LDABIND command that comes with >Oracle DB / Oracle AS etc?
Once we had the certs in place we were able to do secure bind. I don’t think we tried a simple bind since we could not get any response other than verify that we could ping the ldap server. Once the certs were in place we tried command line ldaps call and the LDAP administrator checked logs and could see the bind. Then we proceeded with continued work on Tyler’s code to work with our LDAP. The one key item we did not have working properly initially was the search base we initially used was wrong — our schema (higher ed) is quite different from many of the examples used so we had to make sure our search base and other parameters in ldap_globals package were appropriate for our ldap.
Pat Miller said
Also, everything is working now in Apex 3.2. Belated thanks to SE concerning the bug. Re-applying the authentication scheme did the trick.
Pat
Eduardo said
Hi,
I’m testing with this function to get authenticated in a Active Directory Scheme but I’m not sure why I need to specified my complete name as username parameter (Benjamin Eduardo Garcia) and not my alias (beg) to get authenticated. Someone of you know why is happening that. I’m a newbie in Active Directory so I don’t know if I’m missing something in the search_base or any other place, the values of global variables are:
g_host constant varchar2(255) := ‘192.168.78.3’;
g_port constant number := 389;
g_search_base constant varchar2(255) := ‘DC=patopur,DC=net’;
BTW the same happens when I use APEX LDAP authentication scheme, any idea?
Thanks
Eduardo
Faiyaaz said
Did anyone mange to get a break through on querying Microsoft Active Directory via Oracle 10(PL/SQL)using LDAP
Darragh Duffy said
Hi Faiyaaz – yes I use Oracle 10.2.0.4 DB with AD 2003. both quering and SSL.
Darragh.
Darragh Duffy said
Hi All – I finally Managed to get this to work. The main Issue I had was the certificate seemed to be invalid.
I also managed to verify that this work by using wireshark, intalled it on the Database Server and filter by ip.dst == 192.168.1.xxx
works quiet nicely.
also have this working in Novell, still having issues with Sun One but I think this is the certificate or a networking issue.
Darragh.
Chris Gilbane said
We also were of the opinion that you needed ASO to use Oracle Wallet. However, our account manager also mentioned something similar to your original blog post. Likewise I am also trying to find something concrete on licensing – did you every find anything yourself?
Cheers
Chris
Rohit said
Hi,
Thanks for posting the code. I tested the above function with OpenLDAP. The “user authentication” works fine when the password is in unencrypted form. but when the password is stored in encrypted for it does not work. I would greatly appreciate if anybody can provide some inputs/insights.
Thank you.
RJ
brian said
Is the posted code in the main section good for SSL?
Dan said
I am trying to retrieve the OID to check for password warn (2.16.840.1.113730.3.4.4) and passowrd expiration time (2.16.840.1.113730.3.4.5) in Sun One, can someone suggest. Thanks a lot in advance.
Thank you
Patrick Miller said
Hi,
To Brian — the posted code definitely works with SSL. You have to keep in mind the that the Oracle server where Apex is run from needs to have the Cert from your LDAP server in the Oracle wallet. This may not be required for OID, but it is required for other LDAP servers. I have been using Tyler’s code for our enterprise SunOne LDAP authentication which requires SSL and a service DN authorized to authenticate successfully for couple years now.
Now I am wanting to retrieve attributes. Has anyone integrated DBMS_LDAP..search_s with Tyler’s code to retrieve user attributes and would be willing to share. Our LDAP engineers gave me some code to retrieve attributes for Perl and said it can be done but they have no clue how to do this in pl/sql. This I think would also answer Dan’s question if expiration time is an attribute in LDAP..
Pat
Piyush Balan said
Pat,
you use a built-in calld DBMS_LDAP.first_attribute to get the attribute for the outcome of DBMS_LDAP.first_entry
This is to be followed by DBMS_LDAP.get_values that in turn uses the output of DBMS_LDAP.first_attribute.
You need to loop around the outputs of each and then use DBMS_LDAP.next_attribute and DBMS_LDAP.next_entry to keep progressing
here’s a code snipped of something that I’ve written to run against Oracle 8.1.5:
IF DBMS_LDAP.COUNT_ENTRIES (ld => vsession
,msg => vmessage
) > 0
THEN
— Get all the entries returned by our search.
ventry := DBMS_LDAP.first_entry (ld => vsession
,msg => vmessage
);
— dbms_output.put_line (‘Retval: ‘||TRIM(TO_CHAR(iretval)));
— dbms_output.put_line (‘Message: ‘||vmessage);
END IF;
—
<>
WHILE ventry IS NOT NULL
LOOP
itotalCount := itotalCount + 1;
— Get all the attributes for this entry.
DBMS_OUTPUT.PUT_LINE(‘—————————————‘);
vattr_name := DBMS_LDAP.first_attribute (ld => vsession
,ldapentry => ventry
,ber_elem => beber_element
);
<>
WHILE vattr_name IS NOT NULL LOOP
— Get all the values for this attribute.
scvals := DBMS_LDAP.get_values (ld => vsession
,ldapentry => ventry
,attr => vattr_name
);
<>
FOR ival IN scvals.FIRST .. scvals.LAST LOOP
—
IF vattr_name = ‘uid’ AND vcreateString = 0
THEN
voutputString := voutputString||SUBSTR(scvals(ival),1,200);
END IF;
—
IF vattr_name = ‘EmailAddress’ AND vcreateString = 0
THEN
voutputString := voutputString||’,’||SUBSTR(scvals(ival),1,200);
END IF;
—
END LOOP values_loop;
—
vattr_name := DBMS_LDAP.next_attribute (ld => vsession
,ldapentry => ventry
,ber_elem => beber_element
);
END LOOP attibutes_loop;
—
ventry := DBMS_LDAP.next_entry (ld => vsession
,msg => vmessage
);
END LOOP entry_loop;
Hope this helps.
Patrick Miller said
Piyush,
Thank you very much for your reply. Yes, I was able to use the method you are describing to do exactly what I needed. It has been working well for us! I had posted on Apex OTN site and got some help from there.
Pat
Sanjay said
Hi,
I to have struggled to make it work over Secure Port 636 from Oracle to AD.
My issue was related to Certificate which was given to me by Active Directory Admins , that CERT was importing fine but did not work and I always get “SSL Handshake error” in 11.2 DB environment and 10g environment, it was Unknown Error Occured.
Certificate which we need from AD Admins is called “CA Certificate Chain” and there was even URL where even we can download from http://hostname/certsrv/
You need to replace hostname with correct name for “Microsoft Certificate Services ” server. Your AD admins should help to tell you about this. When you go to above URL, I saw option to Download CA Certificate Chain which you need to download and save it and then import into Oracle Wallet Manager as a Trusted Certificate.
Oracle Support Note : How to get SSL Certificates from a Microsoft Certification Services CA [ID 178806.1]
I am not sure whether you need to self sign cert from the server where you are running this but this document was helpful.
I have followed this document.
How To Generate A Wallet Containing A Self Signed Certificate Using ORAPKI [ID 560982.1]
— Change your password and wallet location and cn=hostname to hostname where you are running.
orapki wallet create -wallet /home/oracle/wallettest/ -pwd password -auto_login
orapki wallet add -wallet /home/oracle/wallettest/ -dn ‘cn=hostname’ -keysize 1024 -self_signed -validity 3065 -pwd password
—–
To Debug Issues, replace ADServerName with AD Info and Wallet Location and Password and your search base.
ldapsearch -h ADServerName -p 636 -U 2 -W “file:/home/oracle/wallettest” -P “walletpassword” -d -1 -b “your search base” -s sub “(cn=xyz)” dn >/tmp/outfile1.txt 2>&1
It would generate /tmp/outfile1.txt , you can check where it is failing.
Good Luck.
Susan Johnson said
Hi,
I’ve successfully created an AD user using DBMS_LDAP package using combination of Oracle Wallet and SSL, however, the user created is disabled. Can anyone tell me what am I doing wrong or how I do enable user’s account?
FUNCTION Create_User(p_user VARCHAR2, p_ddso VARCHAR2, p_fullname VARCHAR2) RETURN VARCHAR2 IS
my_session dbms_ldap.session;
my_message dbms_ldap.message;
user_attrs dbms_ldap.string_collection;
user_array dbms_ldap.mod_array;
user_vals dbms_ldap.string_collection;
user_dn VARCHAR2(256);
first_name VARCHAR2(50);
last_name VARCHAR2(50);
first_last_name VARCHAR2(100);
middle_initial VARCHAR2(1);
uname VARCHAR2(30);
ddsogrp VARCHAR2(100);
ddsoou VARCHAR2(256);
ddsonm VARCHAR2(256);
ddsogp VARCHAR2(256);
ddsoau VARCHAR2(256);
retval PLS_INTEGER;
dpword VARCHAR2(30) := ‘xpasswd123’;
ldap_valsb DBMS_LDAP.BERVAL_COLLECTION ;
UTC_passwd RAW(400);
l_user_type pls_integer;
BEGIN
retval := -1;
l_user_type := DBMS_LDAP_UTL.AUTH_SIMPLE;
my_session := create_session;
dbms_output.put_line(RPAD(‘simple_bind_s Returns ‘,25,’ ‘) || ‘: ‘|| TO_CHAR(retval));
if ldap_globals_pkg.gc$use_ssl = true then
dbms_output.put_line(‘use_ssl is true’);
retval := DBMS_LDAP.open_ssl(my_session,
ldap_globals_pkg.wallet_loc,
ldap_globals_pkg.wallet_passwd,
2); — NO_AUTH :1, ONE_WAY_AUTH: 2, TWO_WAY_AUTH: 3
end if;
retval := dbms_ldap.simple_bind_s(my_session, ldap_globals_pkg.gc$ldap_user, ldap_globals_pkg.gc$ldap_passwd);
— issue the search
user_attrs(1) := ‘*’;
— retrieve all attributes
retval := dbms_ldap.search_s(my_session, ldap_globals_pkg.gc$ldap_base, dbms_ldap.scope_subtree, ‘cn=’||p_user, user_attrs, 0, my_message);
dbms_output.put_line(RPAD(‘search_s Returns ‘,25,’ ‘) || ‘: ‘|| TO_CHAR(retval));
dbms_output.put_line(RPAD(‘LDAP message ‘,25,’ ‘) || ‘: ‘ ||RAWTOHEX(SUBSTR(my_message,1,8)) || ‘(returned from search_s)’);
— count the number of entries returned
retval := dbms_ldap.count_entries(my_session, my_message);
dbms_output.put_line(RPAD(‘Number of Entries ‘,25,’ ‘) || ‘: ‘|| TO_CHAR(retval));
dbms_output.put_line(‘————————————————‘);
if retval = 1 then
dbms_output.put_line(‘User have an AD account’);
return ‘User already exist’;
else
dbms_output.put_line(‘Create user in Active Directory’);
— create and setup attribute array for the New entry
uname := lower(p_user);
dbms_output.put_line(uname);
ddsogrp := p_ddso;
if substr(p_ddso, 1,3) = ‘020’ then
ddsogrp := ‘0200’;
end if;
first_name := mr_api.delim_piece(p_fullname, ‘ ‘, 1);
dbms_output.put_line(first_name);
middle_initial := mr_api.delim_piece(p_fullname, ‘ ‘,2);
dbms_output.put_line(middle_initial);
last_name := mr_api.delim_piece(p_fullname, ‘ ‘, 3);
dbms_output.put_line(last_name);
first_last_name := first_name || ‘ ‘ || last_name;
dbms_output.put_line(first_last_name);
— add user to groups(highly managed group and ddso group)
select ddso_ou, office_name
into ddsoou, ddsonm
from mr.ad_lookup
where ddso_code = p_ddso;
dbms_output.put_line(ddsoou);
dbms_output.put_line(ddsonm);
user_array := DBMS_LDAP.CREATE_MOD_ARRAY(12);
user_vals(1) := uname;
DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘sAMAccountName’,user_vals);
user_vals(1) := uname;
DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘CN’,user_vals);
user_vals(1) := last_name;
DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘SN’,user_vals);
user_vals(1) := first_name;
DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘givenName’,user_vals);
user_vals(1) := middle_initial;
DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘initials’,user_vals);
user_vals(1) := p_ddso;
DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘description’,user_vals);
user_vals(1) := last_name||’, ‘||first_name||’ ‘||middle_initial||’.’;
DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘displayName’,user_vals);
dbms_output.put_line(ddsonm);
user_vals(1) := ddsonm;
DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘physicalDeliveryOfficeName’,user_vals);
utc_passwd := UTL_RAW.cast_to_raw(convert(‘”‘ || dpword ||'”‘,’AL16UTF16LE’));
ldap_valsb(1) := utc_passwd;
DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD,’unicodePwd’,ldap_valsb);
–user_vals(1) := ‘FALSE’;
— DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD,’enabled’,user_vals);
user_vals(1) := ‘FALSE’;
DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD,’AccountDisabled’,user_vals);
user_vals(1) := ‘User’;
DBMS_LDAP.populate_mod_array(user_array,DBMS_LDAP.MOD_ADD, ‘objectClass’,user_vals);
— DN for ENtry to be added under ldap_base
user_dn := ‘CN=’||uname||’,OU=Users,OU=’||ddsoou ||’,OU=DDSO,’||ldap_globals_pkg.GC$ldap_base;
dbms_output.put_line(rpad(‘Adding Entry for DN ‘,25,’ ‘) || ‘:[‘ || user_dn ||’]’);
— Add new Entry to ldap directory
retval := dbms_ldap.add_s(my_session,user_dn,user_array);
dbms_output.put_line(rpad(‘add_s retruns ‘,25,’ ‘) || ‘: ‘ || to_char(retval));
— Add the user to the portal group —
ddsogp := ‘CN=GP-‘||ddsoou||’-HM-User,OU=Groups,OU=’||ddsoou ||’,OU=DDSO,’||ldap_globals_pkg.GC$ldap_base;
ddsoau := ‘CN=’||ddsogrp ||’-allusers,OU=Groups,OU=’||ddsoou ||’,OU=DDSO,’||ldap_globals_pkg.GC$ldap_base;
dbms_output.put_line(ddsoau);
dbms_ldap.free_mod_array(user_array);
— Add the user to the portal group —
retval := add_in_group (my_session, ddsogp, user_dn) ;
retval := add_in_group (my_session, ddsoau, user_dn) ;
— free LDAP Message
retval := dbms_ldap.msgfree(my_message);
retval := DBMS_LDAP.unbind_s(my_session);
DBMS_OUTPUT.PUT_LINE(RPAD(‘unbind_res Returns ‘,25,’ ‘) || ‘: ‘ || TO_CHAR(retval));
DBMS_OUTPUT.PUT_LINE(‘Resultt -> OK’);
RETURN ‘OK’ ;
end if;
— Handle Exceptions
EXCEPTION
WHEN OTHERS THEN
GN$ErrCode := SQLCODE ;
GC$ErrLib := SQLERRM ;
DBMS_OUTPUT.PUT_LINE(‘ Error code : ‘ || TO_CHAR(GN$ErrCode));
DBMS_OUTPUT.PUT_LINE(‘ Error Message : ‘ || GC$ErrLib);
DBMS_OUTPUT.PUT_LINE(‘ Exception encountered .. exiting’);
RETURN ( GC$ErrLib ) ;
END;
Susan
Delano said
How did you get the certificate imported correctly into your Oracle Wallet. I have exported the certificates from our AD server and have imported into Oracle Wallet. but every time I try to run a connection i get the SSL handshake failed error.
dentists atlanta said
Hi! This is my 1st comment here so I just wanted to give
a quick shout out and say I truly enjoy reading your
blog posts. Can you recommend any other blogs/websites/forums that go over the same topics?
Thanks for your time!
Consignment Clothes said
There are also several important baseball tips on hitting involving the front foot.
Edging the Phillies and was the man that fired Tony LaRussa 1.
John Sterling: Let me get one thing out of 10 for
their team, as well.
crowns castleville cheats said
Write more, thats all I have to say. Literally, it
seems as though you relied on the video to make your point.
You definitely know what youre talking about, why throw away your intelligence on just posting videos to your weblog when you could be giving
us something informative to read?
aumentar penis said
Excellent, what a webpage it is! This website provides valuable data to us,
keep it up.
Lori said
Howdy! Would you mind if I share your blog with my twitter group?
There’s a lot of folks that I think would really enjoy your content. Please let me know. Cheers
raspberry ketone reviews weight loss said
I am in fact grateful to the holder of this website who has shared this fantastic paragraph at at this place.
pure raspberry ketone said
Good day! I know this is kinda off topic but I’d figured I’d ask.
Would you be interested in trading links or maybe guest authoring a blog post
or vice-versa? My website addresses a lot of the same subjects as yours and I think we
could greatly benefit from each other. If you are interested feel free to send
me an email. I look forward to hearing from you!
Terrific blog by the way!
singapore tennis lessons said
Greetings from California! I’m bored to tears at work so I decided to check out your site on my iphone during lunch break. I love the knowledge you present here and can’t wait to take a look when I get home.
I’m shocked at how quick your blog loaded on my cell phone .. I’m not even using WIFI, just 3G .
. Anyways, very good site!
singapore chiropractor said
I was wondering if you ever considered changing the structure
of your website? Its very well written; I love what youve got to say.
But maybe you could a little more in the way of content
so people could connect with it better. Youve got an awful lot of text for only having one or two images.
Maybe you could space it out better?
Apartments in SIngapore said
Greetings! I’ve been reading your blog for a while now and finally got the bravery to go ahead and give you a shout out from Atascocita Texas! Just wanted to mention keep up the good job!
Muscle Maximizer said
My coder is trying to convince me to move to
.net from PHP. I have always disliked the idea because of the expenses.
But he’s tryiong none the less. I’ve been using WordPress on
a number of websites for about a year and am anxious about switching
to another platform. I have heard very good things
about blogengine.net. Is there a way I can import all my wordpress content into it?
Any help would be really appreciated!
raspberry ketone diet meal plan said
Hi to all, for the reason that I am actually keen of reading
this website’s post to be updated regularly. It includes pleasant data.
breast actives long term side effects said
My brother suggested I would possibly like this blog. He was entirely right.
This post truly made my day. You cann’t consider just how a lot time I had spent for this info! Thanks!
breast actives pills said
Hi Dear, are you actually visiting this website on a regular basis, if so after that you will without doubt
get pleasant know-how.
breast actives before and after shots said
I am extremely impressed with your writing skills and also with the layout on your weblog.
Is this a paid theme or did you customize it yourself?
Either way keep up the nice quality writing, it’s rare to see a nice blog like this one today.
breast actives scam said
I loved as much as you will receive carried out right here.
The sketch is attractive, your authored material stylish.
nonetheless, you command get bought an edginess over that you wish be delivering the following.
unwell unquestionably come further formerly again since exactly the same nearly
a lot often inside case you shield this increase.
frontier cable said
Thanks a bunch for sharing this with all folks you really realize what you are talking approximately!
Bookmarked. Please also consult with my web site =). We may have a link change arrangement among us
breast actives reviews said
A fascinating discussion is worth comment. I think that
you should write more on this subject matter, it may not be a taboo subject but generally people don’t discuss these subjects. To the next! All the best!!
paruresis-treatment.com said
A highly-critical report said one in six of those requiring intensive care
after surgery did not get it because of any sort of
health problem or any side effect! This is
not really good though as your gallbladder is removed laparoscopically
or through open wounds. This paruresis female create-up wants to make you conscious with the effects of those people,” we can reach the goal of WHO. We control a master ova bank. Dandelion is useful to use on mucous membranes and reduce inflammation paruresis female in the body. 4 million 2010 revenue.
how to reverse gray hair said
I wander disconsolately into the bedroom, it called the guillotine becomes
lighter in color, eventually turning gray or translucent white.
Mr Ostler, please go on.
tornado weather alerts said
Pretty nice post. I just stumbled upon your weblog and wanted to say that I’ve really enjoyed surfing around your blog posts. In any case I’ll be subscribing
to your rss feed and I hope you write again soon!
Web Site said
Thank you for any other excellent article. The place else may just anyone
get that kind of information in such a perfect way of writing?
I’ve a presentation next week, and I am at the search for such info.
microsoft points generator xbox live download said
I usually do not drop a comment, but I looked through some remarks on this page PL/SQL LDAP over SSL
– Please Test Tyler Muths Blog. I do have a few questions for you if it’s allright. Is it only me or does it appear like some of the remarks look as if they are coming from brain dead individuals? 😛 And, if you are posting on additional sites, I would like to follow you. Would you make a list of all of all your public pages like your twitter feed, Facebook page or linkedin profile?
Tina said
Way cool! Some very valid points! I appreciate you writing this post and also the rest
of the site is also very good.
Wilmer said
great issues altogether, you just gained a brand new reader.
What would you suggest in regards to your post that you made a few days ago?
Any positive?
compare security companies said
Good replies in return of this difficulty with firm arguments and explaining all
on the topic of that.
a slimmer you said
Do you mind if I quote a couple of your posts as long as I provide credit and sources
back to your webpage? My blog site is in the exact same area of interest as yours and my visitors
would truly benefit from a lot oof the information yoou present
here. Please let me know if his okay with you.
Appreciate it!
go to sleep said
Hi to all, because I am in fact keen of reading this
web site’s post to be updated on a regular basis. It
includes fastidious material.
elite review weight said
The producer asserts you have the ability to merely sprinkle it on & see
the pounds vanish!. This is since possibly it could change
the efficacy of the medicines. The large majority of it is increased in India.
Does Xenadrine work said
Hі there just happened upon your website via Bing after I typed in, “PL/SQL LDAP over SSL – Please Test Tyler Muths Blog” or something similar (can’t quite remembeг eхactly).
Anyways, I’m deliɡhted I found it simply because your
content is exactly what I’m looking for (wгiting а university paper)
and I hope you don’t mind if І collect some information from here anԁ I will of couгse credit you as the source.
Thank you so much.
live6 said
Ιt’s remarkable for me to have a web page, which is helpful in support of my knowledge.
thanks admin
Geburtstagsgedichte said
Hey there excellent website! Does running a blog like this require a great deal of work?
I’ve no understanding of coding however I had been hoping
to start my own blog in the near future. Anyhow, if you have any
ideas or techniques for new blog owners please share.
I understand this is off subject however I simply had
to ask. Thanks a lot!
game killer apk said
A motivating discussion is worth comment. I think that you need to publish
more on this subject matter, it might not be a taboo subject but typically people don’t
discuss these subjects. To the next! All the best!!
Raspberry Ketones Bodybuilding said
Hi, I do believe this is an excellent web site.
I stumbledupon it 😉 I may come back yet again since i
have book-marked it. May and freedom is the best way to change, may you be rich and continue to
guide other people.
rummy journey said
rummy journey
PL/SQL LDAP over SSL â Please Test « Tyler Muths Blog
pujcky online bez registru Uhlirske Janovice said
Thanks for the useful information on credit repair on this amazing site. The thing I would advice people is to give up the particular mentality they will buy at this moment and pay out later. Being a society we all tend to repeat this for many things. This includes trips, furniture, along with items we want. However, you’ll want to separate your current wants from the needs. As long as you’re working to boost your credit score you have to make some trade-offs. For example you’ll be able to shop online to save money or you can look at second hand outlets instead of costly department stores intended for clothing.
currency exchange said
Great beat ! I would like to apprentice while you amend
your website, how could i subscribe for a blog site? The account helped me a acceptable
deal. I had been tiny bit acquainted of this your broadcast offered bright clear idea
penomet review said
my website
PL/SQL LDAP over SSL – Please Test