Active Directory data as SharePoint 2010 list using BDC

 Active Directory data as SharePoint 2010 list using BDC

Situation

Create SharePoint List that displays all ad users and contacts

Although there may be several ways to approach this problem I have found the BDC external lists as the easiest way

You can accomplish this using two modules

SQL Views doing a LDAP Query using Linked MS SQL Server

BDC External List Connecting  to this view and create external list using this data source.

For the SQL Linked using refer to different ways to doing it. I prefer the Linked Server way. Here is the good post you can refer to

Basically what you have to do is

1. Run the System Stored Proc to add Linked server by running

EXEC sp_addlinkedserver @server = N'ADSI', @srvproduct=N'Active Directory Service Interfaces', @provider=N'ADSDSOObject', @datasrc=N'adsdatasource'

2. Set credentials to the linked server User Account that can perform LDAP query

EXEC sp_addlinkedsrvlogin @rmtsrvname=N'ADSI',@useself=N'False',@locallogin=NULL,@rmtuser=N'DOMAIN\UserAccount',@rmtpassword=N'DomainAccountPassword'


3. Create a SQL Dialet Query View By right clicking the database and say Create view and paste this

//The below is for querying the users with WHERE condition. Be careful with ad objects. Find all the list of available AD

SELECT cn AS Name, mail AS EmailID, department, telephoneNumber
FROM         OPENQUERY(ADSI,
                      'SELECT cn,mail,department,telephoneNumber FROM ''LDAP://DC=xxxx,DC=xxx'' WHERE objectCategory=''person'' AND objectClass=''user'' ORDER BY cn')
 WHERE     (department IS NOT NULL)

//You can use the below for Contacts from AD

CREATE VIEW viewADContacts
AS
SELECT  [Name], sn [Last Name], street [Street], l [City], st [State]
FROM OPENQUERY( ADSI,
     'SELECT name, sn, street, l, st
      FROM ''LDAP:// OU=Sales,DC=activeds,DC=Fabrikam,DC=Com''
      WHERE objectCategory=''Person'' AND
      objectClass = ''contact''')
GO
SELECT * FROM viewADContacts

Ref -http://msdn.microsoft.com/en-us/library/windows/desktop/aa772380%28v=vs.85%29.aspx

4. Create a External Lists using BDC

I prefer to use ReverttoItself type BDC Identity to avoid the user type in password twice using Impersonated Windows Identity.

For this to work first run this pwershell command
$serviceApplications = Get-spserviceapplication$bdc = $serviceApplications[i]#Choose the index ‘i’ that corresponds to BDC,#or filter by $_.GetType().Name. Do not filter by $_.TypeName.$bdc.RevertToSelfAllowed = $true

Ref-http://support.microsoft.com/kb/982586

 Or this

$bcsServiceApp = Get-SPServiceApplication | where {$_ -match "Business Data Connectivity Service"}

$bcsServiceApp.RevertToSelfAllowed = $true;

$bcsServiceApp.Update();

Ref- http://zimmergren.net/technical/sp-2010-bcs-problem-with-authenticationmode-and-reverttoself

Once set give the the web application app pool account the necessary access to the database where you created the view in step 2.

Choose the datasource provide the app pool identity , choose the view, create the Read Items and Read List Items methods and create a external list out of it. 




You are not done yet until you go to the BDC Service and set the Object Permissions to the Data Store .

I usually add "All Authenticated Users" or "Domain Users" to the datastore.
Now navigate to the list you created on the web application and you should all be done