How To

Update Active Directory from a SQL Database

The following article will cover how to update active directory user information from a SQL table. We use a SQL table as the source but you can always use your ERP system to update active directory fields instead.

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 as our source, however you can always use a different connector to suit your needs.

Begin by opening a new Data Sync project and connecting to your 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. Full details on using the Active Directory connector can be seen here.

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 source data to the data in the Active Directory. Click Compare A -> B to reconcile the two data sources.

Compare Results

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 Active Directory with your SQL data.

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.