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.

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.

The Visual Basic Code

Now that we have a table defined to receive our organizational person object we need to write a small VB application that will utilize the ADSI LDAP provider that will be accessed via ADO. This will return a standard ADO recordset that will expose each organizational person as a row in the recordset. As each row is processed the tblOrganizationalPerson table will either have a row inserted if the exchange alias for the person is not found, otherwise the data for that person will be updated.

The VB code will perform the following steps:

1. Establish a connection to the exchange LDAP data provider.
2. Establish a connection to the SQL server database
3. Create a command to return the organizational person object
4. Loop through the exchange recordset and update the tblOrganizationalPerson

Establish the Exchange connection

After the exchange command object has been created, the following code prepares the command to use the connection we just opened, sets up the command text and then executes the command returning a recordset.

pconnExchangeDirectory.Provider = "ADSDSOObject"
pconnExchangeDirectory.Open "ADs Provider", 
        "dc=YOURSITE,cn=YOURUSERID", "YOURPASSWORD"

Note that you will need to have access to an exchange user account that has Service Admin permissions for this connection to work. Since this example is processing the global address list, that user would need permission to that object as well. For any ADSI object manipulation beyond this example the user would need fairly broad access to the exchange directories. You will need to provide your exchange site name and the user id and password that have Service Admin permission and modify the above code accordingly.

Create a command to return the organizational person object

This example only uses stored procedures to access the tblOrganizationalPerson table and has no need to return the actual rows. Therefore the only thing that is needed is a connection to run stored procedure on. This is created in the following code.

pconnOrganizationalPerson.CommandTimeout = 600
pconnOrganizationalPerson.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=NAMEOFYOURDATABASE;Data Source=NAMEOFYOURSQLSERVER"

You will need to modify the open method to use your database name in the Catalog parameter and your server name in the Data Source parameter. Also, this example is using integrated NT/SQL server security. If you wish to use standard SQL server logins the connect string would need to be modified to turn off Integrated Security and the user id and password would need to be added.

Create a command to return the organizational person object

After the exchange command object has been created, the following code prepares the command to use the connection we just opened, sets up the command text and then executes the command returning a recordset.

Set pcmdExchangeDirectory.ActiveConnection = pconnExchangeDirectory
pcmdExchangeDirectory.CommandText = "<LDAP://YOUREXCHANGESERVERNAME>;(objectClass=organizationalPerson);" & _
    "cn,name,uid,personalTitle,street,generationQualifier,houseIdentifier,Company,department," & _
    "givenName,manager,physicalDeliveryOfficeName,sn,st,title,co,facsimileTelephoneNumber," & _
    "homephone,initials,l,mobile,pager,postaladdress,postalcode,secretary,telephoneNumber," & _
    "employeeNumber,employeeType,homeFax,homePostalAddress,personalMobile,personalPager," & _
    "mail;" & _
    "subtree"

pcmdExchangeDirectory.Properties("Page Size") = 100
pcmdExchangeDirectory.Properties("Timeout") = 60 'seconds
pcmdExchangeDirectory.Properties("searchscope") = 2 
pcmdExchangeDirectory.Properties("Cache Results") = False 

Set prsExchangeDirectory = pcmdExchangeDirectory.Execute

The command text is in the form of a native LDAP dialect. We could of elected to use the SQL dialect if desired. The LDAP provider will process commands of either type. The functionality of the two dialects is about the same. The command string breaks down as follows:

1. The first parameter (<LDAP://YOUREXCHANGESERVERNAME>) defines the exchange object we wish to process. In the case of this example we wish to process the global address list (GAL) which is the default container for any exchange server in a site. We can specify any exchange server in the site since all servers keep a common GAL.
2. The next parameter ((objectClass=organizationalPerson)) defines a filter for the type of exchange objects we wish to access from the GAL. This is a very simple filter that simply states that we only want organizationalPerson objects. We could have added other filter conditions to only return certain users or certain changed dates, etc. For this example though, we want all of the objects of type organizationalPerson so no further filter keywords are needed.
3. The third parameter is a list of the data elements we wish to retrieve. This directly equates to the list found in the earlier table.
4. The last parameter is (subtree). This parameter tells the LDAP provider to traverse any trees that exist to return all the objects. Without this parameter only the first level objects would be returned.

Note that the LDAP provider exposes several command properties that can be used to influence how the provider returns the data requested. The four properties used in this example are page size, timeout, searchscope and cache result. The page size and timeout properties are tuned to work well when downloading large numbers of rows from very busy exchange servers. The page size setting of 100 will limit the provider to accessing the exchange server at 100 rows per access. This will also help to keep the command within the 60 second timeout specified even when the server is very busy. Since we will not be traversing the recordset except in a forward only manner, caching is not beneficial and turning it off will conserve memory. The searchscope value of 2 let’s the command know that we are using a subtree search in our LDAP syntax.

Loop through the exchange recordset and update the tblOrganizationalPerson

The rest of the example is straightforward VB/ADO code. A loop is performed on the exchange recordset. For each record read the stored procedure procSelectOrganizationalPerson is called passing the exchange alias as a parameter. The records affected parameter is used to determine if the select found a row or not. If a row was not found then the stored procedure procAddOrganizationalPerson is called passing all of the data elements as parameters. For each parameter the MakeDoubleQuote routine is called to be sure any strings that contain a single quote are changed into two consecutive single quotes to the SQL compiler will accept the parameters.

If the exchange alias is found then the stored procedure procChangeOrganizationalPerson is called passing the same parameters as the add stored procedure. The only difference is that this stored procedure performs an update instead of an insert and also updates the dtLastFoundInExchange column with the current date and time.

Conclusion

This article should give you a fairly simple example of how to access your exchange stores via ADO and LDAP. This example is just a simple start to the many things that can be accomplished through ADSI. By combining the power of a relational database with the ability of ADSI to manipulate other types of information stores, automation of many common administrative tasks can be accomplished.

If you wish to get a head start on the topics discussed in this article and give the example a try on your on MS Exchange/SQL servers. Not only does this download include the Visual Basic project, but I’ve also included the SQL Server script files in order to help.