Sharepoint 2010 Claim Based Authentication with SQL Server as Provider

How to setup the Claim based Authentication with SQL Server as Provider. This is very straight forward. Below are high level tasks we need to do.

1) Setup you AspnetRoleMemberShip Provider

2) Create Claims Based Web Application.

3) Create Site Collection.

4) Configure Web.Config of below Sites

a) Central Administration’s Web.Config

b) Security Token Service’s Web.Config

c) Claim Based Application’s web.Config

Provide Access to User from User Policy in Central Administration.

Details steps are given Below

Setup the AspnetRoleMemberShip Provider database

• Go to the SQL Server database server
• On the database server, open Windows Explorer.
• Navigate to the path %System Drive%\Windows\Microsoft.NET\Framework\v2.0.50727.
• To start the ASP.NET SQL Server Setup Wizard, double-click aspnet_regsql.exe.
• Complete the wizard
• Make sure the Application Pool accounts of the web application(s) and the Central Administration web site have access to the database

Create a new web application with Claim Based Authentication

• Sign in to Central Administration
• Select Application Management from Left Menu
• Click on Manage Web Applications
• Click New Web Application
• Select Claims Based Authentication
• Identity Providers
• Check the Enable Windows Authentication box
• Check the Enable ASP.NET Membership and Role Provider checkbox
• In the Membership provider name edit box, type MySqlMember
• In the Role provider name edit box, type MySqlRole

 Create a new site collection

• Again Select Application Management
• Click Create site collections
• Select the newly created web application
• Fill in a name and select a template

Modify web.config of the Central Administration site

• Open the Central Administration site's web.config file
• Find the </configSections> entry
• Paste the following XML directly below it.

Note: This is connection String of your SQL Server’s Aspnetdb

<connectionStrings>

<clear />

<add name="AspNetDb" connectionString="data source=PRAWAL01;Integrated Security=SSPI;

Initial Catalog=aspnetdb" providerName="System.Data.SqlClient" />

</connectionStrings>

 

Note: Replace {your database Server Name} with Database Name

 ·  Find the <system.web> entry

·  Paste the following XML directly below it

<roleManager enabled="true"

   cacheRolesInCookie="false"

   cookieName=".ASPXROLES"

   cookieTimeout="30"

   cookiePath="/"

   cookieRequireSSL="false"

   cookieSlidingExpiration="true"

   cookieProtection="All"

   defaultProvider="AspNetWindowsTokenRoleProvider"

   createPersistentCookie="false"

   maxCachedResults="25">

                     <providers>

                           <clear />

                           <add connectionStringName="AspNetDb"

applicationName="/" name="MySqlRole"

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>

              <membership defaultProvider="MySqlMember"

                 userIsOnlineTimeWindow="15" hashAlgorithmType="">

                     <providers>

                           <clear />

                           <add connectionStringName="AspNetDb"

enablePasswordRetrieval="false"

                 enablePasswordReset="true"

                 requiresQuestionAndAnswer="true"

                 passwordAttemptWindow="10"

                 applicationName="/"

                 requiresUniqueEmail="false"

                 passwordFormat="Hashed"

                 name="MySqlMember"

type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0,

Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

                     </providers>

              </membership>

 

Modify the web.config of the Security Token Service (STS) virtual directory

 ·  Open the Security Token Service (STS) virtual directory's web.config file location of this file is

C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\WebServices\SecurityToken

 ·  Find the </system.net> entry

·  Paste the following XML directly below it

       <connectionStrings>

              <clear />

              <add name="AspNetDB" connectionString="data source=PRAWAL01;

Integrated Security=SSPI;Initial Catalog=aspnetdb" providerName="System.Data.SqlClient" />

       </connectionStrings>

 

·  Paste the following XML directly just above the entry </configuration>

<system.web>

              <membership>

                     <providers>

                           <add connectionStringName="AspNetDB" enablePasswordRetrieval="false" enablePasswordReset="true"

                           requiresQuestionAndAnswer="true" passwordAttemptWindow="10" applicationName="/" requiresUniqueEmail="false"  passwordFormat="Hashed" name="MySqlMember" 

                           type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0,Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

                     </providers>

              </membership>

              <roleManager enabled="true">

                     <providers>

                           <add connectionStringName="AspNetDB"

                           applicationName="/" name="MySqlRole" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

                     </providers>

              </roleManager>

       </system.web>

 

Modify web.config of the claims based web application

 ·  Connection string – Find the </configSections> entry

·  Paste the following XML directly below it

<connectionStrings>

              <clear />

   <add name="AspNetDB" connectionString="data source=PRAWAL01;

   Integrated Security=SSPI;Initial Catalog=aspnetdb"

   providerName="System.Data.SqlClient" />

</connectionStrings>

 

We need to Add Provider to <MemberShip> and <RoleManager>, So the best way is Replace <membership and <roleManager with below XML  (This is addition to Default which will be available, if you have more provider already configured, Just add more Provider).

  <membership defaultProvider="i" userIsOnlineTimeWindow="15" hashAlgorithmType="">

 <providers>

 <clear />

 <add connectionStringName="AspNetDB"

enablePasswordRetrieval="false"

enablePasswordReset="true"

requiresQuestionAndAnswer="true"

passwordAttemptWindow="10"

applicationName="/"

requiresUniqueEmail="false"

passwordFormat="Hashed"

name="MySqlMember"

type="System.Web.Security.SqlMembershipProvider,

System.Web, Version=2.0.0.0,

&#xD;&#xA;Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />

                 <add name="i"

type="Microsoft.SharePoint.Administration.Claims.SPClaimsAuthMembershipProvider,

\&#xD;&#xA;Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral,

PublicKeyToken=71e9bce111e9429c" />

                </providers>

         </membership>

         <roleManager enabled="true"

cacheRolesInCookie="false"

cookieName=".ASPXROLES"

cookieTimeout="30"

cookiePath="/"

cookieRequireSSL="false"

cookieSlidingExpiration="true"

cookieProtection="All"

defaultProvider="c"

createPersistentCookie="false"

maxCachedResults="25">

                <providers>

                       <clear />

                       <add connectionStringName="AspNetDB"

applicationName="/"

name="AspNetSqlRoleProvider"

type="System.Web.Security.SqlRoleProvider,

System.Web, Version=2.0.0.0,Culture=neutral,

PublicKeyToken=b03f5f7f11d50a3a" />

                       <add applicationName="/"

name="MySqlRole"

type="System.Web.Security.WindowsTokenRoleProvider,

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>

That’s all to be done for Configuration. Next step is to provide access to users from SQL Server.

Adding User Policy via Central Administration

• Open Central Administration
• Navigate to Manage Application
• Select the Claim Based Application from the List
• Select User Policy from Top Ribbon.
• Select Telephone Book ‘icon’
• Select ‘All Zone’ and Click Next
• Enter the Name is FIND text box
• Hit Enter key
• Select user from List and Provide Access Permission.

Now Open a new Browser window and Type http://<<url>> of your site collection of Claim based Web Application.

• Select “forms Authentication”
• Enter user name and password

So as per permission given “Full Control” (as Site Owner) in this case, site will allow user in.

 

Further to this, being a site admin you can add other users from SQL Database to this site’s Member, Owner, Viewer groups.