Howto: ASP.NET Membership and Roles using SQL Server
In this short guide, I’ll show how to use a dedicated SQL Server for the Membership and Roles part of ASP.NET. Normally the membership and roles uses the local SQL Express Server and a aspnetdb in the local app_data directory of the webserver. But for many reasons (one of them central backup) I have always wanted to be able to use my existing SQL Server (in my case SQL Server 2005).
1) Create a new database on your SQL Server to hold the membership and roles.
2) In my case I’ll create a new ASP.NET website to use for this demo. The website will be called "demo_membership".
3) Populate the database you created with the membership tables. For this, you’ll use the program aspnet_regsql located in: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727 – doubleclick to launch.
If you expand the database – you should see the membership/roles tables:
The database is now reade for use.
4) Next you’ll have to edit the web.config file of your web-project to include information to use the SQL Server instead of the local SQL Express.
5) Add a connection-string that points to your membership database:
<connectionStrings>
<add name="CS_DemoMembership" connectionString="Data Source=10.0.0.64;Initial Catalog=DemoMembership;Persist Security Info=True;User ID=DemoMembership;Password=demo"
providerName="System.Data.SqlClient" />
</connectionStrings>
6) Add membership-provider (in system.web):
<membership>
<providers>
<remove name="AspNetSqlMembershipProvider"/>
<add name="AspNetSqlMembershipProvider"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
connectionStringName="CS_DemoMembership"
applicationName="/" />
</providers>
</membership>
7) Add profile-provider (in system.web):
<profile>
<providers>
<remove name="AspNetSqlProfileProvider"/>
<add name="AspNetSqlProfileProvider" connectionStringName="CS_DemoMembership" applicationName="/"
type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</profile>
Add roles-provider (in system.web):
<roleManager enabled="true">
<providers>
<remove name="AspNetSqlRoleProvider"/>
<remove name="AspNetWindowsTokenRoleProvider"/>
<add name="AspNetSqlRoleProvider" connectionStringName="CS_DemoMembership" applicationName="/"
type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add name="AspNetWindowsTokenRoleProvider" applicationName="/"
type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</roleManager>
9) The reasen why the <remove name> tags are added is, that by default, the machine.config file is read, which contains these keys. As you wish to use your own keys, you have to remove the ones read from the machine.config-file.
10) My website looks like this:
- and I wan’t to protect the "admin"-folder.
11) Fire up the ASP.NET Configuration Tool (Website -> ASP.NET Configuration)
12) Select "Security" and make sure that "From Internet" is chosen.
13) Add a user.
14) To check that the user was created select "Manage Users":
15) Add a role – and add the user to this role.
16) Secure the "admin"-folder:
17) Just to show you – the users and roles actually are in the database on the SQL Server:
18) Create a login-control on a public page.
19) Open the web-page, login, and you should be redirected to the secure-page:
