wsspectacular

 

I've come across the occasion, once or twice, where the customer wanted to be able to display all the users of a particular Active Directory group in a SharePoint list.  Though a seemingly simple request, it quickly turned into a headache for me.  Couldn't I use Audiences or a similar resource to populate a list?  I finally came up with this solution.  Although it's something of a kludge, it works reliably once it's set up.

 

It is possible to query Active Directory directly, as if it was a database server.  Setting this up is somewhat complex, but most steps only need to be performed once.

 

Resources you'll need:

• An account with Read Access to Active Directory
• This account will be reading AD on behalf of the query
• SQL server 2005
• You will be working in SSMS
• You will need to have permission to create a linked server, create a database, and create objects in the database.
• SharePoint Designer 2007
• SPD is required to be able to create data views on SharePoint pages.  No other tool can do this.
• Authoring access to the SharePoint page(s)

 

• Part One: Using SQL Server Management Studio, perform the following steps:
• First, we need to set up linked server access to AD:

 

–Add a linked server which will connect to Active Directory Services (ADSI)

–This only needs to be done once.

sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces',

'ADSDSOObject', 'adsdatasource'

GO

 

Note: you may need to modify this Linked Server as follows:

First, go to the Linked Server Properties:



 

Next, specify that the connection will be made using a specific context.  In this case we used the Domain Administrator, but really any account with Read Access to the AD schema would be fine.



 

 

• Part Two: Create Database, View, and SQL Login
• Create a database
• To host the views we'll be running against Active Directory, we'll need to set up a database.  You can use an existing one if you want, but I prefer to keep these items separate from my other production data. 
• I use the name ADLookup.
• The database doesn't need to be anything fancy.  Just accept defaults and continue. 



• Create a SQL Login
• In this example I created an account named "ADLookup", with the following options:
• Authentication type: SQL Server
• Note: this solution depends on the SQL security being set to Mixed Mode.  If you are at a site that requires Windows Only authentication I don't think this will work for you.  There is probably an alternative method, but I haven't looked into it yet.
• Default Database: ADLookup
• Server Roles: None
• User Mapping:
• ADLookup: Database Owner

 

 

• Create a view to do the work for us.  All items in RED can be changed to suit your purposes.

 

– Now show the login name and display name for all users in the specified group.

USE [adlookup] –This is the user database to hold all of the views we'll be creating

GO

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*Show the login name and display name for all users in the specified group.*/

CREATE VIEW [dbo].[vw_groupname]

AS

SELECT     sAMAccountName AS [Login Name], givenName AS [First Name], sn AS [Last Name], displayName AS [Full Name], mail AS [E-Mail Address],

                      telephoneNumber AS Phone

FROM         OPENQUERY(ADSI,

                      'SELECT sAMAccountName, givenName, sn,displayName, mail, telephoneNumber

FROM

''LDAP://dc=YourDomain,dc=local''

WHERE objectClass = ''user'' and objectCategory=''person''

AND memberOf = ''CN=YourGroup,OU=YourOU, dc=YourDomain,dc=local''')

                       AS derivedtbl_1

• Notes:
• This will return all members of the "YourGroup" group, located at "YourDomain.local/YourOU/YourGroup"
• The "objectCategory" parameter is to differentiate between users and computers (which can also be members of a group).
• This query can then be copied into a view, which will dynamically return all users in a given group.
• Then SharePoint can use this view to populate a Data View.  This workaround allows us to show all the users who are members of the group and display it on the page, something SharePoint can't seem to do by itself.
• Customization:
• Specify the view name
• Use the naming convention: vw_GroupName (e.g. vw_YourGroup)
• In this example I use a view named vw_AnchorageStaff.
• This should match the name of the AD group, to avoid confusion
• Specify the properties to be queried per user in the group.
• The above query returns the following properties:

 

sAMAccountName  AS [Login Name],

givenName AS [First Name],

sn AS [Last Name],

displayName AS [Full Name],

mail AS [E-Mail Address],

telephoneNumber AS Phone

 

• You will want to "rename" each of the columns as something more readable.  Column names with spaces have to be enclosed in brackets.
• Active Directory has an enormous number of properties that can be queried.  You can google the Microsoft site for a list of them.
• All properties are case-sensitive!
• Specify the OpenQuery statement. 
• This is essentially a query within a query.  The OpenQuery statement is querying Active Directory to build a resultset, and then our Select statement is querying that resultset.  This means you're running the same query twice.
• Because we're running two identical queries, they need to be exactly the same columns in the same order.
• The placement of single and double quotes is imperative.  Be sure to keep them exactly as they are.
• Modify the AD location of the group you're querying:
• ADSI isn't smart enough to search for the group.  You'll need to specify its location.
• Run the view:
• It should return the right number of records and columns.



 

 

• Part Three: Using SharePoint Designer, create a Data View
• In SPD, go to File–>Open Site and browse to the site you wish to work with.
• You may wish to update an existing page or create a new page. 
• Click in an available area on the SharePoint page.
• On the page to be edited, click Insert–>Data View

 

• A DataView control is added.



• In the Data Source Library tab of the task pane, click Connect to a Database.

• In the Data Source Connection dialog, click Configure Database Connection.

 

• In the Configure Database Connection dialog, enter connection information for your SQL server.  In this case I am connecting to an instance named "Sharepoint".



• Note: the ADLOOKUP user is a SQL user and needs to have (at least) read access to all views in the ADLOOKUP database.

 

• Click OK to acknowledge that passwords are passed in clear text.

 

• Select the name of the view we just created, and click Finish.



• In the Data Source Properties dialog, click the Fields button to select which fields to include in this view.

• Move fields over to add or remove them from the view.

• In the Data Source Library pane, click the drop down arrow next to the view we just added and select "Show Data".



• The Data Source Details tab is activated and you see the fields available to be put on the page.

• Select all the fields, then click the Insert Selected Items As. Button.  For this example we'll use a Multiple Item View.



 

• The fields are then populated in the data view on the page.



• You will need to customize the DataView from here:
• Change column names
• You'll note that any spaces turn into codes ("_x0020_").  You can modify this by clicking on each column header and editing the text.
• Change pagination
• Long lists take a while to load.  Don't go crazy.
• Add buttons for sorting and grouping
• Changing list style
• To customize the DataView, click on the small chevron button at the top right of the list.
• Click Data View Properties
• There are tabs for general, layout, and paging

 

 

This should get you started! 

 

This entry was posted on Thursday, January 10th, 2008 at 4:20 pm and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.