Windows

Synchronize Active Directory with databases


In my day-to-day role as the CIO for Westminster College, my team and I are always looking for ways to make our lives--and the lives of our users--a little easier.  Recently, we had a request to provide more granular email distribution lists.  At the time of the request, we had just three main lists - one for students, one for faculty and one for staff.  The desire was to have lists based on dorm residency, fraternity membership and major/minor.  So far, we've nailed downthe first two with ease.

We decided very early on that maintaining these lists could not be a manual process.  Students move between rooms all the time and it would take a full-time person just to keep up with the changes.  Further, we already have this data in our student information system, and I'm a huge believer in reusing existing information.  Here are the steps we took to meet our goal:

  • Purchased Active Directory Bulk Users from Dovestones Software.  I'll hype these guys all day and night.  They're providing an outstanding and powerful product at a fantastic price.  AD Bulk Users is a product that imports and modifies users in Active Directory.
  • Using the list of supported Active Directory attributes provides in AD Bulk Users' documentation, created a SQL view that pulls the appropriate information from our student information system.  For AD Bulk Users to work, the SQL view field names must match Active Directory attributes.  We're using the extended, user-defined Active Directory attributes for our needs.  Further, we store every user's SAM account name in our student information system and store their student ID in Active Directory so we have an easy way to link the records.
  • Ran AD Bulk against this view to synchronize user information.
  • Using Active Directory Users and Computer, created dozens of query-based distribution groups.  A group was created, for example, for each dorm on campus, and for each fraternity.  The query is based on the extended attributes we created in the previous step.
  • After a few days of testing and manually running the process, automated it using the Windows Task Scheduler.

We have uncovered a couple of problems:

  • Our database isn't perfect.  For example, a student might be listed as living in the wrong dorm.  When they contact us to change it, we send them to Student Life, the department that originates the information.
  • As we add residences, we have to manually create the new query-based distribution groups.  It would be nice to automate.

Even with these minor problems, the solution is fairly elegent and substantially automated and saves us a ton of time while allowing us to be more granular in our communications with students.

About

Since 1994, Scott Lowe has been providing technology solutions to a variety of organizations. After spending 10 years in multiple CIO roles, Scott is now an independent consultant, blogger, author, owner of The 1610 Group, and a Senior IT Executive w...

0 comments

Editor's Picks