Sunday, February 27, 2005

Using SQL to query ActiveDirectory

Finding information in ActiveDirectory using the standard Windows 2003 Server tools is not as easy as database programmers would prefer, like using SQL select statements to read data from tables.

If you have SQL Server 2000 you can use AD as a linked server, and then apply all your SQL knowledge to query AD using SQL select statements with where, order by, group by and having clauses.

SQL Server 2000 makes AD accessible as a SQL view against the linked server using the ADSI OleDB provider.

MSDN describes how to configure and use this distributed query mechanism. Use the ADSIEdit MMC snap-in to explore the attributes for different AD objects.

1 comment:

Morten Dybdahl said...

Useful information. We will use this method in a large MS CRM project to provide Directory information in MS CRM. I.e. copy AD over to a SQL Server table, identify the Diff from table and the MS CRM entity (addressboook), and then run a scheduled EXE file to update MS CRM the "legal" way. In the long run we will migrate this solution to a MIIS based solution.