Synchronise Active Directory Users with SharePoint List
The following article will cover how you can use Data Synchronisation Studio to synchronise your Active Directory Users to SharePoint to create contact and user lists.
Below you will find a couple of solution ideas for how you can use Data Sync to connect Active Directory to SharePoint or other connectors:
- Create and Sync a SharePoint Contact List from Active Directory
- Create and Sync a SharePoint Employee Directory from Active Directory
- Connect an ERP Database to Active Directory (e.g. SQL Database)
- Other Self Service solutions to synchronise updates from SharePoint to Active Directory
In this guide we will be copying Active Directory Users to a new or existing SharePoint list. If you already have a list available in SharePoint we will cover connecting directly to that, or alternatively we also cover how to create a new SharePoint List from Data Sync to hold your data.
Requirements
Before getting started you need to ensure you have the following:
- Access to Active Directory
- Access to SharePoint (we connect to SharePoint Online in this example)
- Windows 10 or Windows Server
- Downloaded & Installed Data Synchronisation Studio
If you do not have Data Synchronisation Studio you can get a free evaluation edition. To test the automation using Ouvvi please contact us to upgrade your trial key.
Connect to Active Directory
To get started open Data Sync and click onto the Connect Datasource link in the Source Window (Data Source A). This will open the connection window and we can connect to Active Directory. Expand the options for Active Directory and select the Active Directory V2 - Users/Contacts/Groups/Computers connector.
You will need to enter in the LDAP path to your Active Directory and user credentials in order to connect. The path can be either the full LDAP Path or simply the server name/domain controller name e.g. LDAP://dc01/dc=demo,dc=simego,dc=com or LDAP://dc01.
For this example we are connecting to a specific OU by adding the OU to the path, note that you will need your domain controller name to be listed: LDAP://dc01/OU=Users,DC=lab,DC=simego,DC=com
In this scenario we are connecting to Users, however if you wanted to connect specifically to Contact records you can change the DefaultAttributes property to Contacts. This will change the default LDAP filter to return only contacts.
For more details on connecting to Active Directory take a look at our documentation pages.
You will notice that there are columns available in the source window that are prefixed with DS- these are Data Sync columns that return data from AD that usually require additional work. Data Sync does this work for you so that you can simply map the columns and go. These are more for when Active Directory is the target and you want to synchronise to these properties (e.g. Setting a Manager, Enabling & Disabling Accounts etc) but will still return the data you need as normal when set as the source.
Connect to SharePoint
We can now move onto connecting to SharePoint. If you do not already have a list created you can create one from Data Sync, take a look at the next section to find out how.
To connect to your SharePoint List click onto the Connect Datasource link in the Target Window (Data Source B) to open the connection window. Now browse to SharePoint > SharePoint Online.
You need to enter in the URL to your SharePoint site where your list can be found. In this example our list can be found in a subsite called Test, so the URL will look like: https://company.sharepoint.com/Test.
- To connect to the main parent site the URL would look similar to:
https://company.sharepoint.com - To connect to a subsite of a subsite the URL would look similar to:
https://company.sharepoint.com/subsite/subsite2
Click onto the Authorize Connection button and follow the onscreen instructions to sign in to SharePoint and authorise the connection. The you can find the full connection details here>.
Once you are connected you can select your list from the dropdown menu and set your delete behaviour as you need to. Click onto Connect & Create Library Connection to save the connection for future use. This will add a connection to your SharePoint site/sub-site in the connection library so that you can quickly access all the lists and libraries in the future.
You can now map the source and target columns.
Create a New SharePoint List (Optional)
If you do not already have a SharePoint list to hold your Users you can create one from Data Sync. There is a requirement that you must have saved a connection to your SharePoint Site in the Connection Library in order to use this feature. If you have already saved your connection to SharePoint then you can follow the steps below to create a new list.
To start you need to add the columns you want to the schema map and remove any you do not want. The columns in the new SharePoint list will be created based on the columns in the schema map.
Now is a good time to configure the schema map so that the data types are what you expect and a key column has been defined. The key column needs to be unique to each user, in this example a good option is the DS-SAMAccountName column.
Then go to Tools > Create SharePoint List. This will open a window where you can configure the new list.
Select your SharePoint connection from the dropdown, make sure that the list type is set to Generic List, enter in a name for your new list, and select a column to be the Title Column in SharePoint. To create the list click OK. This will load your new list as the Target connection.
Check that the source columns are matching to the right target columns and a key column has been selected, you can then move onto the data comparison step.
Map the Source & Target Columns
You now need to select the columns you want to include in the synchronisation. By default all the properties will be added to the schema map, so it might be easiest to select everything in the schema map (ctrl + A) and delete so that you can start afresh.
Add the Active Directory properties from your source window either by dragging and dropping or by selecting the checkbox. Then link these to their corresponding column in SharePoint.
Make sure to select a key column that uniquely identifies each user from Active Directory. A good option is the DS-SAMAccountName column.
Your mapping might look similar to this:
Column Doesn't Exist in SharePoint
If a column doesnt already exist in SharePoint you can quickly create one from Data Sync. The requirement to do this is that you must have saved the connection to your SharePoint site in the Connection Library.
If you have saved your connection then you can create a new column by going to Tools > Create SharePoint Column.
Select your connection from the dropdown and then select your list. Type in a name for the column and then click OK to add the column.
You will need to refresh the connection to SharePoint so that you can see the new column. To do this click onto the refresh icon in the Target Window toolbar. You can now add this column to the schema map.
Compare and Preview the Results
Now run the comparison to see the users that need adding to the SharePoint list. To do this click onto the Compare A>B button in the main toolbar.
You will then be presented with the comparison results where you can see any additions, updates or detentions to be made. Updates will be highlighted to show changes wherever they exist and by default deletes are disabled but will still show in the results. To enable deletes go to the target connection properties and set EnableDelete to True.
Click onto the records with the green plus to see a preview of the data to be added.
Synchronise
The next step is to copy the user data to SharePoint. Click onto the Synchronise button in the compare window and then click Start to begin the sync.
Once the sync has run your users will now be visible to view in your SharePoint List. If we browse to SharePoint we can see that the users added to the list.
Make sure to save your Data Sync project so that you can use it again in the future.
Automate & Schedule
Now that you have configured your synchronisation from Active Directory to SharePoint you have a couple of options available to automate the running of your projects.Run via the Run Tool
The Run Tool is an additional program that comes linked to Data Sync and enables you to build out your data integration jobs.
You can use this to group Data Sync projects that need to run in order and add additional step types such as adding an status report to email you when the project has run and if there were any failures. Each step is run in the order it appears in the list and you can apply conditional rules so that the next step can run if the previous one succeeded or had data changes for example. To find out more please see our Run Tool Documentation.
To open your project in the Run Tool, in your Data Sync project go to Tools >Open in Run Tool.
You can then click onto the green Run button to check it runs as expected. If there are data changes you will be provided with a count of how many items were added updated or deleted
Run via the Command Line
Another option is to Run the Run Tool project or single Data Sync project from the command line. To do this pass the path to the project file like this:
Run Tool Project
Data Sync Project
Automate with Windows Task Scheduler
You can then schedule your Run Tool project to run when you need it to using Windows Task Scheduler. For the full details on how to do this see our Task Scheduler Documentation.
Automate with Ouvvi
An alternative option, with more scheduling capabilities is to use Ouvvi Automation Server. Ouvvi enables you to fully schedule and manage all of your Data Integration Projects, from Data Sync projects to SQL Statements to Powershell Scripts.
It provides full logging and documentation capabilities to fully manage your integration operations.
To find out more see our Ouvvi Documentation or send us an email.
Additional Notes
LDAP Filter
You may wish to apply an LDAP Filter to your Active Directory Connection to return a specific subset of users. You can find the LDAP Filter field in the connection properties of the Source Window.
To edit the default filter click onto the ellipsis (...) to open the dialog box and then enter in the filter you need.
Make sure to refresh your connection, by clicking onto the refresh button in the datasource toolbar to apply the filter.
For more information on the types of filters you can use please see our documentation page on LDAP Filters.
Using Active Directory Properties not listed in the columns
If want to use custom Active Directory attributes or attributes that are not listed in the default column list you can quickly add them from the connection properties.
To do this scroll to the Settings section and click on the ellipsis (...) in the Attributes field.
This will open the AttributeInfo Collection Editor.
Click onto the Add button and then enter in the name of the attribute (internal Active Directory name) into the AttributeName field and a friendly name into the DisplayName field.
Make sure to complete the property fields so that they match the attribute you are adding. Once you are done click OK to add the attribute and make sure to refresh your connection to Active Directory to make the attribute avaialble as a column.
Below is a table of all the fields with a description and example to help you configure your attribute:
| Property | Description | Example |
|---|---|---|
| AttributeName | The name of the attribute in Active Directory | userprincipalname |
| DisplayName | The name you want to represent this attribute | Logon Name |
| DataType | The DataType of the Attribute | System.String |
| MaxLength | The maximum length of the Attribute (returned by AD after refresh) | 255 |
| MinLength | The maximum length of the Attribute (returned by AD after refresh) | 0 |
| ValueConverter | Select the converter to use to convert the value returned into the value you need | DateTimeParser |
Aggregating OUs into One DataSet
As Data Sync can be extended with custom code you can collate all of your organisational units (OUs) into one dataset using Dynamic Columns.