Setting up ASP.NET membership and roles in development is very easy to do since out of the box, it’s already configured to use the built-in SQL Server Express provider. To set it up, you just need to launch ASP.NET Web Site Administration Tool by clicking on ASP.NET Configuration icon in the Solution Explorer or choose the ASP.NET Configuration option from the Project menu.
Once you complete the configuration, a SQL Server Express database named Aspnetdb.mdf is created locally and ready to use. While this is very practical and suitable for development, it’s definitely not the best practice for production. It is highly recommended that a full-blown SQL Server is used in the production environment instead.
For a more detailed instructions on how to setup the user and roles in localhost, refer to the step-by-step guide from Scott Guthrie, Walkthrough: Managing Web Site Users with Roles included in the Further Reading on the bottom of this post.
Setting Up Application Services in Production
Configuring ASP.NET 4.0 Application Services to Use SQL Server 2008 in production database can also be done quite painlessly. The first step is to find ASP.NET SQL Server Setup Wizard on your local ASP.NET installation. Open a command-line window by opening the Start Menu and typing ‘cmd’ (without quotes) in the search box.
Change to directory C:\Windows\Microsoft.NET\Framework\v4.0.30319
or C:\Windows\Microsoft.NET\Framework\v2.0.50727 if you’re still using .NET Framework 2.0 or 3.5.
Look for a file called aspnet_regsql.exe and run it. By default when run without any parameters, it actually uses -W (Wizard mode) argument. You can use -? argument to see the list of all available parameters.
Substitute SQLSERVERPROD with your SQL Server Production server name and YOUR_DB_NAME with the database name where you want your application services configured. Then click Next >.
Click Next > and the wizard will run for a while until you see this screen:
Click Finish and a new and empty Membership and Roles infrastructure is ready to use in your production database.
Setup Web.Config Parameters
The second step is to update the ConnectionStrings in your Web.config file. If you use Visual Studio 2010 and .NET Framework 4.0, it’s even better as you only need to add the settings for your PROD in Web.Release.config file and never need to worry about switching the parameters between the DEV and PROD afterwards.
Web.config
<connectionStrings>
<add name="AspNetSqlRoleProvider"
connectionString="Server=.SQLEXPRESS;
Database=YOUR_DB_NAME_DEV;
Trusted_Connection=yes"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<roleManager enabled="true"
defaultProvider="LocalSqlServer">
<providers>
<clear />
<add name="AspNetSqlRoleProvider"
applicationName="/"
connectionStringName="LocalSqlServer"
type="System.Web.Security.SqlRoleProvider" />
</providers>
</roleManager>
</system.web>
Web.Release.config
<connectionStrings>
<add name="AspNetSqlRoleProvider"
connectionString="Data Source=SQLSERVERPROD;
Persist Security Info=True;
Initial Catalog=YOUR_DB_NAME;
User ID=myuserid;Password=dbpassword123"
providerName="System.Data.SqlClient"
xdt:Transform="SetAttributes" xdt:Locator="Match(name)" />
</connectionStrings>The new database is going to be empty and this is what I like best as I don’t want to copy over various test user ids to production.
Further Reading:
Walkthrough: Managing Web Site Users with Roles
Configuring ASP.NET 2.0 Application Services to Use SQL Server 2000 or SQL Server 2005
Users and Roles On The Production Website (C#)
