SharePoint Form Based Authentication (FBA) Using SQLMembership Provider

- Here we have a very common sharepoint security issue is facing any Moss specialist, it is Form Based Authentication "FBA".
- It is very powerful feature to your company.
- In case you want your customer's company access your portal keeping them away of your active directory you should do what I'll mention below.
- Before I start I'm very grateful to Andrew Connel for great post which helps me a lot.
- What I write below is for simplicity, If you wanna more, please explore Andrew blog.

Listen carefully(I mean read carefully) any step is effective, don't miss any.

1. Create Database to store external users accounts:

• Create SQL Server membership database to store external users (non Active Directory users) using VS tool called “aspnet_regsql.exe” located at:
  
  
  C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
  
• It will launch wizard as below:

• Select “Configure SQL Server for application services” then click “NEXT”.

• Enter your database Server name and Database name you will use for authentication then click “NEXT”.

• Accept the Settings Summary page and click “NEXT”.

• Last screen inform that the database has been created then click FINISH.
  

2. Create connection to SQL membership and add new users:

• Here we want to add new users at “MossFBA” database to grant access to our sharepoint website.


• Open VS.NET (C# 2008 used here) and create new website (host it anywhere).


• At “web.config” replace with text below <connection string /> to SqlMemebership database between <appSettings /> and <system.web> tags :

<connectionStrings>
<add name="FBA_SqlConnectionString" connectionString="server= m11-214-04;database=MossFBA;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
connectionStrings>

m11-214-04: Is your database server name.
MossFBA: Database name for Sqlmembership.

• We still at “web.config” of website, add text below (membership provider definition) between compilation> and <authentication mode="Windows" /> :
  
  <membership defaultProvider="FBA_AspNetSqlMembershipProvider">
  <providers>
  <add connectionStringName="FBA_SqlConnectionString" passwordAttemptWindow="10"
  enablePasswordRetrieval="false"
  enablePasswordReset="true" requiresQuestionAndAnswer="false"
  applicationName="/"
  minRequiredPasswordLength="1" maxInvalidPasswordAttempts="5"
  minRequiredNonalphanumericCharacters="0" requiresUniqueEmail="false"
  passwordFormat="Hashed"
  description="Stores and retrieves membership data from the Microsoft SQL Server database"
  name="FBA_AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
  providers>
  membership>
  
  
  <roleManager enabled="true"
  defaultProvider="FBA_AspNetSqlRoleProvider" >
  <providers>
  <add name="FBA_AspNetSqlRoleProvider"
  type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
  connectionStringName="FBA_SqlConnectionString"
  applicationName="/"
  />
  providers>
  roleManager>
  
• NOTE: Connection string name should be the same as defined in first block “Connection string definition” (here FBA_SqlConnectionString).
  
• At VS.NET IDE (Integrated Development Environment) from “WebSite” menu select “ASP.NET Configuration” for adding new users and roles.
  
  
  
  
• New window will open.
  
  
  
  
• Select “Security” then “Select authentication type”.
  
  
  
  
• Select “From The Internet” to enable Form authentication (default is windows) then “DONE”.
  
  


• Select “Select a different provider for each feature (advanced)” to ensure that our Membership and Role providers are tested and selected.
  
  
  


• Select “FBA_AspNetSqlMembershipProvider” then click “Test”.then select “FBA_AspNetSqlRoleProvider” then click “Test”.
  
  
  
  
• Move to “Security” tab again and click “Add user”- test-, fill in the form and click “Create user”.
  
  
  
• New we have Database “MossFBA” with connection string “FBA_SqlConnectionString” and user created “test” as external user to login to our sharepoint site.

3- Configure SharePoint site which Form authentication will be applied to:

Navigate to web site folder for targeted sharepoint site and open “Web.config”.

C:\Inetpub\wwwroot\wss\VirtualDirectories\1212

At “web.config” add text below(connection string) to SqlMemebership database between <appSettings/> and <system.web> tags :

<connectionStrings>
<add name="FBA_SqlConnectionString" connectionString="server= m11-214-04;database=MossFBA;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>
connectionStrings>

m11-214-04: Is your database server name.
MossFBA: Database name for Sqlmembership.

Add text below (membership provider definition) between authentication> and <identity impersonate="true" />:

<membership defaultProvider="FBA_AspNetSqlMembershipProvider">
<providers>
<add connectionStringName="FBA_SqlConnectionString" passwordAttemptWindow="10"
enablePasswordRetrieval="false"
enablePasswordReset="true" requiresQuestionAndAnswer="false"
applicationName="/"
minRequiredPasswordLength="1" maxInvalidPasswordAttempts="5"
minRequiredNonalphanumericCharacters="0" requiresUniqueEmail="false"
passwordFormat="Hashed"
description="Stores and retrieves membership data from the Microsoft SQL Server database"
name="FBA_AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
providers>
membership>


<roleManager enabled="true"
defaultProvider="FBA_AspNetSqlRoleProvider" >
<providers>
<add name="FBA_AspNetSqlRoleProvider"
type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
connectionStringName="FBA_SqlConnectionString"
applicationName="/"
/>
providers>roleManager>


4- Configure Central Administration Web.Config file:

At “web.config” add text below(connection string) to SqlMemebership database between <appSettings/> and <system.web> tags :

<connectionStrings>
<add name="FBA_SqlConnectionString" connectionString="server= m11-214-04;database=MossFBA;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>connectionStrings>

m11-214-04: Is your database server name.
MossFBA: Database name for Sqlmembership.

Add text below (membership provider definition) between authentication> and <identity impersonate="true" />:

<membership defaultProvider="FBA_AspNetSqlMembershipProvider">
<providers>
<add connectionStringName="FBA_SqlConnectionString" passwordAttemptWindow="10"
enablePasswordRetrieval="false"
enablePasswordReset="true" requiresQuestionAndAnswer="false"
applicationName="/"
minRequiredPasswordLength="1" maxInvalidPasswordAttempts="5"
minRequiredNonalphanumericCharacters="0" requiresUniqueEmail="false"
passwordFormat="Hashed"
description="Stores and retrieves membership data from the Microsoft SQL Server database"
name="FBA_AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
providers>
membership>


<roleManager enabled="true"
defaultProvider="AspNetWindowsTokenRoleProvider" >
<providers>
<add name="FBA_AspNetSqlRoleProvider"
type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
connectionStringName="FBA_SqlConnectionString"
applicationName="/"
/>
providers>roleManager>

NOTE:At “roleManager” the “defaultProvider” value is set to “AspNetWindwosTokenProvider”

5- Configure Authentication for our site to be form based:

Open IIS which host “Moss2007” then check for user account which has access to your site.from “Application Pools”->“SharePoint 1212” -> RightClick -> Properties->Identity.

Now you have got the “User Name” then go to you your database server “host MossFBA database”.

Open “MossFBA” database -> expand “Security”->expand “Users”--rightClick--> “New User”.
o At “UserName” type “FBAUser” it is alias name.
o At “Login name” type user name you picked from previous step (previous image).
o Here we grant access for Sql database contains our user’s name we created in step no.2.
o Check “db_owner” as database role membership (you should select appropriate permission for your users, I did this for demo only).

• Open “SharePoint 3.0 central administration” -> ”Application Management”-> “Authentication providers”.

• Select your website “sharepoint 1212”.

  

• Be sure “Web Application” is your targeted site and note that “Membership Provider Name” is “windows”. Now Click “Default”.

  
  

• Select “Forms” as “Authentication type”.
  o Membership provider name: FBA_AspNetSqlMembershipProvider
  o Role manager name: FBA_AspNetSqlRoleProvider
  

  
  

• Open “SharePoint 3.0 central administration” -> ”Application Management” -> “Site collection Administrators”.

  
  

• Be sure the selected site collection is your targeted site.
  o At “Primary site collection administrator” type: test.
  “test” is the username we created in “MossFBA”.

• Now we grant our users from SQL Server membership database (MossFBA) access to our site and our site is not accessible for local windows accounts or active directory users.

• Open your browser and navigate to targeted site you will automatically redirected to login page ask for username and password.

  

• Congratulations….you logged in…welcome home.