SQL Tips by Namwar Rizvi

August 30, 2009

How to Query Active Directory (AD) from SQL Server

There can be a scenario where you will want to query Active Directory, directly from your stored procedure. This can be achieved by creating a linked server to your target Active Directory and then querying it through OPENQUERY functionality. Following is an example to achieve this:

Step 1: Add Linked Server for Active Directory

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource'

Step 2: Query the Active Directory

SELECT * FROM OpenQuery
(
ADSI,   --Name of the linked server for Active directory, created in step 1
'SELECT title, displayName, sAMAccountName, givenName, telephoneNumber, facsimileTelephoneNumber, sn   --Fields you want to retreive
FROM ''LDAP://DC=TestDomain''    --Name of your Active Directory Domain
where objectClass = ''User'''         --Type of objects you want to query e.g. User, Person etc...
)

NOTE: The order in which columns will return will be exactly reversed of what you mentioned in your query. For example, in above query we have mentioned 
title, displayName…….,facsimileTelephoneNumber, sn but the resultset will return as
sn,facsimileTelephoneNumber……,displayName,title.
There is no reason of this behaviour and it is just how Active Directory OLEDB Provider works.

Advertisements

1 Comment »

  1. When running these scripts i still get the following error.
    (890 row(s) affected)
    Msg 7330, Level 16, State 2, Line 1
    Cannot fetch a row from OLE DB provider “ADSDSOObject” for linked server “ADSI”.

    The same story with openquery…And it seems that nobody has a solution beside changing maxpagesize in ad server and set it to 50000 instead of 1000
    Or the known vb or c# scripts…

    Doesn`t any body has a solution to query the rows in the AD? so you can make a work arround for this?

    Hope any body can help with solving this challenge(As far as i see on i.net noby solved it in SQL openquery)

    Thanks in advance.

    Grt,
    Job

    Comment by Job de Bob — September 21, 2010 @ 4:42 PM | Reply


RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: