Adding Membership and Roles to your MVC or ASP.NET Web Site - Application Services
published 5/12/2010,
filed under ASP.NET
There are several ways to add Application Services(membership, roles, etc.) to your web project. Depending on your deployment needs the different ways of adding these services are not all equal.
At the heart of most of the ways of adding Application Services to you sites is aspnet_regsql.exe. Application Services (and aspnet_regsql.exe) first appeared with .NET 2.0. These pieces were not included with .NET 3.0 or .NET 3.5, because these were add-ons to .NET 2.0. Now that .NET 4.0 is out, and is a standalone framework that doesn't rely on .NET 2.0, .NET 4.0 has its own version of aspnet_regsql.exe.
Regardless of which base .NET framework you are using (2.0 or 4.0) there are five elements to the ASP.NET application services: Membership, for managing user names and passwords, and secure credential management; Roles, to manage roles and map user to roles; Profile, for storing information about authenticated and anonymous users visiting your site; Personalization, for storing user control customization information, this is mostly used with WebParts; and, Web events, that can be used to collect information about errors that occur on the web site.
The simplest way to add Application Services to your web project is to simply select ASP.NET Configuration at the bottom of your Project menu in Visual Studio. Doing so will create the default database aspnetdb.mdf and modify your web config. Running aspnet_regsql.exe from the command line or from the GUI wizard will also create the aspnetdb.mdf by default, and you do have the option to specify which pieces you want to include. But, since aspnet_regsql.exe is a standalone executable it has no knowledge of your project and won't modify your web config, which you will want to do; I'll cover that later in this post.
If you are building a web site that will be deployed to a hosting service you may only be allowed a single SQL Server database, or have to pay extra for the additional database. So, this becomes the first problem, how to add the application service pieces to your database? If you attach your database to SQL Server then from either the GUI wizard or from the command line of aspnet_regsql.exe you can specify what database to use and what application service pieces to add. However, there is yet another method to add these pieces that I've found to be even easier.
Aspnet_regsql.exe calls SQL script files, you can call these same script files yourself, using just the ones you want for your site.
The SQL scripts and aspnet_regsql.exe reside in the same location(s). For .NET 2.0 this is Windows\Microsoft.NET\Framework\v2.0.50727. For .NET 4.0 the location is Windows\Microsoft.NET\Framework\v4.0.30319. And, on 64 bit Oss you will find yet another set in the Framework64 hive; Windows\Microsoft.NET\Framework64\v2.0.50727 and Windows\Microsoft.NET\Framework64\v4.0.30319.
In addition to the aspnet_regsql.exe you will find 9 Install… SQL scripts and 9 Uninstall… SQL scripts. The install and uninstall scripts are complementary to each other, the install adds pieces and the uninstall, of the same name, removes them. Under normal circumstances I've never needed to uninstall; I point them out just so you know they are there and available to you, either through aspnet_regsql.exe or manually.
Since I am only interested in the Membership and Roles pieces that's all I'm going to focus on. If you are interested in profile or personalization I'll leave it to you to go the extra step for them.
First, copy the following three scripts to another directory; I have a scripts directory at the root of all my projects and this is where I copy these files; InstallCommon.sql, InstallMembership.sql, InstallRoles.sql. The names are give-aways as to what each does. Doing a file compare between the .NET 2.0 and .NET 4.0 versions they are identical.
Using the text editor of your choice you need to make the same two changes to each file, namely supply the name of your database file in place of the aspnetdb name. Here are some snippets to show you.
From the InstallCommon.sql, lines 29 - 42
SET @dbname = N'aspnetdb'
IF (NOT EXISTS (SELECT name
FROM master.dbo.sysdatabases
WHERE name = @dbname))
BEGIN
PRINT 'Creating the ' + @dbname + ' database...'
DECLARE @cmd nvarchar(500)
SET @cmd = 'CREATE DATABASE [' + @dbname + '] ' + @dboptions
EXEC(@cmd)
END
GO
USE [aspnetdb]
Change the first and last lines shown to reflect your database
SET @dbname = N'YourDbName' USE [YourDbName]
In the installMembership.sql, lines 35-45
SET @dbname = N'aspnetdb'
IF (NOT EXISTS (SELECT name
FROM master.dbo.sysdatabases
WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
BEGIN
RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname)
END
GO
USE [aspnetdb]
Change the first and last lines shown to reflect your database
SET @dbname = N'YourDbName' USE [YourDbName]
To complete the Role and membership scripts modifications you need to make the same changes to the InstallRole.sql file.
SET @dbname = N'aspnetdb'
IF (NOT EXISTS (SELECT name
FROM master.dbo.sysdatabases
WHERE ('[' + name + ']' = @dbname OR name = @dbname)))
BEGIN
RAISERROR('The database ''%s'' cannot be found. Please run InstallCommon.sql first.', 18, 1, @dbname)
END
GO
USE [aspnetdb]
Change the first and last lines shown to reflect your database
SET @dbname = N'YourDbName' USE [YourDbName]
Save these files and run them in the SQL Server Management Studio (SSMS) against your database and it will be ready for the application Services.
There are several differences between the web.config used in MVC and the one used in an ASP.NET project. For starters the web forms project has no connection string and no membership provider information, but the a new MVC project has both a connection string, set up to use aspnetdb, and providers for membership, role and profile. If you are not using profiles in your app you can remove the profile provider section from the web config.
<connectionStrings>
<add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient"/>
</connectionStrings>
Note the Connection string name above "ApplicationServices" you will also find it in each of the providers below, the connection string name can be anything you want, but the providers need to use the same name. Since we are using our own database rather than the aspnetdb database you will need to modify the connection string to point to your database.
<membership>
<providers>
<clear/>
<add name="AspNetSqlMembershipProvider"
type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
connectionStringName="ApplicationServices"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="false"
requiresUniqueEmail="false"
passwordFormat="Hashed"
maxInvalidPasswordAttempts="5"
minRequiredPasswordLength="6"
minRequiredNonalphanumericCharacters="0"
passwordAttemptWindow="10"
passwordStrengthRegularExpression=""
applicationName="/"
/>
</providers>
</membership>
<profile>
<providers>
<clear/>
<add name="AspNetSqlProfileProvider"
type="System.Web.Profile.SqlProfileProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
connectionStringName="ApplicationServices"
applicationName="/"
/>
</providers>
</profile>
<roleManager enabled="false">
<providers>
<clear />
<add connectionStringName="ApplicationServices" applicationName="/" name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add applicationName="/" name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
</providers>
</roleManager>
When the connection string is modified to point to your database, the connection string name changed to what you want to call it, and that change rippled through the membership and role providers all you need to do now is compile your MVC project in order to use the ASP.NET Configuration menu item on the project menu (Web Site Administration Tool).
Now you have one database for your site, with the Application Services added to that database and the ability to use the Web Site Administration Tool (WSAT) from Visual Studio to set up and mange your users and roles as you develop your application. When it comes to inital deployment you can also resue the SQL scripts you modified to create the Application Service pieces in your hosted database.
