February 28, 2021
Hot Topics:

Using ADSI to Mirror Exchange Users

  • By Doyle Vann
  • Send Email »
  • More Articles »

SQL Server icon

You look at your exchange directory and one day you realize that there is a vast amount of data stored there about the users in your organization. One challenge can be deciding how to leverage that information in your relational database applications. You could imbed ADSI (Active Directory Services Interface) calls into program logic, but this would still not allow the data to be joined to other tables or be accessed via other database mechanisms. This article discusses one approach to this problem by showing how a database table can be created on your SQL server and updated on a periodic basis by a background process.

To accomplish the periodic download a VB component will be created that uses ADSI to access the Exchange global address list and download it into a SQL table that mirrors the Exchange information. The features for this approach are available from a combination of ADO, ADSI and LDAP support. This article assumes that you have at least a basic understanding of those technologies. To run the example the client machine that runs the VB code must be on either WIN2000 (which includes MDAC 2.5 and ADSI provider) or WIN 4.0 SP 5 with at least MDAC 2.1 and the ADSI provider installed.

Which Exchange ADSI Object To Use?

First we need to decide which Exchange ADSI object that we will need to access to obtain the information about each person in the Exchange directory. For this purpose we will need to use the Exchange Mailbox object. This is exposed via LDAP as the organizationalPerson object class. We will also need to use some data elements that are in the Exchange Mail-Recipient object exposed as the LDAP person object. The Mail-Recipient object is inherited by the Mailbox object so the data element names for that object will also be available when the Mailbox object is retrieved.

Each data element has both an Exchange object name as well as an LDAP name. Since ADO will be used to access the exchange data through the LDAP exchange provider, the LDAP data element names will need to be mapped to the table column names that will store the data.

What the SQL Table Contains

The SQL table used to store the downloaded Mailbox objects will be called tblOrganizationalPerson. This table will contain one column for each of the LDAP data members that are to be downloaded. Table 1 shows the mapping between the table column names and the LDAP common name that will be placed in the column.

LDAP Common Name Table Column Name
Uid vcExchangeAlias
GivenName vcFirstName
Sn vcLastName
Company vcCompanyName
Street vcStreetAddress
personalTitle vcPersonalTitle
generationQualifier vcGenerationQualifier
houseIdentifier vcHouseIdentifier
department vcDepartment
Manager vcManager
physicalDeliveryOfficeName vcPhysicalDeliveryOfficeName
St vcState
Title vcTitle
Co vcCountry
facsimileTelephoneNumber vcFaxNumber
homephone vcHomePhoneNumber
Initials vcInitials
L vcCity
Mobile vcMobileNumber
Pager vcPagerNumber
postalAddress vcPostalAddress
postalCode vcPostalCode
Secretary vcAssistant
telephoneNumber vcOfficePhone
employeeNumber vcEmployeeNumber
employeeType vcEmployeeType
HomeFax vcHomeFaxNumber
homePostalAddress vcHomePostalAddress
personalMobile vcPersonalMobileNumber
personalPager vcPersonalPagerNumber
Name vcDisplayName
Mail vcSMTPMailAddress

There are two other columns in the table not described in the mapping. First there is intPersonPK. This is the primary key column for the table and is defined as an IDENTITY column so that SQL Server will populate the value for this column automatically. This column then can be used as a foreign key in other table relationships.

Second, there is dtLastFoundInExchange. This column is used to track users that have been deleted from exchange. Once a user has been removed from exchange this date will begin to age. It can then be used for a stored procedure to occasionally remove rows older than n days.

In addition to the primary key, the exchange alias is defined as a unique constraint. This is the index that will be used to look-up each organizational person to see if they already exist in the table or not.

Page 1 of 2

This article was originally published on August 22, 2000

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date