SharePoint Dragons

Nikander & Margriet on SharePoint

Monthly Archives: January 2012

Using external data in user profiles using BCS

When we set out to reuse data from a BCS external content type we’ve used a couple of resources that we wanted to share. First off, the next article was a great help during the set up of user profile synchronization:

Then, the following post was a nice walk thru showing how to set up user profile imports using BCS:

Finally, when we saw that the import (using miisclient.exe) failed because of a missing anchor component, the following post was a life saver:

Claims in SharePoint 2010

When we set out to write a blog post we had the intention of documenting the process of setting up a claims aware SharePoint web application. After consulting numerable resources, we’ve found out that it’s not needed at all.

First off, if you want to read more about claims authentication, go to and read the claims-based authentication white paper.

If you want to set up claims authentication in SharePoint Server 2010 we found that the following post is the best resource: This post describes a visual way to set it up, and since it’s so easy to make tiny mistakes, it’s better to execute the process aided by visual tools.

We have three comments about the post that might help you though:

  1. You can also use the MembershipSeeder tool to set up test accounts, we feel it’s the best way to set up a larger number of test accounts. The MembershipSeeder tool can be found on CodePlex:
  2. Be sure to create a new SQL Server user account as specified in the blog post.
  3. Adjust the People Picker when you’re done following the steps in the blog post.

1. Setting up users via the MembershipSeeder tool

The MembershipSeeder tool is a client dedicated to communicating with the Membership database. It’s the best way for adding larger amounts of test users to the Membership database.

Please note: Don’t get too excited about the comment that the MembershipSeeder tool has been superseded by CKS: FBA. If you follow that path, it will actually be more work for you. We like the fact that the MembershipSeeder tool directly accesses the membership database and works in a simple, no bells and whistles kinda way.

Before you’re able to use the MembershipSeeder tool, you need to download it (duh!) and change some configuration data:

  1. Download the MembershipSeeder tool, extract it, and go to the Debug folder.
  2. Locate the <connectionStrings> element.
    Notice that the connection string is pointing to the wrong direction: server=o12; database=aspnetdb; Trusted_Connection=True. Replace it, in our case it looks like this:

      <add name=”AspNetSqlMembershipProvider” connectionString=”Data Source=moon;Initial Catalog=MyAccounts;User ID=administrator;Password=*****” />

Please note that we’re using SQL Server authentication, as specified in the blog post, so while your data source and user credentials may differ, don’t change that aspect. It’s a great way to test if the SQL Server user account has sufficient permissions. Now, let’s add some roles and users:

  1. Double-click MembershipSeeder.exe. This opens the ASP.NET Membership Form window.
  2. We feel the UI is a bit messy (we’re not criticizing, we feel this is a very handy tool), so be careful. Check out the next Figure to get the idea.


  3. First, check the two checkboxes about adding, creating or deleting only 1 user.
  4. The upper right part allows you to create new roles. Create two new roles: Developers and Managers.
  5. Then, click the Get Roles button to verify this has succeeded.
  6. The upper left part allows you to add new users. Create three new users: anton (with the password: anton), bill (pwd: bill), and daisy (pwd: daisy).
  7. After you’ve done that, add the users to roles in the upper right part by typing a role name in the Role textbox and typing the user name in the User Prefix textbox.
  8. Then, click the Add to Role button. Add anton and daisy to the Managers role. Click the Get Users button to verify this has succeeded.
  9. Now, add bill and daisy to the Developers role.

At this point we’ve created a Developers role with the members Bill and Daisy, and a Managers role with the members Anton and Daisy. This will give us some users to test with later.

2. Create a new SQL Server user account

At first we used a SQL Server user account that we’ve created during installation. Apparently this is a special kind of user account that can’t be used when connecting to the membership database. When you’re doing direct manual configuration of the web.config files, this mistake is hard to detect. We liked the approach of creating a fresh SQL Server user account, even for development purposes.

3. Configure People Picker

If you want to be able to type parts of user names and want the People Picker to autocomplete those names add the following line to the web.config file of every web application where you want to use People Picker autocompletion:

<add key=”fbamembershipprovider” value=”%” />

So that it looks like:

  <clear />
  <add key=”AspNetSqlMembershipProvider” value=”%” />
  <add key=”fbamembershipprovider” value=”%” />

What now?

If everything goes well, you should be able to log in both using Windows accounts and form accounts. To check out what claims are associated to each user, first download and install the Windows Identity Foundation SDK at We’ll be using it within a web part, so you’ll need install the SDK 3.5 version because SPS 2010 doesn’t support .NET 4.

Now, create an empty SharePoint project in Visual Studio.NET 2010, add a visual web part and add a label on it. Add the following code to it, deploy the package to SPS 2010 and add the web part to a page:

using System;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.IdentityModel.Claims;

namespace LCWebParts.ClaimsVisualPart
    public partial class ClaimsVisualPartUserControl : UserControl
        protected void Page_Load(object sender, EventArgs e)
            IClaimsIdentity cidentity = (IClaimsIdentity) Page.User.Identity;
            Label1.Text = String.Format(“User name = {0}<br/>”, cidentity.Name);
            foreach (Claim claim in cidentity.Claims)
                Label1.Text += String.Format(“type: {0} value = {1}<br/>”, claim.ClaimType, claim.Value);

It’s interesting to see the different set of claims associated to different user accounts, and this allows you to make programmatic authorization decisions based on claims.

Claims documentation

So far the best resource we’ve found online about claims authentication and SharePoint 2010:

Number of logged on users in SharePoint

Nice: A very reasonable approach to finding the number of users currently logged on to SharePoint.

Little InfoPath trick

Place two buttons on an InfoPath form, and give the first button the label ‘5’ and the second button the label’ ‘6’. Then select both buttons and select the font Marlett. The labels 5 and 6 will be replaced with arrows.


The SharePoint Language Store

We feel the SharePoint Language Store ( ) is an interesting idea for situations where you don’t want (or are not allowed to) to roll out a new resource file every time there is a change in the UI multilingual text.

Want to use a different user account when logged into SharePoint Designer 2010?

Somebody asked this exact same question, and we were wondering how to do it. It turns out that, once connected to a SharePoint site, there is a very small icon at the bottom left that allows you to switch user accounts: check out

Long operation in progress

Sometimes, there are operations on a SharePoint application page or a web part that take a long time to complete. The best thing to do is to reuse the In Progress animated picture used by SharePoint itself, as shown in the next picture:

This can be accomplished easily by using the SPLongOperation class of the Microsoft.SharePoint namespace. It sets the web page image to the image used by the server to indicate a lengthy operation (typically, an animated image with associated text). The code goes something like this (taken from

using (SPLongOperation objLongOperations = new SPLongOperation(this.Page))
objLongOperations.LeadingHTML =”Processing request”;
objLongOperations.TrailingHTML = “Please wait while the request is being processed…”;
//Write your code here
System.Threading.Thread.Sleep(25000);//added to show the processing screen. Nor required in actual implementation

Check this blog post for more information:

Determining the SharePoint server version from a client programmatically

The next bit was taken from a forum post and written by Wayne Fan:

It’s a piece of code that issues a HTTP request and can be used for determining the version of SharePoint on a server. We’re documenting it here, because we know there’ll come a day that we’ll need this code:

HttpWebRequest request =(HttpWebRequest)WebRequest.Create(“http://<servername>/default.aspx&#8221;);

request.Credentials=new NetworkCredential(“<username>”,”<password>”,”<domain>”);

HttpWebResponse response =(HttpWebResponse)request.GetResponse();

request.Accept = “text/plain”;

Console.WriteLine(“Server Version:{0}”,response.GetResponseHeader(“MicrosoftSharePointTeamServices”));

When it’s a SharePoint 2010 site, you will get the result is like this: MicrosoftSharePointTeamServices:

And in SharePoint 2007 site, the result is like this: MicrosoftSharePointTeamServices:

Web analytics unique visitors go sky high

On a recent consultancy job, we were told that a certain site collection was used by a total group of 100 people. Big was our surprise when we turned to the SharePoint 2010 Web Analytics feature and found the site collection had a total of around 1000 unique visitors per day. Strange, no?

The definition of the total number of unique visitors per day can be found on the Microsoft Enterprise Content Management (ECM) Team Blog: : the total number of unique visitors per day consists of all SharePoint authenticated users plus anonymous users. In the last case, each unique IP address counts as a unique (anonymous) visitor.

Then, we saw something strange on one of our dev machines. The total number of unique visitors/day on a given site collection was 7, whereas there is only one user account on this machine. So, 7 different unique visitors was impossible on this machine. Were we on to something?

Intrigued, we took a look at the database. After doing some snooping around, we expected that the database WebAnalyticsServiceApplication_ReportingDB_[guid] is the database that holds the web analytics data.

After running a trace, we knew for sure. Executing the following table valued function returned the contents of our Site Collection Web Analytics Reports – Summary page:

— Get info for Site Collection Web Analytics Reports – Summary
exec sp_executesql N’SELECT TOP (2000) [propertyname], [currentvalue], [previousvalue], [percentagechange]
FROM [dbo].[fn_WA_GetSummary](@p0, @p1, @p2, @p3, default)
ORDER BY [PropertyName] ASC’
,N’@p0 int,@p1 int,@p2 int,@p3 uniqueidentifier’,@p0=20111213,@p1=20111113,@p2=30,@p3=’9519CAC1-F9AE-3BF8-D261-119A8B0A4F33′

Taking a look inside the function, we found the following T-SQL code being equivalent to the retrieval of unique visitors info:

— Get unique visitors info
DECLARE @CurrentStartDateId int = 20111213
DECLARE @PreviousStartDateId int = 20111113
DECLARE @Duration int = 30
DECLARE @AggregationId [dbo].[AggregationIdDataType] = ‘9519CAC1-F9AE-3BF8-D261-119A8B0A4F33’
DECLARE @IncludeSubSites bit = 1

DECLARE @CurrentEndDateId int, @PreviousEndDateId int
SET @CurrentEndDateId = [dbo].[fn_WA_AddDateId](@CurrentStartDateId, @Duration – 1)
SET @PreviousEndDateId = [dbo].[fn_WA_AddDateId](@PreviousStartDateId, @Duration – 1)
‘UniqueVisitors’ AS [PropertyName],
  SELECT [Frequency]
  FROM [dbo].[fn_WA_GetTotalTrafficVolume]
  (@CurrentStartDateId, @CurrentEndDateId, @AggregationId, @IncludeSubSites, 2)
) AS [CurrentValue],
  SELECT [Frequency]
  FROM [dbo].[fn_WA_GetTotalTrafficVolume]
  (@PreviousStartDateId, @PreviousEndDateId, @AggregationId, @IncludeSubSites, 2)
) AS [PreviousValue]

Apparently, the fn_WA_GetTotalTrafficVolume function plays an important role in determining the number of unique visitors. The next T-SQL code determined our current number of unique visitors:

— Current unique visitors from table-valued function
  SELECT [Frequency]
  FROM [dbo].[fn_WA_GetTotalTrafficVolume]
  (@CurrentStartDateId, @CurrentEndDateId, @AggregationId, @IncludeSubSites, 2)
— Current unique visitors from underlying table
    ISNULL(SUM([Frequency]), 0) AS [Frequency]
FROM [dbo].[WATrafficAggregationByDate] WITH (NOLOCK)
    [AggregationId] = @AggregationId AND
    [IncludeSubSites] = 1 AND
    [MetricType] = 2 AND
    [DateId] BETWEEN @CurrentStartDateId AND @CurrentEndDateId

This function sums all unique users per day, but if you want to see the real number of unique users per day, you do:

— Show unique users per day
FROM [dbo].[WATrafficAggregationByDate] WITH (NOLOCK)
    [AggregationId] = @AggregationId AND
    [IncludeSubSites] = 1 AND
    [MetricType] = 2 AND
    [DateId] BETWEEN @CurrentStartDateId AND @CurrentEndDateId

Using this query, we found 7 entries. This looks a lot more like the Site Collection Web Analytics Reports – Number of Daily Unique Visitors page. So, there was the answer we were looking for.

According to IFABC Global Web Standards, a unique visitor is an IP address plus a further identifier (such as user name, user agent, or a cookie). If you want to know how many daily unique visitors there are, you should go to the Site Collection Web Analytics Reports – Number of Daily Unique Visitors page. The Summary page is nothing more than a summation of all days and all unique visitors.

Is this a bad thing? We feel it is at least a little (okay, more than just a little). It makes sense to provide overviews of the number of unique visitors per day, week, month, year or whatever. By simply summarizing all unique visitors, soon the number of unique visitors on the Summary page goes sky high. It leads to a situation where web analytic reports show that there are 1000 unique visitors, whereas in reality there are only 100. We know it’s a summary page, but we also feel this is a situation where simple math doesn’t apply to this data and leads to confusing results.