Migration\Sync Application I am building
So I am building an application for a customer that helps the customer populate their Active Directory Users from a HR database.
The HR database is running PeopleSoft/JDEdwards/Oracle what ever they call it nowadays ;), on a Sun Microsystems machine.
There are four tiers to the application architecture and three different types of databases that are being used in the solution.
First of all we have Oracle which stores all the PeopleSoft data, we then have a SQL 2005 Database where the data is provisioned (and yes I know this screams MIIS but I will explain later why we didn’t use MIIS), and finally we have Active Directory Database.
The data is exposed to us in the form of an Oracle Data View, which is the selected user fields that we need to import in to Active Directory, we then run a SQL 2005 SSIS package to connect to the Oracle database (using the 64-bit version of the Oracle 8.1.7 drivers), and suck all the data from that is exposed in that view. Usually this is 10 000 records (this is for bulk import).
Once we have this data in SQL 2005, we have a few Stored Procedures (SPs) that run to fix the data, and to add a few columns that we need. Some of the data is “Dirty” and not in the format that we need so we have to adjust the data (i.e. cleaning white spaces and unnessasary symbols from the data)
Once we have done basic cleaning we process:
The Active Directory username policy that I proposed in my design was to use First_Name+ First_Letter_Of_Surname, where there is a conflicting username i.e. you have two Joe Blogs, you would take First_Name+ First_And_Second_Letter_Of_Surname. Now this seemed pretty good until you run into a very interesting issue. What if you have ten Joe Blogs (in a data set of 10 000 that’s very possible), you would reach the end of the username logic by the fifth user. So by the fifth Joe Blogs you would have a complete JoeBlogs as the username. So I had to go through and add logic to append numbers to the end of the usernames so you would get JoeBlogs1, JoeBlogs2.
So for ten users (Joe Blogs) with the same name the following usernames would be generated:
The proposed design explains a highly delegated model where each business unit has its own OrganizationalUnit (OU) and has a predefined amount and structure of children OUs.
These Business Unit OUs (BUOUs) have a specific format that they follow. The forest has a OU called <Company_Name> Business Units, under that OU there is a list of each Business Units OUs.
<Company_Name> Business Units
One of the predefined containers under the Business Unit<n> container is a child container Users i.e.
<Company_Name> Business Units
Now what the sp_UserADLocationLogic SP does is associate each user record (that we got from PeopleSoft database Oracle), has an additional column called ActiveDirectory_DN.
Now the logic here is that each user has a Location_Descr column which maps to a Business Unit name i.e.
I then have a separate table called BusinessUnitInformation, this table is populated the first time we downloaded information from PeopleSoft database. I ran a TSQL Distinct on the Location_Descr column from the Users DataSet (returned from PeopleSoft), this gives me a fresh list of Business Units. I then ran a query that builds the DistinguishedName (DN) for that Business unit. We know the logic behind how the OU structure created so we can do “OU=Users,”’ + NewBusinessUnit +’”,OU=<Company_Name> Business Units,DC=CompanyDomain,DC=Com”, this automatically generates the DN for the Users container in the Business Units OU, i.e.
OU=Users,OU=Business Unit1, OU=<Company_Name> Business Units,DC=CompanyDomain,DC=Com
OU=Users,OU=Business Unit2, OU=<Company_Name> Business Units,DC=CompanyDomain,DC=Com
OU=Users,OU=Business Unit3, OU=<Company_Name> Business Units,DC=CompanyDomain,DC=Com
Now I know where to put the user based on his Business Unit location.
I know what you thinking, what if the PeopleSoft admins add a new Business Unit, well I thought of that. We have a default container in Active Directory called “UnprovisionedObjects”, under that OU we have a Users container.
Now in the BusinessUnitInformation table (where we store BusinessUnitName -> ActiveDirectory_DN mapping) I have a record called Default. This default record has a matching record that is the DN of the Users OU in the UnprovisionedObjects OU.
Now every time we get new data from the PeopleSoft database, I check for new Business Units, if I find a new Business unit, I add that Business Unit name to the BusinessUnitInformation Table, but the ActiveDirectory_DN that is assigned to that Business Unit is the default DN, OU=Users,OU=UnprovisionedObjects, OU=<Company_Name> Business Units,DC=CompanyDomain,DC=Com, this way the application doesn’t throw an error for not knowing where to put the user, it knows it’s a new Business Unit and puts the unprovisioned users in the OU=Users,OU=UnprovisionedObjects, OU=<Company_Name> Business Units,DC=CompanyDomain,DC=Com container. A mail will then be fired off by SQL Mail to tell the administrator that a new Business Unit was created and that users have been provisioned in the directory for him.
Now that we have taken care of “dirty data”, usernames and the Users “home” (Active Directory DN) they are ready to be imported.
There is a .net 2.0 application that runs as a service, this app clicks off at a specified time, it connects to the SQL database and builds a complete list of in memory Active Directory Objects (this is a object I have built). Now the beauty about this application is that it uses a “map” file that you build (which is XML), this maps a SQL column name to a User Class Attribute LdapDisplayName. So at run time I have a generic object, this object is then set to the type of classSchema object (i.e. User, Computer etc) this classSchema is specified on the map file, at this time the program then dynamically sets properties (the properties are not actually defined they are dynamically added to the in memory object), once all this logic is run then the in memory Active Directory Object is created. Some final checks are done on the object and then imported into the directory. Each Active Directory object has a respective DN so I know exactly where to put the object.
Now I know many People are asking why not MIIS, well simply the PeopleSoft MA will only be coming out later and we needed a solution now. The company also didnt want to extend the scope of the current project to include MIIS.
The good side of this, well the entire app runs off a .net 2.0 library which MIIS can make use of. In phase 2 of this project we will be looking at MIIS and now to intergrate it into the new environment.
For now I have the generic App that can take any data from SQL and import that object into AD (to the exact location I might add ;)) that you need :)
As usual with most things, I do not know everything in the world and I called on the best SQL and .net guys and below is a list of the people that helped me
Joao Paulo Carreiro - .net
Ernst Kuschke - .net
Doug Nelson ( Doug I cant find your site please let me know so I can update the link) - SQL
Thank you guys!!!!