Querying Active Directory through SQL

Lately I’ve had quite a lot of interaction with Active Directory, using multiple tools and methods to get the data I require. As a SQL developer, I was very pleased to find out that active directory could be queried through SQL, allowing data from active directory to be joined on data from our database, allowing queries across multiple operational domains.

This is a quick introduction to querying Active Directory using SQL – these queries
work in both SQL 2000 and 2005.

To query AD through SQL it is first necessary to create a linked server to Active Directory using the ADSI provider. This can be achieved as follows:



Next we query the linked server using the openquery command in SQL. The
below example selects all the users in the Accounting Employees organizational unit
on the irwinj domain.


Finally, if we want the same group of employees, but this time we need
to filter to show only active users, we can use bit-masking on the userAccountControl
to filter the group as follows:


NOTE: A major drawback when querying AD (regardless
of the method used) is that the default AD server setting imposes a return limit
of 1000 objects. If a query returns more than 1000 objects, this list will be cut
short. It is not recommended to increase this default return value, so we must query
around this! The obvious method, and SQL best practice, is to provide the most narrowing
filter possible to your SQL query, but if all else fails there are other possibilities
(http://blogs.msdn.com/ikovalenko/archive/2007/03/22/
how-to-avoid-1000-rows-limitation-when-querying-active-directory- ad-from-sql-2005-with-using-custom-code.aspx)

Comments

Anonymous said…
Hi,

I just don't get this thing to work!

I have a domain controller:
'DC-01'

so the procedure would be:
EXEC master.dbo.sp_addlinkedserver
@server = N'ADSI',
@srvproduct=N'Active Directory Services',
@provider=N'ADsDSOObject',
@datasrc=N'LDAP://DC-01'


Whenever I try to launch a query I always get the same error:

Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "my query" for execution against OLE DB provider "ADsDSOObject" for linked server "ADSI".
February 5, 2008 at 11:32 AM
Jason said…
Tom,
That is correct. In my example i bind to the domain itself, but your code should allow you to bind directly to the domain controller - in fact, i have used precisely the same code (save the actual domain controller) on another server and it worked like a charm.

Is your error occcuring on creation of the linked server, or on a query you perform over that linked server? If you want to send me more details I'll be happy to try and work this out with you.
Thanks
Jason
February 5, 2008 at 1:31 PM
Post a Comment

Popular posts from this blog

Excel - Adding an existing Pivot table to the data model

Image
  I've been working more with, and continue to be amazed by, Power Pivot. I recently ran into an issue where I needed to add the data for an existing Power Pivot report to the Data Model. There is a checkbox that makes this easy when creating a PivotTable: As so often happens, the analysis grew in scope/importance and I found myself needing to add the data to the data model. While it's not obvious how, there is a way to add an existing Pivot table to the data model. There's a More Tables... link in the PivotTable Fields pane.  Clicking on the  More Tables... results in a dialog confirming that you want to Create a New PivotTable .  Doing so will result in the creation of the new PivotTable in a new sheet. By default the data is added to the data model. You'll likely lose some formatting but for anything other than a trivial PivotTable, this should save effort, as it did for me.
Read more

Mirth

Image
Introduction Let's talk about Mirth. At it's simplest, Mirth  link is an Open Source HL7 interface engine. HL7 (Health Level 7) is an NPO involved in the development of healthcare standards and the HL7 v2.x and HL7 v3 standards are commonly used in Medical Information for data interchange - including but not limited to clinical, financial, logistical and administrative messaging. I first ran across Mirth over a year ago on a personal quest to find an alternative to our client's legacy HL7 application. Academically, I wrote a number of lexical analysis tools using lexx, yacc, bison and JavaCC and knew the challenges of writing and maintaining complex parsers. I was simply looking for something that could parse HL7 for us, and perhaps log messages and provide a little more transparency than our application currently allowed.  A year later and our legacy application is still going strong (read: my proposal was declined), but Mirth is being used in production, far away from th
Read more

Getting Started with Mirth (Part 1)

Image
This is the first post in a 2 part series. The second post is here Recently I posted about my positive experience when using Mirth by Webreach as an open source communication layer between one of our in-house applications and a third party and proprietary web service interface. That post received a large amount of attention (relative to my other posts :o) ) so now I will post a quickstart guide to creating a similar setup. In this post I will go over the very basics of using Mirth (please dig in and play around with some more advanced options/configurations), specifically I will demonstrate how to use Mirth to read data from a database, package the data up in a SOAP envelope, send the data to a web service and process the SOAP response. I'll also show you how easy it is to monitor channels once deployed. Once you've installed and opened Mirth for the first time you'll be presented with an empty dashboard - but it wont stay empty for long. This is where you'll s
Read more