MS SQL – How to Publish the Active Directory as a SharePoint List

January 26, 2014

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 !