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.

Viewed 30046 times with 7 Comments

Comments

thank's ;)
— sadegh said on Saturday, May 15, 2010 6:03:46 PM
Hi , Its very nice article. One thing you have mentioned while hosting there is no option for another database. So you mean to create the tables in the Same DB which is provided for us ? Thanks, Thanigainathan.S
— Thanigainathan.S said on Monday, May 17, 2010 4:27:33 AM
how to iam preapare good software engineer
— ram mohan reddy said on Monday, May 17, 2010 3:22:36 PM
By default, many host providers only allow one SQL database with an account. To get a second database the host may charge extra. It is not necessary to place the membership and role table in its own database, they can easily be added to your main database using the information in the post above.
— Steve said on Monday, May 17, 2010 6:50:04 PM
I have two web sites on an IIS server v6. They are different domains, yet the web config file looks the same other than app DB names and the app database connection string. Do I need to change the name of the Connection String on the second domain to make this work? Didn't seem to help though unless I missed somehting else. I am using SQL Express on the Web server, and both web sites call the ASPNETDB.
— marc said on Wednesday, May 19, 2010 1:48:13 PM
My objective was to keep the Membership and Roles info seperate from the application database. By allowing the default DB (aspnetdb.mdf) to be created for each web site, I simply created a link on user name using a session variable to retrieve app data. This worked fine with only one site on the server. Now that I am trying to add a second web site to the server, there seem to be issues with using the default database onboth domains. Is there anyone that has done this without problems? Is the best practice to include Membership and Role info in the app database? I really like the security of not having it together, even if I have to write more code.
— marc said on Wednesday, May 19, 2010 1:49:14 PM
@Marc - your problem is two databases with the same name "Aspnetdb" on the server. You have at least three things you can do. Put the membership tables and associated stored procedures in the primary data database, which is the intent of my article above; change the name of one or both of the AspNetdbs so they aren't the same; or use just one AspNetdb. If you look at the Membership, Roles and Users tables in AspNetdb they have an ApplicationId, this is so you can use the same membership database for multiple applications. You will need to investigate how to do this, I've never tried it.
— Steve said on Wednesday, May 19, 2010 10:05:07 PM

Comments Closed

Upcoming PDX Events