SQL Developer 19.1 unable to use connection type ldap with OUD

Due to a tip from a work colleague, I came across a changed behaviour of the latest SQL Developer release. It affects the connection type LDAP respectively the use of an LDAP directory for the database name resolution. After specifying one or more LDAP servers it should actually be possible to select the corresponding context in the drop down list. But as of SQL Developer 19.1 this drop down list remains empty as you can see in the following images. As far as I can tell, this problem only occurs when using Oracle Unified Directory and the latest release of SQL Developer.

You can enable debugging in the SQL Developer to search for errors. But it is far easier to have a short look in the OUD access log. In the access log, you will instantly notice an error as you can see in the following excerpt of the access log.

[02/Jul/2019:04:34:51 +0000] CONNECT conn=276 from=172.20.0.1:55224 to=172.20.0.2:1389 protocol=LDAP
[02/Jul/2019:04:34:51 +0000] BIND REQ conn=276 op=0 msgID=1 type=SIMPLE dn="" version=3
[02/Jul/2019:04:34:51 +0000] BIND RES conn=276 op=0 msgID=1 result=0 authDN="" etime=0
[02/Jul/2019:04:34:51 +0000] SEARCH REQ conn=276 op=1 msgID=2 base="" scope=sub filter="(objectClass=orclContext)" attrs="cn,DN"
[02/Jul/2019:04:34:51 +0000] SEARCH RES conn=276 op=1 msgID=2 result=50 message="The request control with Object Identifier (OID) "1.2.840.113556.1.4.319" cannot be used due to insufficient access rights" nentries=0 etime=3
[02/Jul/2019:04:34:51 +0000] SEARCH REQ conn=276 op=2 msgID=3 base="" scope=one filter="(objectClass=orclContext)" attrs="cn,DN"
[02/Jul/2019:04:34:51 +0000] SEARCH RES conn=276 op=2 msgID=3 result=50 message="The request control with Object Identifier (OID) "1.2.840.113556.1.4.319" cannot be used due to insufficient access rights" nentries=0 etime=1
[02/Jul/2019:04:34:51 +0000] SEARCH REQ conn=276 op=3 msgID=4 base="" scope=base filter="(objectClass=orclContext)" attrs="cn,DN"
[02/Jul/2019:04:34:51 +0000] SEARCH RES conn=276 op=3 msgID=4 result=50 message="The request control with Object Identifier (OID) "1.2.840.113556.1.4.319" cannot be used due to insufficient access rights" nentries=0 etime=1
[02/Jul/2019:04:34:51 +0000] UNBIND REQ conn=276 op=4 msgID=5
[02/Jul/2019:04:34:51 +0000] DISCONNECT conn=276 reason="Client Disconnect"

There is an issue with an LDAP control as stated in error message: The request control with Object Identifier (OID) “1.2.840.113556.1.4.319” cannot be used due to insufficient access rights The control 1.2.840.113556.1.4.319 better known as Paged Results Control
 allows the client to control the search results. In particular it is used to iterate through the search results a page at a time. And why is this of interest in connection with the SQL Developer?

When using the LDAP connection type, the SQL Developer uses an anonymous LDAP query to query the Oracle context and database name. Starting with SQL Developer 19.1, Oracle seems to use Paged Results Control, which makes sense if you have a large number of LDAP entries. By default Oracle Unified Directory does not allow the Paged Results Control for anonymous connections. Simplest solution to this problem is to allow Paged Results Control also for anonymous connections. For this you have to change the access control handler and the global act either by dsconfig or via OUDSM. The MOS Note 1932191.1 does provide the step by step solution how to modify the global act via dsconfig, OUDSM or by modifying the config.ldif. The latter is not recommended, because this requires a restart of the OUD server. Furthermore, the direct modification of config.ldif is only necessary in a few cases, e.g. when resetting the root user respectively directory manager password.

Procedure to modify the global aci via dsconfig:

  • Start dsconfig and directly modify the act of the Access Control Handler
dsconfig -h oud -p 4444 -D "cn=Directory Manager" -j $PWD_FILE -X set-access-control-handler-prop
  • Select the property global-aci
  • Select remove one or more values
  • Select the # that has Authenticated users control access which contains the 1.2.840.113556.1.4.319 OID)
  • Select use these values
  • Select finish and apply any changes to the Dsee Compat Access Control Handler
  • Start again dsconfig to add the new aci for the Access Control Handler
dsconfig -h oud -p 4444 -D "cn=Directory Manager" -j $PWD_FILE -X set-access-control-handler-prop
  • Select the property global-aci
  • Select add one or more values
  • When prompted with Enter another value for the global-aci property [continue]:, enter 
    (targetcontrol="1.3.6.1.1.12|| 1.3.6.1.1.13.1 ||1.3.6.1.1.13.2 ||
1.2.840.113556.1.4.319 ||1.2.826.0.1.3344810.2.3 ||
2.16.840.1.113730.3.4.18 ||2.16.840.1.113730.3.4.9 ||
1.2.840.113556.1.4.473 ||1.3.6.1.4.1.42.2.27.9.5.9")
(version 3.0; acl "Authenticated users control access"; allow(read) userdn="ldap:///anyone";)
  • Select use these values
  • Select finish and apply any changes to the Dsee Compat Access Control Handler

The entire adjustment can also be done directly with two dsconfig commands.

First remove the global-aci for userdn all: (Make sure to remove the line wraps when copy/paste the aci)

dsconfig -h oud -p 4444 -D "cn=Directory Manager" -j $PWD_FILE -X -n set-access-control-handler-prop \
--remove global-aci:"(targetcontrol=\"1.3.6.1.1.12 || 1.3.6.1.1.13.1 || 1.3.6.1.1.13.2 || 1.2.840.113556.1.4.319 || 1.2.826.0.1.3344810.2.3 || 2.16.840.1.113730.3.4.18 || 2.16.840.1.113730.3.4.9 || 1.2.840.113556.1.4.473 || 1.3.6.1.4.1.42.2.27.9.5.9\") (version 3.0; acl \"Authenticated users control access\"; allow(read) userdn=\"ldap:///all\";)"

Add the new global-aci for userdn anyone:

dsconfig -h oud -p 4444 -D "cn=Directory Manager" -j $PWD_FILE -X -n set-access-control-handler-prop \
--add global-aci:"(targetcontrol=\"1.3.6.1.1.12|| 1.3.6.1.1.13.1 ||1.3.6.1.1.13.2 ||1.2.840.113556.1.4.319 ||1.2.826.0.1.3344810.2.3 ||2.16.840.1.113730.3.4.18 ||2.16.840.1.113730.3.4.9 ||1.2.840.113556.1.4.473 ||1.3.6.1.4.1.42.2.27.9.5.9\") (version 3.0; acl \"Authenticated users control access\"; allow(read) userdn=\"ldap:///anyone\";)"

Now the SQL Developer can load the LDAP context and database name.

The question now is whether this change is a bug or a feature. In any case, it would make sense if the SQL Developer could alternatively allow to enter the Oracle context directly and thus avoid the anonymous query.

Some links related to this blog post: