Store ASP.NET User Security Inside Your SQL Database

This article deals with a specific problem, how to get rid of a separate ASPNETDB.MDF extraneous attached database file, and to store all your security information (users, roles) inside your custom SQL Database.

Step 01 – Create Security Tables in Your SQL Database

First, you need to create tables that will hold ASP.NET security information inside your SQL database.  There is a wizard for that.

ASP.NET SQL Server Setup Wizard is located  (Windows XP and Windows 2008 Server):

%WinDir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe

ASP.NET SQL Server Setup Wizard is located  (Windows 7 x64) in one of those:

%WinDir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_regsql.exe
 %WinDir%\ Microsoft.NET\Framework\v4.0.30319\aspnet_regsql.exe
 %WinDir%\Microsoft.NET\Framework64\v2.0.50727 \aspnet_regsql.exe
 %WinDir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regsql.exe

This self-guided wizard is very simple.   You pick your SQL Server (usually .\SQLExpress) and desired database and the wizard will create all required tables and objects to handle ASP.NET security inside your custom SQL database.

Step 02 – Generate a Machine Key

Passwords inside security tables are store encrypted.  You need to generate and record your unique machine key inside web.config file.

Visit http://aspnetresources.com/tools/machineKey and get a ready unique machine key neatly wraped in a few lines of code, ready for your web.config.  Generate your lines and place them inside <system.web> XML element:

... <machineKey
      validationKey="6B6DC7FF0657AEE33FEB36189072D99551F27E281EAEEC8B0516B188A85CF2E4C
                     4A988429765C625979A232B5BE78D6E52CB59C3675B44FCA032C24B2C49DC5"
      decryptionKey="CFED70360D049F182EA009258C34ED698A799774A2687AAE94098C71C6BF38C8"
      validation="SHA1"
      decryption="AES" />
</system.web>

Step03 – Modify Your web.config

Modify your web.config to include membership, roles and profile providers.

Place code similar to these lines bellow inside <system.web> XML element.  Replace word “Your” with an appropriate name, matching your naming conventions.

... 
<membership defaultProvider="YourMembershipProvider"> 
  <providers> 
    <add connectionStringName="YourConnectionString" applicationName="/" 
       enablePasswordRetrieval="true" enablePasswordReset="true" requiresQuestionAndAnswer="false" 
       requiresUniqueEmail="true" passwordFormat="Encrypted"     maxInvalidPasswordAttempts="5" 
       passwordAttemptWindow="10" minRequiredPasswordLength="5" minRequiredNonalphanumericCharacters="0" 
       name="YourMembershipProvider" type="System.Web.Security.SqlMembershipProvider" /> 
  </providers> 
</membership>

<roleManager enabled="true" cacheRolesInCookie="true" cookieName="YOUR_ROLES" defaultProvider="YourRoleProvider"> 
  <providers> 
    <add connectionStringName="YourConnectionString" applicationName="/" name="YourRoleProvider"
      type="System.Web.Security.SqlRoleProvider" /> 
   </providers>
 </roleManager>

 <profile defaultProvider="YourProfileProvider">
    <providers> 
       <add name="YourProfileProvider" connectionStringName="YourConnectionString" 
      applicationName="/" type="System.Web.Profile.SqlProfileProvider" /> 
     </providers>
 </profile>

Now you can run ASP.NET Configuration inside Web Developer or Visual Studio and make sure that all new users are created inside your custom database, and not in /App_Data/ASPNETDB.MDF