dcsimg
December 9, 2016
Hot Topics:

Using ADSI to Mirror Exchange Users

  • August 22, 2000
  • By Doyle Vann
  • Send Email »
  • More Articles »

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.

VB code





Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

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

Sitemap | Contact Us

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