Store ASP.net membership roles in SQL Server

Setting up ASP.net membership roles is a breeze, but if you want to use SQL server to handle the authentication you need to make a few changes. After looking up the syntax repeatedly, I decided to create a mini step by step that includes what changes need to be made in the web.config, the sql permissions and how to set up users / roles.

Assumptions: You have a working Visual Studio project and a SQL server connection working.

Using: Visual Studio 2008 / ASP.net 3.5 project

SQL Server: 2005

Step 1
From the command line (running as administrator)
From: Windows\Microsoft.NET\Framework\v2.0.50727\
aspnet_regsql.exe -E -S lrswsqlt1 -d databasename -A all -sqlexportonly c:\membership.sql

Step 2
In SQL Server
Execute the SQL created in c:\membership.sql
GRANT EXECUTE TO dbuser

Step 3

Modify the web.config (This assumes you already have a working sql connection string in place)

Under the <machineKey... tag />

<authentication mode="Forms">
<forms cookieless="UseCookies" timeout="5184000"protection="All" loginUrl="~/Login.aspx">
</forms>     </authentication>

</authentication>

<membership defaultProvider="AspNetSqlProvider" userIsOnlineTimeWindow="15">
<providers>
<clear />

<add name="AspNetSqlProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="existingconnectionstring" applicationName="/" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="true" passwordFormat="Hashed" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" />

</providers>

</membership>

At the bottom before </configuration>

<location path="Admin" allowOverride="false">
<system.web>
<authorization>
<allow roles="Administrators" />
<deny users="*" />
</authorization>
</system.web>
</location>

Step 4
In Visual Studio
Run ASP.NET configuration tool
Create a new account and a new role called "Administrators"
Assign the admin user to the new role

Step 5
File Changes: Create and drag a login control onto the form.
Login.aspx
Login.vb