Syncing Active Directory with my Application

Question

I've inherited an application with its own user database and login authentication scheme which cannot be replaced.

There is now a need to integrate with Active Directory.

I've implemented mixed mode (forms and AD) authentication, where I'm having issues is keeping the users in sync

I've added a column to our user database for the active directory account name. The admin will need to create a user in AD, Create it in our application, and then in our application, select the AD user that matches...

This feels dirty and naive, what better ways are there to do this.

Answer 1

One thing I recommend is not storing the account name in the database, but the guid for the AD user account. Then if some admin changes something the connection remains even though the username has changed.

You can use a CLR library within sql server and a sql job to periodically sync accounts between AD and the user database.

You could use a group, which the clr library can look for, pull in all the members, and then automatically sync - that is update/create/deactive accounts according to their ownership of the AD group. Then your admin will only need to create the user in AD, give them access to the group, and wait for the job to kick off. (or go kick it off manually)