How to Get on Track with Active Directory

Introduction

I often have to deal with processing of information from Active Directory and as this system is quite widespread, I decided to share with you my experience of working with it.

It all started with me when I gave access to Microsoft Sharepoint. In the employees' requests, accounts came at best, and at worst – something like “To my boss and me”. It initially was very troublesome, and I was feeling like a real admin, sent such “sharp-witted” requests back to its creators for remake. Eventually, it turned into a pile of soaked in anger letters and calls. I even started to wonder if the abbreviation of the Active Directory – AD matched in pronunciation with Russian word “АД” (Hell) was really a coincidence. As a result, realizing that it is time to change the world for the better, I decided to investigate what exactly is to be stored in Active Directory and how to use it for automatically giving of access and other useful things.

The employees' information in the company is stored in two places:

• Active Directory
• Oracle Database

The Oracle maintains the uniqueness, and AD gives the access, i.e., one employee may have several accounts. Here comes the problem of unambiguous definition of an employee according to his account.

The solution of this problem has been found and spread to various tools and programs, actively used within the company. One of the most used tools was a program searching for an employee information.

One of the most used tools has become a program of searching for information about the employee. It looks for an employee or employees by a variety of criteria and gives the most complete information about them, combining data from Active Directory and Oracle. The photos of employees stored in the database are especially popular.

It should be noted that Active Directory integrates not only with Oracle, but also with MS Sharepoint. Due to the found solution, there appeared an application capable of quickly giving access for a group of Active Directory users to the Sharepoint portal pages, as well as deleting permanently inactive for various reasons users.

The problem was solved in two ways:

1. To get information on one employee — DBMS_LDAP in Oracle
2. To download all the accessible profiles and see the contents of the Active Directory in general, we used the C# code. 

While using the C# code, consider the following (library System.DirectoryServices.dll):

• The property of DirectorySearcher.PageSize must be different from 0, if you want the function FindAll() to return all the entries.
• It is obligatory to call Dispose(), for instance DirectorySearcher. 

There also is a property DirectorySearcher.SizeLimit, which sets the maximum quantity of entries in the returned result. Its default value is 0, which means we take its value from a server, where it is usually set as 1000. You can play with the property SizeLimit and see how the result of the function changes (http://stackoverflow.com/questions/90652/can-i-get-more-than-1000-records-from-a-directorysearcher-in-asp-net).

The above link contains the sample code, used by yiled, that from the point of view of the .NET is better, but I have not used it in the example below for clarity of the algorithm:

Code Samples

DBMS_LDAP Oracle

function GetDataByUser(userAccount VARCHAR2)
return VARCHAR2 is

l_ldap_host VARCHAR2(256) := 'host';
l_ldap_port VARCHAR2(256) := 'port';
l_ldap_user VARCHAR2(256) := 'user';
l_ldap_passwd VARCHAR2(256) := 'password';
l_ldap_base VARCHAR2(256) := 'ldap_path';

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_attr_name VARCHAR2(256);
l_ber_element DBMS_LDAP.ber_element;
l_vals DBMS_LDAP.string_collection;

ret VARCHAR2(256);

begin

   DBMS_LDAP.USE_EXCEPTION := TRUE;
   l_session := DBMS_LDAP.init(hostname => l_ldap_host,portnum => l_ldap_port);
   l_retval := DBMS_LDAP.simple_bind_s(ld => l_session,dn => l_ldap_user,passwd => l_ldap_passwd);
   l_attrs(1) := 'postalcode';
   l_retval := DBMS_LDAP.search_s(ld => l_session,base => l_ldap_base,scope => _
   DBMS_LDAP.SCOPE_SUBTREE,filter => '(&(objectClass=user)(cn=' ||userAccount || ')_
   (mail=*))',attrs => l_attrs,attronly => 0,res => l_message);
   
   IF DBMS_LDAP.count_entries(ld => l_session, msg => l_message) > 0 THEN
      l_entry := DBMS_LDAP.first_entry(ld => l_session,msg => l_message);
      l_attr_name := DBMS_LDAP.first_attribute(ld => l_session, ldapentry => l_entry, ber_elem => l_ber_element);
      l_vals := DBMS_LDAP.get_values (ld => l_session, ldapentry => l_entry, attr => l_attr_name);
      ret := l_vals(0);
   END IF;
   
   l_retval := DBMS_LDAP.unbind_s(ld => l_session);
   return ret;
end GetDataByUser;

?#
        static void Main(string[] args)
        {
            var listDict = ActiveDirectoryTraversal();
            var d = listDict[0];
        }
        private static List<Dictionary<string, object>> ActiveDirectoryTraversal()
        {
            List<Dictionary<string, object>> ret = null;
            var dep = new DirectoryEntry();
            dep.AuthenticationType = AuthenticationTypes.FastBind;
            dep.Path = "LDAP://yourpath";
            
            using(DirectorySearcher ds = new DirectorySearcher(dep))
            {
                ds.Filter = "(&(objectClass=user))";
               //ds.SizeLimit = 5;
                ds.PageSize = 100;
                using (SearchResultCollection results = ds.FindAll())
                {
                    var e = results.Count;
                    if (results != null && results.Count > 0)
                    {
                      ret = SaveData(results);
                    }
                }
            }
            dep.Close();
            return ret;
        }
        private static List<Dictionary<string, object>> SaveData(SearchResultCollection results)
        {
            var ret = new List<Dictionary<string, object>>();
            for (int i = 0; i < results.Count; i++)
            {
                var res = results[i];
                var dict = new Dictionary<string, object>();
                foreach (var e in res.Properties.PropertyNames)
                {
                    if (!dict.ContainsKey(e.ToString()))
                        dict.Add(e.ToString(), res.Properties[e.ToString()][0]);
                }
                ret.Add(dict);
            }
            return ret;
        }

Conclusion

Ability to work with Active Directory and access it directly from the Oracle database significantly simplified the work of our department programmers. In the beginning, as an experiment, I downloaded the content of Active Directory into a database table. Then, to my surprise, I found a number of stored procedures that worked with this table. It turned out that the developers addressed it, too lazy to figure out the package DBMS_LDAP. This precedent stimulated me to write this and give examples of how you can work with Active Directory.