United Kingdom +44 (0) 1424 863 450   hello@simego.com

Update Active Directory from a SQL Database

In this example we are going to use a MS SQL table called ERPUsers but this could easily be any of the extensive list of DS3 connectors.


26 May 2014

Information about employees always resides in at least two places. In the Human Resources (Capital) application (or ERP) and in the company’s Active Directory. Often it is necessary to update data in the company’s Active Directory to reflect changes or structural updates to reflect the HR application. Using DS3 you can automate this process and reduce expensive and error prone rekeying as well as reducing the amount of individuals with access to the Active Directory and Human Resources applications.

  

Connect to your ERP application as the Source (Left Side)

In this example we are going to use a MS SQL table called ERPUsers but this could easily be any of the extensive list of DS3 connectors.

 

Open DS3

 

 image 

 

Click on the Connect to Data Source on the left hand side (Data Source A)

 

image

 

Select ‘Microsoft SQL Server’ as the source provider.

 

 

image

 

Complete the details, selecting the database and SQL Table (this could be a view or a SQL statement)

 

image

 

Once complete DS3 will offer to remember the connection in its connecting library, save it as ‘ERP Users SQL Source’. In addition DS3 extract the tables' schema and display it on the left to assist mapping.

 

image

 

Connect to your Active Directory as the destination (Right Side)

Click on the Connect to Data Source on the right hand side (Data Source B)

 

image

 

Select the Active Directory Users provider. Remember to complete you Active Directory Path correctly. In our case it is DC=Dev,dc=dom.

 

image

 

After completing the provider details save the connection in the library and click OK. DS3 will map any columns automatically that have the same name.

 

image

 

Choose a Key (Click on the tick box in the Key column) so that DS3 can identify the same employee in the source ERP system and the target Active Directory. In this case we are mapping EMPID to EmployeeID. We have also removed all attributes we so not want mapped. To map attributes you can drag and drop them from either side or use the dropdowns provided.

 

image

 

Now we can compare the data in the ERP system to the data in the Active Directory. Click on the ‘Compare A –> B’ button in the task bar to reconcile the two data sources.

 

image

 

This will reconcile the SQL Database and the Active Directory and give a list of actions to bring the two into Sync. Once complete click the ‘Updates’ in the tree view. We are seeing a list of deletes because our SQL source is not as complete as our Active Directory target and therefore we would have to delete items in the AD to bring them into sync. DS3 Disables Deletes and Inserts by default but they can be enables if required. Scrolling across you can see the changes that will be made during a sync in yellow.

 

image

 

Clicking ‘Synchronise’ in the tool bar above the Results tree view will show the Synchronise Dialog. Clicking Start will now update the Active Directory with the details from the SQL Table.

 

image

Once the Synchronise has been completed. You can compare again and there should be no updates to make.

image

 

The advantage of using DS3 is that you can run this as many time as required as DS3 reconciles the source against the Active Directory first before sending changes, If there are no differences then no updates will be sent.


|
Can't find the answers you're looking for?