-1

I want to start a new HR web application based on asp.net mvc-5 & Entity Framework. as it is almost the case Users inside the HR system will exists inside the AD, and when a new user join our company we will create a new user inside the AD, then the HR dept will add the user to the HR system. but my question is how i can integrate my web application with AD users. i am thinking of the following approach:-

  1. To create a table inside my system named ADUsers, with these fields; userID,FName,LName,Email .

  2. to define a sync job which will run on daily basis, which will query all the AD users and populate the DB table accordingly. the sync job will add new users, delete removed users, update user info as defined inside the AD. In this case i can mange the users inside my system easier. for example i will have a module named Person , which should display all the Person AD Info (such as Fname, Lname,email address, etc) + their annual leave count, hire date, sick leave count which are HR specific etc...

so can you advice on this approach please?

closed as unclear what you're asking by amon, Bart van Ingen Schenau, GlenH7, user40980, Kilian Foth Dec 17 '14 at 13:42

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    which database product are you using for your datastore? – kevinsky Dec 15 '14 at 14:18
  • sql server 2008 R2 – john Gu Dec 15 '14 at 14:50
1

You will probably need three tables: User tables with a primary key and the usual first name, last name etc Roles table with roles specific to your application (eg Administrator, Power User, User, read only) UserRoles with the user primary key, roles primary key, date created, date last modified, enabled (NUMBER (1) default 1 with check constraint that this field can only have the values of 0 or 1

As you indicated you will also need a package/procedure to query Active Directory for the users who have roles that are of interest to your application. The usual insert/update methods are required. Keep in mind that you never want to delete a user for an intranet application if there is any logging so when someone leaves the user table is updated to indicate that enabled = 0.

You indicate that you are using SQL server, that makes it easier than some other databases. A query such as this will get you started:

SELECT * FROM OpenQuery ( ADSI, 'SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber FROM ''LDAP://DOMAIN.com/OU=Players,DC=DOMAIN,DC=com'' WHERE objectClass = ''User'' ') AS tblADSI ORDORDER BY displayname

Keep in mind that there are many fields in Active Directory. The less you ask for the better. Depending on how you access AD you may find a record limitation of no more than 1000 records can be returned. Fields that you mention such as "annual leave count, hire date, sick leave count" should be in separate tables as the functionality is not connected to Active Directory

I don't know of any other practical way aside from a sync job. Your system and AD are separate and you do not want to tie the two together closely. Most AD data is best described as "Slowly changing" so a sync job on five minute intervals works for many applications.

1) You asked if this is valid approach to:

  • create a new DB table inside my system named ADUsers with these columns; UserID,Fname,Lname,Email address.
  • define a Sync job which will query the AD users and populate the ADUsers table accordingly

    • I have seen this approach taken in numerous windows web and client server systems

2) what do you mean by " Your system and AD are separate and you do not want to tie the two together closely"

  • you want to minimize the points of failure. Your system should work when the sync process is not working due to unexpected failure. Active Directory should work if your sync procedure fails.
  • thanks for the valuable reply. now we will be managing user's roles inside the HR system, not inside the AD. so we will have 2 tables as you mentioned (Role + UserRoles), but their data will be managed within our HR system, in all ways this is not my original question. My question is about managing the Users info, as users are managed from AD, but i want to query them inside my own database table using sync job.. So what do u think about this approach?; mainly for creating a DB table which got populated using a sync job, where the sync job will query the Ad and populate the table accordingly? – john Gu Dec 15 '14 at 14:15
  • thanks for the edit,yes i know that HR specific field will be stored on different tables. Regarding the query you wrote, where i need to write the query ? as part of the Sync job to populate my own DB ? not sure that i understand your edit,, can you adivce ? – john Gu Dec 15 '14 at 15:07
  • i can use WebConfigurationManager which comes with asp.net , which allow me to query AD inside my application, in all ways the technical method to query AD is not my question,, but my question is regarding the approach from Architecture point of view to manage AD users inside my HR system. i am thinking on defining a sync job , and my question is about this approach and if there are other approaches which might be better – john Gu Dec 15 '14 at 15:13
  • so do u recommend my previous approach; 1) create a new DB table inside my system named ADUsers with these columns; UserID,Fname,Lname,Email address . 2) define a Sync job which will query the AD users and populate the ADUsers table accordingly ? second question, is this approach widely used in other applications ? third question what do you mean by " Your system and AD are separate and you do not want to tie the two together closely" ?thanks agian for you valuable help.. – john Gu Dec 15 '14 at 16:51

Not the answer you're looking for? Browse other questions tagged or ask your own question.