How To

Update Active Directory from a SQL Database

In this example we are going to use a SQL table but you can always use a different connector.

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 Data Sync 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 Source (ERP application)

In this example we are using a SQL table called "ERPUsers" but you can always use a different connector to suit your needs.

Open a new Data Sync project and connect to the SQL table using the SQL Server provider.

Connect to SQL Server

You can choose to save this connection to your SQL Database to the Connection Library to use again at a later date by clicking Connect & Create Library Connection.

Connect to your Target (Active Directory)

Now connect your target side to Active Directory by using the Active Directory Users provider. Remember to complete you Active Directory Path correctly. In our case it is dc=internal,dc=simego,dc=com.

Connect to Active Directory

Configure the Schema Map

Data Sync will automatically map any columns that have the same name. However please configure the schema map as you need to, to only include the columns you want.

Make sure to select a key column so that Data Sync can identify the same employee in the source as the target. In this case we are mapping EmpID to EmployeeID.

Schema Map

Run the Compare and Sync

Now we can compare the data in the ERP system to the data in the Active Directory. Click Compare A -> B to reconcile the two data sources.

Compare

This will reconcile the SQL Database and the Active Directory and give a list of actions to bring the two into Sync. You can click onto any of the results to preview the changes that will be made. The green + will show you the list of additions to be made, the blue diamond will show you any updates, and the red - will show you any deletions (items that do not exist in your source but do in your target).

Please note that deletions are disabled by default to prevent any unwanted removal of data. You will need to enable them if you do wish to remove this data from your Active Directory.

Click Synchronise and start to begin the synchronisation and update the Active Directory with the details from the SQL Table.

Once the Synchronise has been completed. You can run the compare again to make sure that there are no updates left to make.

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

You can go a step further and schedule this project to run either using the Run Tool or Ouvvi.