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.

Programming Exchange 2003 using .NET

We have several projects where we need to manage e.g. Exchange public folders and distribution lists from our own code, such as SPS web parts (really ASP.NET user controls hosted in a web part such as SmartPart). There are 21 different technologies available from Microsoft for programming Exchange 2003 (see official list), thus a choice must be made, also with regards to selected API being supported by .NET (see supported list).

We do not want to use the common, but not good, approach to install Outlook on the web server or on the Exchange Server to use CDO to implement our logic. Outlook is a client tier application, and CDO belongs on the client tier. The same applies to all Office applications, just try to use Word as an automation server in a web solution to produce documents from templates. Very bad application design indeed.

After a bit of experimenting with a selection of the 21 APIs, we now use these APIs against Exchange 2003 and ActiveDirectory:

• CDOEXM
• ADSI
• System.DirectoryServices (the only native .NET component, the others are COM)
• CDOEX
• Exchange OleDB (ExOleDB)

You might wonder why we use the ActiveDirectory components (ADSI and DirectoryServices). Several of the entities that an Outlook user thinks of as part of Outlook or Exchange, are actually spit into two different storages: Exchange for mailboxes and public folders, and ActiveDirectory for contacts, distribution groups and more. AD also handles the authorization for the different objects. To be able to make code that e.g. creates a mail enabled contact (AD contact, remember), you need to use both AD and Exchange components. I recommend checking out the ADSIEdit MMC snap-in to explore the structure of AD and Exchange.

Note that CDOEXM/CDOEX must run on the Exchange server, or on a computer where the Exchange Administrative Tools have been installed (and in the same Active Directory organization). The Exchange server by default has CDOEXM/CDOEX installed (note that just copying and registering the DLLs will not work). We have chosen the former, because the latter have a long list of prerequsites for the target computer, thus using the Exchange server as the location of our .NET components is simply the easiest approach.

In order to call the CDOEXM code on the Exchange server from your WebForm or WinForm application, you can use .NET Remoting or .NET web services. We have chosen the latter, as it is simply the easiest way to implement a distributed solution for intra-system "remote procedure calls". This approach with a specific service for all our Exchange and AD logic also have several other benefits, such as making it easy to set the identity used to access AD, mailboxes, etc. Use the above mentioned ADSIEdit tool to view and set permissions for AD and Exchange objects.

Always remember that the Exchange domain (myco.com) might not be the same as your AD domain (myco.local), as this might cause you some grief when trying to understand and use the rather poor code samples provided on MSDN.

Warning: If you try to use CDOEXM code on a computer that do not have the Exchange Administrative Tools properly installed, you will at run-time get a cast exception (InvalidCastException, "specified cast is not valid") when trying to use .NativeObject to get a CDOEXM object from a .NET AD object, e.g. when your code must call MailEnable() on an AD user, AD contact or AD distribution group.

A final warning: Watch out for the Exchange RUS when mail enabling external contacts, more on this in a later posting.

Welcome to my blog

I work as a solution architect at Objectware in Oslo, Norway (a part of Itera Consulting Group: www.iteraconsulting.com), and will share my experiences on several Microsoft servers and solutions with you, such as:

• MSCRM 1.2
• Exchange Server 2003
• ActiveDirectory
• SQL Server
• Office 2003, including Outlook and InfoPath
• SharePoint Portal Server and Windows SharePoint Services (SPS and WSS)
• Information Worker Bridge (IBF 1.5)

I addition I plan to post other useful stuff needed when architecting, designing and programming solutions based on the products listed above. My postings will be focused on solving problems that you can encounter in your projects.