Microsoft Active Directory SharePoint Lists Integration

Microsoft Active Directory data can be integrated codeless with native SharePoint lists using the Layer2 Business Data List Connector. LDAP queries via OLEDB are used to connect. Please take a look here for more supported systems and applications.​ In case you are looking for Online data integration, you will find the right tool here.

Benefits of Microsoft Active Directory Integration in SharePoint

• Very easy to setup in a few minutes: Create a SharePoint list, click "Connect to external data source" in the list settings, select the data provider, enter connection settings and data query as shown below.
• No installation / modification on Active Directory: No programming, no additional tools.
• Connected data always up-to-date: The connected data updates automatically in background (via SharePoint Timer Job), or alternatively, on-demand (Action Menu / Ribbon Button, URL, via workflow, API).
• One-way connection: You can generally write-back the changes made in SharePoint to the external data source automatically with full CRUD (Create / Update / Delete) functionality. The SharePoint list can act as a full-featured front-end for external systems - depending on data provider. In case of Active Directory it's one-way (AD => SharePoint) only.
• Well-known BCS "external list" issues and limitations are completely solved: ALL list features are to you. Views, sorting and grouping, filters, calculated fields, search, managed metadata. Lookups, additional columns and attachments can be created as normal. All kind of lists can be used, e.g. contacts, tasks, calendar, or custom lists. You can take external data offline via Outlook. In case of Active Directory using a SharePoint contact list to cache the external data is especially helpful.
• Workflows and notifications on external data change: List workflows and change notifications per RSS or email can be used to take business actions in SharePoint, when external business data records are changed.
• Application logging, reporting, and notifications: A SharePoint list ist used to store settings and log information. SharePoint item versioning and workflows can be used to manage reporting and notifications. Direct notification per email in case of errors is supported as well.
• Highest Security, best performance, easy to maintain: SharePoint Secure Store can be used to store security relevant configuration information safely in one central place. Users are working with the SharePoint lists as an external data cache with highest security and performance.
• 100+ more external systems supported: Layer2 Data Providers included (e.g. for SharePoint/Office 365, Exchange, Dynamics, OData, XML/RSS, SOAP), vendor specific data providers can be used (e.g. SQL Server Oracle, mySQL etc.), 3rd party data providers also supported, e.g. for ERP/CRMs, Facebook or Twitter. See here for supported systems and applications.

Microsoft Active Directory Specific Configuration Settings

To connect to Microsoft Active Directory the data source entity must be configured as follows in the Layer2 Business Data List Connector:

Fig. 1 - Example connection configuration to connect to Active Directory via OleDb.

• Create a list in your SharePoint on-premises to cache the external Active Directory data. Click "Connect to external data source" in the General List Settings, select the data provider, enter connection settings and data query as shown below.
• Please select the OleDb Data Provider as data source. Refer to ADSDSOObject later on. Both should be installed on any Windows OS.
• A valid connection string looks like this: Provider=ADSDSOObject; User Id=myUsername; Password=myPassword;
• Please note the optional page size parameter in connection string for longer lists. In some cases the page size must be set for the AD as well to work as expected.
• See here for more about ADSI queries. A valid query can look like this:
  
  SELECT displayname, title, name, objectSID, ADSPath, givenName, sn, cn, company, department, l, mail, telephoneNumber From 'LDAP://myserver.mydomain.lokal/OU=MyOU, OU=Users,OU=MySubOU, DC=MyDomain,DC=lokal' WHERE objectClass='user' AND objectCategory='Person'
• Please verify the select statement. If there are any issues, please check your access rights first (execute as admin). Start with a very basic query and increase complexity step-by-step. Select as few fields as required to increase performance. Use the data preview to check out the result of your query.
• Primary Key: Include the unique ADSPath field in your query and use it as a "primary key" (unique column value) for synchronization.
• The data provider does not support DISTINCT operations, e.g. to create a list of departments. But you can sync to any SQL database or Microsoft Access first using the Layer2 Cloud Connector to have such type of operations available there. You can sync to SharePoint in a 2nd step from SQL to push e.g. departments to a SharePoint lookup list.
• Alternatively there is an 3rd party AD / LDAP Data Provider available from RSSBus / CData. But there are also issues with write access in this case.

Do you have any issues to connect? Please contact [email protected] for next steps.

Microsoft Active Directory Connection Details 

​Provider: 
.Net Framework Data Provider for OleDb

Connection string sample: 
Provider=ADSDSOObject;User Id=myDomain\myUsername;Password=myPassword;Mode=Read;Bind Flags=0;Page Size=1000;

Select Statement sample:
SELECT displayname, title, name, objectSID, ADSPath, givenName, sn, cn, company, department, l, mail, telephoneNumber From 'LDAP://myserver.mydomain.lokal/OU=MyOU, OU=Users,OU=MySubOU, DC=MyDomain,DC=lokal' WHERE objectClass='user' AND objectCategory='Person'

Microsoft Active Directory Integration Known Issues

• Note that some fields are binaries and do not have a ToString conversion implemented in ADSI. Those fields cannot be managed using the connector.
• Date fields are returned as "System._ComObject" by the provider. There is no workaround for this so those fields cannot be managed using the connector.
• Note that the Microsoft ADSI data provider is read-only. No write-operations supported. You will receive an error message like this: "Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information."
• Error reading from entity 'Active Directory: 'ADSDSOObject' failed with no error message available, result code: -2147016669(0x80072023). The message directly comes from the Microsoft ADS data provider / driver, not product related. You can google for specific reasons.
• You may also want to consider changing MaxPageSize in Active Directory using ntdsutil.exe. See How to view and set LDAP policy in Active Directory by using Ntdsutil.exe for more details. Please also set Page Size Parameter in connection string, e.g. …; Page Size=1000; …

Ready to go next steps?