.Net, Active Directory, Programming, Software, SQL, SQL Tips, Transact-SQL, tSQL

Notes on Writing T-SQL LDAP Queries Using ADSI OPENQUERY

Active Directory
Active Directory

When you are working with Active Directory on your network, then it can be difficult to get a clear picture for how your particular instance of Active Directory is structured. I’ve been writing T-SQL LDAP queries using ADSI OPENQUERY and have been having fun trying to find where some of the values I’m looking for are stored.

I have found quite a bit of piecemeal information, but nothing comprehensive to explain which fields one can select for a particular filter or how best to find these fields. For example, if you are using the ObjectClass=’computer’ filter, then you will need to select very different return values than if you are using the ObjectClass=’user’ filter. Trying to guess what return values have data is an exercise in futility. Your environment’s Active Directory set-up likely contains several standardized attributes and likely also some custom attributes. You’ll need a good Active Directory browsing tool to track all of these down and identify which attributes you need to query.

Active Directory Browsing Tools

There are several good Active Directory viewers that come for free with Windows so there’s really little need to look any further. Google suggests downloading and using JXplorer, but when I tried it out, I found it unnecessarily difficult to use compared to the default viewers that come with Windows.

If you don’t find the Microsoft Active Directory tools included with your version of Windows then you may need to activate the tools in your Add/Remove Software -> Windows Components computer administration functions. The best bet is to activate all active directory related tools, try them out, and pick your favorite. One caveat is that you may need to separately download the MS Active Directory components depending on your version of Windows. For example if you are running Windows 7 then you will need to download the Windows Server 2008 Active Directory components before you can activate them as Windows components.

ADSIEDIT.MSC

After trying out several options for Active Directory viewers, my favorite is ADSIEDIT.MSC.

To start the viewer up just enter ADSIEDIT.MSC into your run command (start->run (on Windows 7)). You’ll be browsing through the details of your  Active Directory structure before you know it. This tool has been great for me to find what I need and to properly structure my LDAP queries.

If you are viewing properties of various Active Directory entities in ADSIEDIT.MSC, then make sure you change your filter options. By default you will see results with and without data, but this really is unnecessary clutter that will just end up hiding things that you are looking for. In the bottom right of the properties window you will see a Filter button. Click this and make sure that the option Show only attributes that have values is checked.

Points to Keep in Mind

There are two main limitations to keep in mind when you are writing T-SQL LDAP queries using ADSI OPENQUERY:

  1. You can’t return more than 1000 rows by default using OPENQUERY.
  2. You can’t return multi-part attributes like description using OPENQUERY.

With these two caveats in mind you should have a better idea if you want to keep querying your Active Directory using T-SQL or if you want to try to find an alternate method such aswriting a program in C# or VB.NET.

If you prefer to continue using T-SQL LDAP queries then you can circumvent problems with the 1000 maximum row limit in T-SQL by applying enough filters to your queries. This, of course, isn’t always feasible. For the most part T-SQL LDAP should be able to handle most situations, but if you do need more advanced query power then you should consider writing an application to query Active Directory in a language such as .NET.

Advertisements

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