How to Publish the Active Directory as a SharePoint List

Ever wondered how you could publish your Active Directory in a SharePoint List. It will show all entries dynamically.

Step 1

First of all you need to setup a linked server in MS SQL using the ADSL provider

 
-- CREATE LINKED Server to ADSI

-- sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 
'ADSDSOObject', 'adsdatasource'

-- CREATE VIEW in the ICT DB

USE ICT ;

GO

IF OBJECT_ID ('ADSI_USERS', 'V') IS NOT NULL

DROP VIEW ADSI_USERS ;
 
GO

CREATE VIEW ADSI_USERS

AS 

SELECT * FROM OpenQuery (ADSI, 'select title, displayName, sAMAccountName, 
givenName, department, mail, telephoneNumber, sn, physicalDeliveryOfficeName
from ''LDAP://dc=domain,dc=local'' where objectCategory=''user''
AND objectCategory = ''Person'' AND displayName = ''*'' ')

GO

-- Select the USERS to test the connection

USE ICT

Select * from ADSI_USERS

To Drop a Linked server

Sp_DropServer 'ADSI', 'droplogins'

Step 2

Set the correct security

Set security to BE MADE USING THE CURRENT SECURITY CONTENT !

Giving the correct user and password to connect.

clip_image002

Step 3

Create an External content type in SharePoint Designer using the ADSI data source.

And make sure that you change the Authentication Mode security of the Default and Client to BCD Identity

image

See here is the output.

image

And the beauty is that you can reuse these accounts as lookup Fields in other lists !

This makes sure you are always in sync with your AD.

Enjoy !

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: