Group Management:  A Suite of Tools for Automating the Population of Active Directory Groups from an SQL Database

 

This talk was presented by Kim Nystrom and John Barclay at the Graduate School of Library and Information Sciences builing on October 20th 2005.  30 people attended.  Thanks to GSLIS for hosting this event.

Summary

Active Directory (AD) groups are a powerful means of controlling access and permissions to everything from web servers, to filespace, to exchange groups, to database objects. The College of Education uses a suite of in-house developed Group Management tools to automate the population of AD groups. This toolset capitalizes on existing College staff and Banner student data, automating a potentially onerous task, while also providing a user-friendly web interface for designated College power users to manually maintain AD groups. AD groups are more accurate, network administrators are relieved of tasks, and the investment in central databases provides yet more returns.

The purpose of this brownbag is to showcase the potential of collaborative work that bridges central data stores with Active Directory utilization. Technical details, such as code and architecture, will be provided. Note that this tool is sufficiently complicated that attendees will not be able to implement without moderate adjustments to code.

Presenter

Kim Nystrom, College of Education Database Administrator and founder of Datamasters Group (http://www.ed.uiuc.edu/datamasters/)

Example of Tool in Use

An exchange enabled AD group composed of all college graduate students is maintained by a weekly query to the College student database. As students enroll and drop, the AD group is adjusted automatically, sending the group owner a summary of changes made. College staff rely upon the group for email. Intranet permissions are also granted based on this group's members . Other types of automated groups include classes of faculty and staff, unit staff, and class rosters.

Features

• the option of an automated reconcile of an AD group or simply email notification that the AD group may need manual adjustments
• transaction log of group membership changes
• ability to have manual exceptions to the sql driven logic
• population of one AD group from another AD group
• job scheduling ranging from hourly to weekly to on demand

Intended Audience and Skill Level

This tool crosses database, network administrator, webmaster and programmer audiences. A multidisciplinary set of skills is required to replicate, to include ADSI, ADO, and LDAP programming, sql query knowledge, and AD query and administration skills.

Deliverables

• step through roles and scenarios
• overview of architecture, application flow and features
• screen shots
• core vbscript code with commentary
• stored procedure code with commentary

Tools Used

• Microsoft SQL Server
• Vbscript
• Windows Task Scheduler
• Microsoft Access – Project