Enumerating user active directory attributes with Infopath and Sharepoint 2010 SP1

Sharepoint 2010 and Infopath are an extremely powerful team, from IT support request forms, staff leave request forms and the ability to enumerate values from SQL databases, the options are endless.

Recently we began working on a "Parent Portal" which will allow parents of our students to login and view school dates, student marks and timetables. Most of the data we want to pull is stored on our SQL 2008 R2 SP1 servers, so Sharepoint and Infopath are potentially a great match.



The Problem

We quickly realized that all of these external SQL databases didn't reference our users by their Active Directory samAccountName but instead by the students ID number. The solution we came up with was to populate an unused Active Directory attribute with the student ID.

So then when a user logs in, Sharepoint sees the samAccountName then can enumerate the AD attribute (we are using the Department attribute) and use that Student ID to do lookups in SQL.

So how do we enumerate AD attributes with Infopath? Easy!




The Solution


Prerequisites

1. Sharepoint 2010 (preferably with SP1 installed as it fixes a number of User Profile problems).
2. The User Profile Service application needs to be running and a Active Directory synchronization connector created. This AD synchronization connector needs to have completed a full synchronization before you can enumerate any attributes.
3. An AD attribute needs to be pre-populated.


The Process

1. Open Infopath Designer 2010 and create a new Blank form.

2. Click on the "Data" tab and select "Data Connections"

3. Select "Create a new connection to" and select "Recieve Data" then click next.

4. Select "SOAP Web Service" and click next.

5. Enter the URL of the User Profile Service. In my environment it is http://sharepoint.domain.internal/_vti_bin/userprofileservice.asmx

6. Select "GetUserPropertyByAccountName", press next.

7. Leave "tns:AccountName" as is, set the value of "tns:propertyName" to the name of the Active Directory attribute you want to enumerate.

It is best to use something standard like "Department" or else you need to mess around in Forefront Identity Manager and the Sharepoint User Profile Service and believe me its something you don't want to do. For this example I am using "Department" Active Directory attribute which I have pre populated, then click next.

8. Click next on the following screen.


9. You can give this connection a name that means something to you, I will call mine, "sharepointEnumerateDepartment", then click Finish.


10. Now for demonstration we will simply display this "Department" attribute on the screen as a proof of concept. Click on the Fields dialog on the far right hand side of the screen, select "myFields" then click "Add Field".

11. Name it "Test" and click OK.


12. Drag the "test" field onto the form and drop it. This will insert the test field into the form.

13. Right click the test box and select "Text Box Properties" then select the "Data" tab, and the "fX" symbol under the Default Value heading.

14. Select "Insert Field or Group".


15. Click the "Fields" drop down and select "sharepointEnumerateDepartment" or whatever you named this DataConnection in step 9.


16. Please select the "Value" item as in the image below.

myFields > dataFields > tns:GetUserPropertyByAccountNameResponse > GetUserPropertyByAccountNameResult > Values > ValueData > Value

Then click OK, OK, OK.

17. Now it is time to preview the form. Click on the "Home" tab and select "Preview".

If all has worked correctly, you will see the Active Directory attribute you want to enumerate displayed in the Test box above. My attribute is Department and I have set it to my example student ID of 20010.


Completion

Now that we have pulled this value from Active Directory we have so many more options. I can use this ID to pull student marks from our Accelerus database or student timetables from the timetable SQL database. You might want to pull a totally different attribute from AD and display custom SQL content to the end user, this same process can be modified and used to achieve that.






Things to check if its not working

• The Sharepoint User Profile Service application. Ensure it is running and both the "Forefront Identity Manager Service" and "Forefront Identity Manager Synchronization Service" are running on the Sharepoint 2010 server. 

• Ensure the Sharepoint User Profile Service application has done a full synchronization. You can check this in Sharepoint Central Administration > Manage Service Applications > User Profile Service. On the far right side of the page, there should be a number relating to your AD users in the "Number of User Profiles" field and a number relating to the number of Active Directory properties you are synchronizing in the "Number of User Properties" field.

If these are blank. you need to create a new Active Directory Synchronization connector and then start a full profile synchronization.

• Of course ensure the "Department" attribute is populated in Active Directory.

 

Sharepoint User Profile Service application can be extremely difficult to configure correctly, if you have problems I have found the folks on the Sharepoint MSDN forums are extremely helpful.