Step by Step – Configure SharePoint 2010 Forms Based Authentication with SQL

Note: Download artifacts discussed in this article from Here.

It is very common to use SQL Server database to store external users and roles in extranet environments for physical separation of the internal and external users. Typically external identity systems require specific schema changes and AD administrators don’t allow applications to store their users in the main organization domain directory for security concerns.

I have recently wrote an article on step by step guide to configure SharePoint 2010 FBA with ADLDS. This article follows same pattern to configure SharePoint FBA with SQL Server. As you may notice, most of the steps are same and web configuration file changes are similar. This article describes 5-Steps guide to configure SQL Users and Roles in Single-Server SharePoint 2010 environment on Windows 2008 R2 server for Forms Based Authentication.

Note: If you are looking for detailed step by step guide with lots of screenshots, you can download 35-pages step by step PDF guide demonstrating same steps discussed in this article.

 Step 1 – Create SQL Server Database to host FBA Accounts

  • Run C:\Windows\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe to create the SQL DB – aspnetdb
  • Verify that Niks\Administrator is running Application Pools for the Content and Central Admin Web Application and it’s added as dbowner role on the aspnetdb database.

Step 2 – Add SQL Users and Roles for FBA

  • Download the MembershipSeeder to load users for form-based authentication – http://cks.codeplex.com/releases/view/7450
  • How to use MembershipSeeder tool?
    • Open the  \Bin\Release\MembershipSeeder.exe and Update the SQL Server Name by clicking “Configure” button and close the tool
    • Reopen the tool and use following guidelines to add users.
      • Add 1 user at a time from membership section using “create” button
      • Add 1 role at a time from roles section using “create” button
      • Add 1 user to the role at a time from roles section using “Add to Role” button

  • Add SQL Roles and Users as following
    • Roles – sqlowners, sqlcontributors, sqlreaders
    • Users – sqlowner, sqlcontributor, sqlreader and add them to specific groups

Step 3 – Create New Web Application with Forms Based Authentication

  • Add DNS entries for the host headers – sqlportal.niks.local
  • Create New Web Application with Claims Based Authentication
    • Specify Port-80 and Host Header – sqlportal.niks.local
    • Select Windows Authentication and Forms Based Authentication
      • Specify Membership Provider – SqlMember and Role Provider – SqlRole
    • Specify proper content database name and leave everything else as it is
    • Create New Site Collection and specify Niks\Administrator as Site Collection Admin
    • Verify the Windows Authentication by logging to http:\\sqlportal.niks.local as Using Niks\Administrator

Step 4 – Update the Web Config Files for FBA – Content Web App, Central Admin Web App, and STS


<connectionStrings>
<clear />
<add name="AspNetSqlMembershipProvider" connectionString="data source=SP2010VM;Integrated Security=SSPI;Initial Catalog=aspnetdb"  providerName="System.Data.SqlClient" />
</connectionStrings>
    • Replace the  <PeoplePickerWildcards> entry with following XML
<PeoplePickerWildcards>
<clear />
<add key="AspNetSqlMembershipProvider" value="%" />
<add key="SqlMember" value="%"/>
<add key="SqlRole" value="%"/>
</PeoplePickerWildcards>
    • Locate the <membership> entry and Replace everything from <membership> to </membership> with the following XML
<membership defaultProvider="i">
<providers>
<clear />
<add connectionStringName="AspNetSqlMemberShipProvider"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
passwordAttemptWindow="10"
applicationName="/"
requiresUniqueEmail="false"
passwordFormat="Hashed"
name="SqlMember"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="i" type="Microsoft.SharePoint.Administration.Claims.SPClaimsAuthMembershipProvider, Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
</providers>
</membership>
    • Locate the <roleManager> entry and Replace everything from <roleManager> to </roleManager> with the following XML
<roleManager defaultProvider="c" enabled="true" cacheRolesInCookie="false">
<providers>
<clear />
<add connectionStringName="AspNetSqlMemberShipProvider"
applicationName="/"
name="SqlRole"
type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="c" type="Microsoft.SharePoint.Administration.Claims.SPClaimsAuthRoleProvider,
Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
</providers>
</roleManager>
  • Central Admin Web Application 
    • Update Central Admin Web Application web.config file (to find central admin web.config – go to the IIS, select central admin, and click Explore to find contents)
    • Find the </configSections> entry and add following XML directly below it
    <connectionStrings>
    <clear />
    <add name="AspNetSqlMembershipProvider" connectionString="data source=SP2010VM;Integrated Security=SSPI;Initial Catalog=aspnetdb"  providerName="System.Data.SqlClient" />
    </connectionStrings>
    • Replace the  <PeoplePickerWildcards> entry with following XML
<PeoplePickerWildcards>
<clear />
<add key="AspNetSqlMembershipProvider" value="%" />
<add key="SqlMember" value="%"/>
<add key="SqlRole" value="%"/>
</PeoplePickerWildcards>
    • Find the <system.web> entry and add the following XML directly below it. By default, there should be 1 blank Membership or RoleManager entry. Double check whether the <membership> and <rolemanager> entries only exist ones. Delete any double entries.
  <membership defaultProvider="i">
    <providers>
<clear />
<add connectionStringName="AspNetSqlMembershipProvider"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
passwordAttemptWindow="10"
applicationName="/"
requiresUniqueEmail="false"
passwordFormat="Hashed"
name="SqlMember"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="i" type="Microsoft.SharePoint.Administration.Claims.SPClaimsAuthMembershipProvider, Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
</providers>
</membership>

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

</roleManager>

  • Modify STS web.config file 
    • From the IIS, select the SecurityTokenServiceApplication under SharePoint Web Services and click Explore – it should take you to the C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\SecurityToken
    • Find the </system.net> entry and add the following XML directly below it
<connectionStrings>
<clear />
<add name="AspNetSqlMembershipProvider" connectionString="data source=SP2010VM;Integrated Security=SSPI;Initial Catalog=aspnetdb"  providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<membership defaultProvider="i">
<providers>
<clear />
<add connectionStringName="AspNetSqlMembershipProvider"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
passwordAttemptWindow="10"
applicationName="/"
requiresUniqueEmail="false"
passwordFormat="Hashed"
name="SqlMember"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0,
Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="i" type="Microsoft.SharePoint.Administration.Claims.SPClaimsAuthMembershipProvider, Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
</providers>
</membership>

<roleManager defaultProvider="c" enabled="true">
<providers>
<clear />
<add connectionStringName="AspNetSqlMembershipProvider"         applicationName="/"
name="SqlRole"
type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="c" type="Microsoft.SharePoint.Administration.Claims.SPClaimsAuthRoleProvider, Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" />
</providers>
</roleManager>
</system.web>

Step 5 – Configure the SharePoint Authorization and Verify the Access for the both AD (Windows) and SQL(Forms Based) Users

  • Verify AD Groups and Users are available to test
    • Sample Global Security Groups – adowners,adcontributors,adreaders
    • Sample Users – adowner,adcontributor,adreader
    • Add AD Groups and SQL Roles as SharePoint Security Groups – Readers, Contributors, and Owners
  • Verify that central admin and content web application people picker finds both AD groups/users and SQL roles/users – Please note that SQL Roles can be searched with the full role name while SQL user names can be searched via wildcard

  • Add AD users and SQL users into SharePoint Security Groups via AD Groups/SQL Roles and verify proper access – read-only, contribute, full control

Following screenshot demonstrate that I was able to successfully login as SQL User – SQLOwner

More Resources

About these ads
This entry was posted in SP2010 Admin General. Bookmark the permalink.