How do I use Forms Authentication with SQL Server database?

From Guidance Share

Jump to: navigation, search

J.D. Meier, Prashant Bansode, Alex Mackman


Use the built-in SqlMembershipProvider along with the Login control. Membership provides a consistent and simple APIs for user authentication and user management and the Login control works with membership. The control coupled with the membership provider significantly reduces the amount of code you need to write to authenticate your users.

To use forms authentication with a SQL Server database as the user store:

  • Install the SQL Server user store database by using the aspnet_regsql.exe utility.
  • From a Visual Studio 2005 command prompt, run the following command.
  aspnet_regsql -S (local) -E -A m
* -S specifies the server, which is (local) in this example. 
* -E specifies to use Windows authentication to connect to SQL Server.
* -A m specifies to add only the membership feature. For simple authentication against a SQL Server user store, only the membership feature is required.
  • Create a SQL Server login for your ASP.NET application's process identity (or impersonated identity if your application uses impersonation) and grant it the appropriate permissions in the membership database.
  • Configure your application for Forms authentication in the Web.Config file as follows
  <authentication mode="Forms">
  • Configure your application to deny access to unauthenticated users in Web.config file as follows
     <deny users="?" />
  • Configure a database connection string in the connectionStrings section to point to the membership database in SQL Server as follows:
     <add name="MyLocalSQLServer"
         connectionString="Initial Catalog=aspnetdb;
            data source=localhost;Integrated Security=SSPI;" />
  • Configure the SqlMembershipProvider, using the connection string. Ensure that the *defaultProvider* attribute is set to the configured provider as follows:
  <membership defaultProvider="MySqlMembershipProvider" >
        <add name="MySqlMembershipProvider"
           System.Web, Version=, Culture=neutral, 
           PublicKeyToken=b03f5f7f11d50a3a" />
  • Configure password complexity rules if you need to override the defaults, which ensure a minimum length of 7 characters with one of them being non-alphanumeric.
  • Use the Login and CreateUserWizard controls to create a login page (Login.aspx) for forms authentication.
  • Encrypt the connectionStrings configuration section by using protected configuration.

More Information

For information on forms authentication using SQL Server database, see “How To: Use Forms Authentication with SQL Server in ASP.NET 2.0” at

Personal tools