How to connect Sharepoint lists directly to Active Directory data to access users or devices?

The SharePoint Business Data List Connector is used to simply connect a SharePoint list with external data, e.g. from databases, CRM or ERP systems.

You can connect a custom SharePoint list (no Web part) directly with external data from Active Directory to access users or devices using the SharePoint Business Data List Connector (BDLC).

You can set the connection string, the select statement and the primary keys (if any) simply in the SharePoint list settings dialog. Here are some sample connection strings and select statements of the OLE DB provider 'ADSDSOObject' for Active Directory access.

Connection String

Provider=ADSDSOOBject; User Id=Mydomain\myUser; Password=myPwd; Mode=Read; Bind Flags=0; Page Size=1000;

Start with an high privileged user account. If no account is given, the access rights of the currently logged in user are used if accessing the data manually in the list settings. Just be sure to not run into a double hop problem in this case. For updating data in background the access rights of the respective service account are used.

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.

Select Statement

Example:

SELECT ADSPath, givenName, name, title, telephoneNumber From 'LDAP://DC=Yourdomain, DC=COM' WHERE  objectClass='user' AND objectCategory='Person'

Primary Key

Include the unique ADSPath field in your query and use it as a "primary key" (unique column value) for synchronization.

Known issues & workarounds

• Note that some fields are binaries and do not have a ToString conversion implemented in ADSI. 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; …
• In some cases it could be helpfull to directly adress the domain server, e.g.
  SELECT name, ADsPath From 'LDAP://mydomainserver.mydomain.lokal/'  WHERE objectClass='user' AND objectCategory='Person'

READY TO GO NEXT STEPS?