Extracting user information from Active Directory with C#

Solving a problem of how to query user information from Active Directory there were discovered the next issues:

  • LDAP (Lightweight Directory Access Protocol) can be used in T-SQL queries only if they are ran on SQL Server installed on Domain Controller, that is a problem – both DBA and SysAdmin don’t like anyone connects to production server.
  • Executing LDAP queries from C# (convenient from SSIS Script Task) allows to pull data from a server without RDP to it.
  • Custom multi-valued attributes cannot be read with a help of SQL Server.
  • T-SQL dialect for LDAP doesn’t support data type conversion and variating WHERE clause can’t help to get data from custom attributes.

Also:

  • If some property in AD is not set (i.e. equal to NULL) it will not be returned at all. Pretty unusual behaviour for any querying engine.
  • Custom attributes and their values can be easily parsed.

Sample C# script listing all user information including multi-valued attributes:

public class UserInformation
{
 public bool GetUserInfo(out List<string> userInformation, string userName)
 {
 userInformation = new List<string>();
 var valueReturn = false;
 try
 {
 const string pathNameDomain = "LDAP://DC=<domain>, DC=local";

var directoryEntry = new DirectoryEntry(pathNameDomain);

directoryEntry.RefreshCache(new string[] { "msExchExtensionCustomAttribute1" });
 directoryEntry.RefreshCache();

var directorySearcher = new DirectorySearcher(directoryEntry)
 {
 Filter = "(&(objectClass=user)(sAMAccountName=" + userName + "))"
 };

var searchResults = directorySearcher.FindAll();
 string sTmp = "";
 valueReturn = searchResults.Count > 0;

foreach (SearchResult searchResult in searchResults)
 {

foreach (var valueCollection in searchResult.Properties.PropertyNames)
 {
 if (searchResult.Properties[valueCollection.ToString()].Count == 1)
 {
 userInformation.Add(valueCollection.ToString().ToUpper() + " = " + searchResult.Properties[valueCollection.ToString()][0].ToString());
 }
 else
 {
 sTmp = "";
 for (int i = 0; i < searchResult.Properties[valueCollection.ToString()].Count; i++) // Loop through List with for
 {
 sTmp = sTmp + " [" + searchResult.Properties[valueCollection.ToString()][i].ToString() + "]";
 }
 userInformation.Add(valueCollection.ToString().ToUpper() + " = " + searchResult.Properties[valueCollection.ToString()].Count.ToString() + ": " + sTmp);
 }
 }
 }
 directoryEntry.Dispose();
 directorySearcher.Dispose();
 searchResults.Dispose();
 }
 catch (InvalidOperationException iOe)
 {
 MessageBox.Show(iOe.Message);
 }
 catch (NotSupportedException nSe)
 {
 MessageBox.Show(nSe.Message);
 }
 finally
 {
 }
 return valueReturn;
 }
}
...
public void Main()
{
 string sResult = "*";
 List<string> userInfo = null;
 UserInformation UsInf = new UserInformation();
 UsInf.GetUserInfo(out userInfo, "<login>");
 for (int i = 0; i < userInfo.Count; i++)
 {
 sResult = sResult + "\r\n" + userInfo[i];
 }
 sResult = sResult + "\r\n*";
 MessageBox.Show(sResult);
 Dts.TaskResult = (int)ScriptResults.Success;
}
Advertisements

About fdtki

Sr. BI Developer | An accomplished, quality-driven IT professional with over 16 years of experience in design, development and implementation of business requirements as a Microsoft SQL Server 6.5-2014 | Tabular/DAX | SSAS/MDX | Certified Tableau designer
This entry was posted in programming and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s