Uncategorized

>Claim Based Authentication with SQL Server as Provider


>

I thought of sharing the Details about 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

Please refer below picture:

image

image

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,
    
    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 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.

    image

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

    image

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

    image

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

    image

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

    Thanks!!

  • Advertisements

    3 thoughts on “>Claim Based Authentication with SQL Server as Provider

    1. >can you show how to use asp.net control (login, change password and password recovery) in this claim based web app???i'm really stuck with it…

    2. >Very Good stuff..Very easy to understand the concept.Need to know more information on permissions levels in Authentication with Sql, FBA, LINQ.Thanks for your stuff.

    3. >Hi,Can you please provide more stuff on Windows Live Authentication using Claim based Authentication for MOSS 2010.Thanks in advance!!

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s