SharePoint 2010 FBA: Enable search by part or full name in People Picker

 
Please refer to this post of mine on how to configure Forms Based Authentication (FBA) on a SharePoint Web Application using ASP.NET SQL Membership Provider.

After you setup FBA the People Picker control by default will only search for user’s by using the username. However, if you would like to search for users by part or full display name then you will need to carry out the following changes to the Membership Database:

• Add a column “ProfileNames” of type nvarchar(255) in the table: aspnet_Users
• Update the stored procedure: aspnet_Membership_FindUsersByName by replacing the following where clause:

  WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.LoweredUserName LIKE LOWER(@UserNameToMatch) 
  

  with:
   

  WHERE  u.ApplicationId = @ApplicationId AND m.UserId = u.UserId AND u.ProfileNames LIKE '%' + LOWER(@UserNameToMatch) + '%'
  

• Create the following Trigger on the “aspnet_Profile” table:

  CREATE TRIGGER [dbo].[ProfileProperty_Trigger] 
     ON   [dbo].[aspnet_Profile] 
     AFTER INSERT,UPDATE
  AS 
  BEGIN
  SET NOCOUNT ON;
  DECLARE @Names nvarchar(50)
  DECLARE @UID nvarchar(50)
  
  SELECT 
  @Names = p.PropertyValuesString,
  @UID = p.UserId
  FROM aspnet_Profile p
  INNER JOIN inserted i
  ON p.UserId = i.UserId
  
  UPDATE aspnet_Users SET ProfileNames = @Names WHERE aspnet_Users.UserId = @UID
  END
  

 
That is it, after making these changes the People Picker control should now match by username as well as display name.

SharePoint 2010: Limit People Picker to search only FBA Users

 
Please refer to this post of mine on how to configure Forms Based Authentication (FBA) on a SharePoint Web Application.

Consider the following scenario:

You have configured FBA on a Web Application and created a Site Collection that uses FBA. Your Web Application uses mixed mode authentication (FBA and Windows Authentication). When you search for users in People Picker it shows you matches for both FBA and AD users. You would like to restrict People Picker to only show matches for FBA Users.

You can achieve this by running the following stsadm command:

stsadm -o setproperty -pn peoplepicker-onlysearchwithinsitecollection -url http://www.nameOfMySiteCollection.co.uk/ -pv yes

The ‘peoplepicker-onlysearchwithinsitecollection’ part in there is misleading and doesnt really do what it implies, however, it does get rid of the AD user mataches from People Picker.

Before running the stsadm command:
 

 

 
After running the stsadm command:
 

 

Please note that if you type in the full username of an AD user and click on the Check Names icon it will still resolve the user.

SharePoint 2010: Setting up Form Based Authentication (FBA) using ASP.NET SQL Membership Provider

 
There are a few different ways of setting up Forms Based Authentication on a Web Application using ASP.NET SQL Membership as the Provider and in this post I will explain the approach I have used time and time again over the years. Please note that the method I use involves modifying the config files manually, however, there is another approach where this is done purely via IIS which I plan to blog about some time in the future.

We can break down the setup process into 7 steps (I am assuming here that we are setting it all up from scratch):

• Create the new Web Application
• Set up the Membership Database
• Modify the Web Application Web.config
• Modify the Web.Config of the Central Admin
• Modify the Web.Config of the STS (Security Token Service) Application
• Add a new .NET User
• Create the new Site Collection

1         Create the new Web Application

Go to Central Administration and create a new Web Application. Use the following information to create the Web Application:

• For Authentication select ‘Claims Based’
• Claims Authentication Types
  • Uncheck ‘Enable Windows Authentication’ (optional: if you purely want to use FBA)
  • Check ‘Enable Forms Based Authentication (FBA)
    • ASP.NET Membership provider name: MyMembershipProvider
    • ASP.NET Role manager name : MyRoleProvider

Fill out the rest of the form as per your requirement and create the Web Application.

Please note that the Membership provider and Role Manager names used above are just examples and you can name them according to your requirements.
 

2         Setup the Membership Database

Carry out the following steps to create the membership database:

1. Go to C:\Windows\Microsoft.NET\Framework64\v2.0.50727 and run “aspnet_regsql.exe”
2. Select “Configure SQL Server for Application Services”




3. Choose Windows Authentication
4. Specify the Database name, this can be anything in our example we will use ‘MyWebAppUsers’

To ensure form based authentication works smoothly it is important that the application pool identity account of SharePoint Central Admin, The Web Application we created above and the SecurityTokenServiceApplication have the appropriate rights on the Membership database (MyWebAppUsers). I usually grant them db_owner rights.
 

3         Modify the Web Application Web.Config

Add the following element after the </sharePoint> and before the <system.web> element as below and change the value of ‘DbServername’ with the relevant database server name:

<connectionStrings>
  <add name="MyDbConnectionString" connectionString="data source=DbServername;Integrated Security=SSPI;Initial Catalog=MyWebAppUsers" providerName="System.Data.SqlClient" />
</connectionStrings>

Find the <membership> element and add your own provider as below:

<membership defaultProvider="i">
<providers>
.....
<add name="MyMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="MyDbConnectionString" enablePasswordReset="false" enablePasswordRetrieval="true" passwordFormat="Clear" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" applicationName="/" />
</providers>
</membership>

Find the < roleManager> element and add your own provider as below:

<roleManager cacheRolesInCookie="false" defaultProvider="c" enabled="true">
<providers>
   ......
   <add name="MyRoleProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" applicationName="/" connectionStringName="MyDbConnectionString" />
</providers>
</roleManager>

Locate the <PeoplePickerWildcards> element and add the following element as below:

<PeoplePickerWildcards>
   ......
   <add Key="MyMembershipProvider" value="*" />
</PeoplePickerWildcards>

This enables partial matches when you type in a username in a people picker control. Without this a user will only be matched if you type the exact username. We are basically telling SharePoint here the character to use (asterisk) to do the wilcard search in SQL.

4         Modify the Web.Config of the Central Admin

Add the following after </sharePoint> and before <system.web> element replacing ‘DbServername’ with the relevant database server name.

<connectionStrings>
<add name="MyDbConnectionString" connectionString="data source=DbServername;Integrated Security=SSPI;Initial Catalog=MyWebAppUsers" providerName="System.Data.SqlClient" />
</connectionStrings>

Find the element <membership> and add your own provider as below:

<membership defaultProvider="MyMembershipProvider">
<providers>
   .......
   <add name="MyMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" applicationName="/" connectionStringName="MyDbConnectionString" enablePasswordReset="true" enablePasswordRetrieval="true" passwordFormat="Clear" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" />
</providers>
</membership>

Find the element <roleManager> and add your own provider as below:

<roleManager defaultProvider="AspNetWindowsTokenRoleProvider" enabled="true" cacheRolesInCookie="false">
<providers>
 ......
 <add name="MyRoleProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" applicationName="/" connectionStringName="MyDbConnectionString" />
</providers>
</roleManager>

Locate the <PeoplePickerWildcards> element and add the following element as below:

<PeoplePickerWildcards>
<clear />
   ......
   <add Key="MyMembershipProvider" value="*" />
</PeoplePickerWildcards>

 

5         Modify the Web.Config of the STS Application

Go to the root directory of the “SecurityTokenServiceApplication” which is typically located at: “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\SecurityToken” and open the web.config.

Just before the </configuration> element add the following ensuring you change the database server name to match the details of your database server.

  <connectionStrings>
    <add connectionString="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyWebAppUsers;Data Source=DbServername" name="MyDbConnectionString" providerName="System.Data.SqlClient" />
  </connectionStrings>
    <system.web>
      <membership defaultProvider="i">
            <providers>
                <add name="i" type="Microsoft.SharePoint.Administration.Claims.SPClaimsAuthMembershipProvider, Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
                <add name="MyMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="MyDbConnectionString" enablePasswordReset="true" enablePasswordRetrieval="true" passwordFormat="Clear" requiresQuestionAndAnswer="false" requiresUniqueEmail="false" applicationName="/" />
            </providers>
        </membership>
      <roleManager defaultProvider="c" enabled="true" cacheRolesInCookie="false">
        <providers>
                <add name="c" type="Microsoft.SharePoint.Administration.Claims.SPClaimsAuthRoleProvider, Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
                <add name="MyRoleProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" applicationName="/" connectionStringName="MyDbConnectionString" />
            </providers>
        </roleManager>
    </system.web>

Please note that I am making the assumption here that you dont have any other Web Applications in your farm with FBA enabled, if you do then a lot of these elements will already exist and all you need to do is to add the relevant parts from above to the relevant elements.
 

6         Add a new .NET User

Now we need to add a user that we will use to login to the FBA site. To do this we need to carry out the following steps:

• Open up IIS (Start > Run > type inetmgr)
• Select the SharePoint Central Administration v4 site from the list of sites
• Double click on .NET Users from the right hand side (as in the screeshot below).



• From the actions menu on the right click on ‘Set Default Provider’ and select ‘MyMembershipProvider’ from the DropDownList (or whatever name you used to name the MembershipProvider)
• From the actions menu click on ‘Add’ and fill out the form (screenshot below)



• After adding the user reset the Default Provider to what it was originally

 

7         Create the new Site Collection

Create a new Site Collection under the Web Application we created in step 1 and set the user we created in step 6 as the Site Collection Administrator (screenshot below).

Once the Site Collection is created successfully, browse to it and login using the credentials of the user we created in step 6.

Thats it! We have now successfully setup Forms Based Authentication on our Web Application.