Configure SharePoint Forms Based Authentication to use SQL user accounts

By:   |   Updated: 2010-08-11   |   Comments   |   Related: > SharePoint Configuration


Problem

This article outlines how to configure SharePoint Forms Based Authentication (FBA) to use user accounts stored in an SQL database, typically used on a SharePoint Extranet.

Solution

Configure the User and Role Store

First we need to configure a database to store our users in. The easiest was to do this is via the Asp.Net 2.0 Framework

  1. Go to a command prompt, Start -> Run -> CMD <enter>
  2. Change to the Asp.Net Framework directory, "cd c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727" <enter>
  3. Run the Asp.Net SQL Server Setup Wizard by typing aspnet_regsql in the command prompt followed by <enter>
    4. windows
  4. Click Next on the Wizard
  5. Select the default option Configure SQL Server for application services then click Next
    6. select a setup option
  6. Enter the Server name and the name of the Database you want to create, then click Next
    7. select the server
  7. Finally click Next to accept your settings
    8. confirm your settings
  8. The Wizard will take a few seconds to complete before displaying the final page. Click Finish to close the wizard.
  9. Open Microsoft SQL Server Management Studio and confirm your new database has been created.

Tip: Make sure the user account running your SharePoint Application Pool has access to the database above.

Create a User

Now that we have a database to store our users in we need to create at least one user. Again the easiest way to do this at this stage is via Microsoft Visual Studio as it has a built in web application which connects to a membership and role provider store.

  1. Create a folder called FBA Management Site, just use your desktop as it temporary
  2. Open Microsoft Visual Studio, screen shots below show the 2005 version.
  3. Select File -> Open -> Web Site.
  4. In the Open Web Site dialog, choose the File System icon on the left side of the dialog, then browse to and select the FBA Management Site folder created in step 1.
  5. Click Open.
  6. In the Solution Explorer, right-click on the web site and select Add New Item.
  7. Select Web Configuration File and click Add. There is no need to rename the file, web.config is fine.
  8. Replace the empty <connectionStrings/> element with the following snippet. Be sure to replace both <server name> and <database name> with their appropriate values.


    9. <connectionStrings>
      <add name="AspNetDbFBADemoConnectionString"
        connectionString="Data Source=<server name>;
Initial Catalog=<database name>;
Integrated Security=True"/>
    </connectionStrings>
  9. Just below the <system.web> element, add the following membership and roleManager elements. Be sure to update the connectionStringName attributes of each of the two providers to the name of the connection string name you created in the step above. 
    10. <!-- membership provider -->
    <membership defaultProvider="FBADemoMember">
      <providers>
          <add
          connectionStringName="AspNetDbFBADemoConnectionString"
          enablePasswordRetrieval="false"
          enablePasswordReset="true"
          requiresQuestionAndAnswer="false"
          applicationName="/"
          requiresUniqueEmail="false"
          passwordFormat="Hashed"
          maxInvalidPasswordAttempts="5"
          minRequiredPasswordLength="1"
          minRequiredNonalphanumericCharacters="0"
          passwordAttemptWindow="10"
          passwordStrengthRegularExpression=""
          name="FBADemoMember"
          type="System.Web.Security.SqlMembershipProvider,
System.Web,Version=2.0.0.0,
Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a"   />
      </providers>
    </membership>

    <!-- role provider -->
    <roleManager enabled="true" defaultProvider="FBADemoRole">
      <providers>
          <add
          connectionStringName="AspNetDbFBADemoConnectionString"
          applicationName="/"
          name="FBADemoRole"
          type="System.Web.Security.SqlRoleProvider,
System.Web,Version=2.0.0.0,Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a"/>
      </providers>
    </roleManager>
  10. Save and close the web.config file.
  11. Click ASP.NET Configuration under Website. The ASP.NET Web Site Administration Tool opens in a browser. If the tool does not appear, or cannot connect, verify the connection string and provider information entered above.
  12. Click on the Security tab. You are presented with the following. From here we will create our users and roles.
web site administration tool
  1. Click on the Select authentication type link in the Users box on the left.
  2. Select the From the internet radio button then click the Done button in the bottom right hand corner of the window.
  3. Create an Administrator, Manager and Employee role. This step and the next three are intuitive enough that I am not going to spell them out.
  4. Create a single Administrator user, spadmin. Be sure to assign the user to the Administrator role as you create it.
  5. Close the ASP.NET Web Site Administration Tool.
  6. Close Microsoft Visual Studio 2005.

Prepare the Authentication Provider

Now we need to configure the Authentication Provider to use Forms based authentication. This is done via SharePoint Central Administration.

  1. Browse to SharePoint Central Administration
  2. Select the Application Management tab
  3. Under the Application Security section select Authentication Providers
    4. application management
  4. Select the appropriate Web Application which you want to allow FBA for.
    5. authentication providers
  5. Click the appropriate Zone you would like to change, only Default is shown below.
  6. Scroll down to the Authentication Type section and change the Authentication Type to Forms
    7. edit authentication
  7. Scroll down to Membership Provide Name and enter the name of your Membership provider, this must match the name in your web.config you previously configured..
    8. sharepoint

Note the Enable Anonymous access check box. This one of two settings you need to change to allow anonymous access to part of your site. You might want to do this to present a custom logon form. Read my tip on Enabling Anonymous Access to learn more.

Update SharePoint Central Admin web.config

Please note updating the web.config incorrectly can damage your SharePoint installation. Extreme care should be taken, please do not edit the web.config if you are not familiar with web.config or XML structures.

  1. Locate your web.config file for the SharePoint Central Administration website. Normally located in C:\Inetpub\wwwroot\wss\VirtualDirectories
  2. Take special care to select the right Virtual Directory, mine is named SharePointCA80 yours will be different
  3. Locate the web.config file, make a backup of it, and then open it with notepad
  4. Scroll down to the configuration node, <configuration> find <connectionStrings>


    5. <connectionStrings>
<add name="ADConnectionString" 
      connectionString="Data Source=<server name>;
Initial Catalog=<database name>;
Integrated Security=True" />
</connectionStrings>
  5. As before replace both <server name> and <database name> with their appropriate values.
  6. Scroll down to the system web <system.web> node and the following membership provider, as before
    7. <!-- membership provider -->
    <membership defaultProvider="FBADemoMember">
      <providers>
          <add
          connectionStringName="AspNetDbFBADemoConnectionString"
          enablePasswordRetrieval="false"
          enablePasswordReset="true"
          requiresQuestionAndAnswer="false"
          applicationName="/"
          requiresUniqueEmail="false"
          passwordFormat="Hashed"
          maxInvalidPasswordAttempts="5"
          minRequiredPasswordLength="1"
          minRequiredNonalphanumericCharacters="0"
          passwordAttemptWindow="10"
          passwordStrengthRegularExpression=""
          name="FBADemoMember"
          type="System.Web.Security.SqlMembershipProvider,
System.Web,Version=2.0.0.0,
Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a"   />
      </providers>
    </membership>

    <!-- role provider -->
    <roleManager enabled="true" defaultProvider="FBADemoRole">
      <providers>
          <add
          connectionStringName="AspNetDbFBADemoConnectionString"
          applicationName="/"
          name="FBADemoRole"
          type="System.Web.Security.SqlRoleProvider,
System.Web,Version=2.0.0.0,Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a"/>
      </providers>
    </roleManager>
  7. Save and close the web.config for SharePoint Central Administration
  8. Update the web.config of SharePoint Web application
  9. Repeat steps 1 to 7 for the web.config of the SharePoint web application you configured the Authentication Provider for Forms Based Authentication above, i.e. the Extranet site
  10. Check the authentication in this web.config is set to the following.
    11. <authentication mode="Forms">
<forms loginUrl="/_layouts/login.aspx"></forms>
</authentication>

Note you can specify a custom login page here, shown as loginUrl="" above.

Add an FBA user as a Site Collection Administrator

Now we need to make sure an FBA user can administer the site.

  1. Open Central Administration.
  2. Click on the Application Management tab.
  3. Click on Site collection administrators in the SharePoint Site Management section.
  4. Make sure your extranet is selected in the Site Collection drop down.
  5. Type spadmin into the Secondary Site Collection Administrator text box, then click the person icon to resolve the user.
  6. Click OK.

Note: This is one of the reasons why we needed update the SharePoint Central Administration web.config file.

Test the Extranet

Simply browse to your extranet URL and test you can authenticate using your SQL user account spadmin.

Common issues

  • DNS is not configured to resolve the Extranet address, try a simply ping from the commend line.
  • Invalid connection strings in the web.config files.
  • FBA users is not granted access to the Extranet site
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Matt Takhar Matt Takhar

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2010-08-11

Comments For This Article

















get free sql tips
agree to terms