Oracle PL/SQL: Using DBMS_LDAP To Query Active Directory

It's always useful to be able to retrieve details from Active Directory when working within an Oracle Database (I'll do a separate post later on how we use LDAP records to update our employee email addresses stored in Oracle).

Oracle have created a package called DBMS_LDAP, I have to say when I first found it I thought it was new but it has apparently existed in one form or another since Oracle 9i. It is fairly self explanatory to use, the main (useful) function is SEARCH_S which does the querying. The function takes the following parameters;

SYS.DBMS_LDAP.SEARCH_S Parameter List

As you can see calling this isn't going to be simple based on the complexity of the parameters (RAW, TABLE OF ...) but actually the DBMS_LDAP package has a number of helpful definitions that mean it's not quite so daunting as it may initially appear so here is a simple example;

DECLARE
  v_SearchUsername         VARCHAR2(40) := '@@';
  v_LDAPSession            DBMS_LDAP.SESSION;
  v_LDAPAttributeQueryList DBMS_LDAP.STRING_COLLECTION;
  v_LDAPQueryResults       DBMS_LDAP.MESSAGE;
  v_BerElement             DBMS_LDAP.BER_ELEMENT;
 
  v_FunctionReturnValue    PLS_INTEGER;
BEGIN
  :Result := '';
  v_LDAPSession := DBMS_LDAP.INIT('@LDAP server@', '@port@');
  v_FunctionReturnValue := DBMS_LDAP.SIMPLE_BIND_S(v_LDAPSession,
                                                   '@domain@\@user@',
                                                   '@password@');
  v_LDAPAttributeQueryList(1) := 'mail';
  v_FunctionReturnValue := DBMS_LDAP.SEARCH_S(
    ld       => v_LDAPSession,
    base     => '@base location@', -- "DC=xx,DC=yy=DC=zz"
    scope    => DBMS_LDAP.SCOPE_SUBTREE,
    filter   => 'samaccountname=' || v_SearchUsername,
    attrs    => v_LDAPAttributeQueryList,
    attronly => 0,
    res      => v_LDAPQueryResults);
  v_FunctionReturnValue := DBMS_LDAP.COUNT_ENTRIES(v_LDAPSession,
                                                   v_LDAPQueryResults);
  IF DBMS_LDAP.FIRST_ENTRY(v_LDAPSession, v_LDAPQueryResults) IS NOT NULL THEN
    :Result := DBMS_LDAP.GET_VALUES(
                 v_LDAPSession,
                 DBMS_LDAP.FIRST_ENTRY(v_LDAPSession, v_LDAPQueryResults),
                 DBMS_LDAP.FIRST_ATTRIBUTE(
                   v_LDAPSession,
                   DBMS_LDAP.FIRST_ENTRY(
                     v_LDAPSession,
                     v_LDAPQueryResults),
                   v_BerElement)) (0);
  END IF;
  v_FunctionReturnValue := DBMS_LDAP.UNBIND_S(v_LDAPSession);
END;

You need to do a little updating in order to make this work for your configuration (entering the server, a active directory user account with permission to do the query, your base location, etc) but on our system this runs in a tiny fraction of a second.

NOTE: This patch of code is only returning the first record returned. You will encounter problems if the user name exists in different domains, but that issue hasn't arisen for us and I guess most companies will probably be ok.

The v_berElement is required by the FIRST_ENTRY call in order to allow you to iterate through the results but as we're just interested in the first record returned it is declared, used, but never referenced again in the code above.

Final comment, if you are running 11g you are likely to get;

"ORA-24247: network access denied by access control list (ACL)"

When you attempt to run the command (unless your user has already been granted LDAP access). You need to update the access control list granting access to the connected user. The solution is readily available on Google (but I might create a post over the next few days as I need to do it myself!).

Installing Active Directory Tools Under Windows 7

This blog post is a step-by-step guide to installing the Active Directory Tools (i.e. Active Directory Users and Computers) on a Windows 7 machine. It has been tested on Windows 7 Enterprise but will probably work with Professional or Ultimate as well - Home users it will not work (but then why are you wanting to administer AD from a home machine??!!)

First of all you need to download the software from Microsoft. In the Microsoft Download Centre these are called "Remote Server Administration Tools for Windows 7" a direct link to the download page is given below;

http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=7d2f6ad7-656b-4313-a005-4e344e43997d

If you scroll down to the "Files in This Download" section of the page you'll see two files. Depending on whether or not you're running 32-bit or 64-bit Windows 7 you need to pick the correct file. If you're unsure of which version you're running then go to the start button, right-click "Computer" and then select "Properties". You'll see something like this;

System Information (64-bit/32-bit)

Look at the "System type:" value and you'll see what version of Windows you're running.

If you're running 32-bit then you need to download the file which starts "x86..." (currently this is "x86fre_GRMRSAT_MSU.msu" but it might change). For 64-bit users you need to download the file which begins "amd64..." (currently this is "amd64fre_GRMRSATX_MSU.msu") - this is true even if you're running a non-AMD 64-bit processor. The reason for this I'll leave Microsoft to explain ...

Once you've got the file install it (it's a standard KB update file).

After it's been successfully installed go to the Start Menu > Control Panel and select "Programs";

"Turn Windows Features on or off" under "Programs and Features"

The "Windows Features" dialog box will be displayed, scroll down to "Role Administration Tools" (under "Remote Server Administration Tools") and select the the following items;

"Windows Features" dialog

Click "OK" to make the changes.

In order to make finding these under the Start Menu a little easier right-click the Start Button and select "Properties";

Taskbar and Start Menu Properties

Select "Customize ..." and then scroll down the list until you see "System administrative tools" and choose where you want the tools to display;

Customize Start Menu

Under the Start Menu you will now see an "Administrative Tools" option, under this you'll see the new AD Tools;

Active Directory Start Menu Items

NOTE: Sometimes a reboot is required to pick up these changes!