Moving ASP.NET Membership to SQL Server 2005

As I posted previously, this was going to be a longer post. But it got erased because I clicked the wrong button on my mouse, so you all get the Cliffs Notes version. But that’s ok, you’re a clever group. All three of you. 🙂

ASP.NET 2.0 Membership

As you may know, ASP.NET 2.0 comes with ASP.NET 2.0 Application Services, a motley gang of controls and APIs that offer basic building blocks for whipping together sophistication web sites with almost no code at all. For example, let’s say you wanted to create a web site that required users to register and log in before accessing certain portions, and be able to have different types of users who could access different areas, and even store personalized information about those users. In the past you’d have to code it all yourself, but not any more. There are a series of APIs that you can access and web controls that work against those APIs. For example,

Membership — stores usernames, passwords, and handles credentials
Roles — groups users in roles
Profiles — lets you store specific information (e.g. favorite color) for users
Personalization — works with Web Parts to customize web controls so users can have their own, personalized web pages (think My Yahoo)

One nice thing is the APIs are simply interfaces to the data — they don’t deal with storing it. Instead, data is persisted via various providers that communicate with the APIs. So you could choose to have the Membership API work with a Membership Provider called XMLMembershipProvider that knew how to store membership data in an XML file. You could then change your mind and switch to using a provider called MySQLMembershipProvider that stored data in a MySQL Database, and you wouldn’t need to change any code. Think of the APIs as object oriented interfaces, and the providers as implementations of those interfaces.

So Many Providers, So Little Time

ASP.NET comes with a few built-in providers (SQL Server, SQL Server Express, XML), but there’s nothing stopping you from writing your own provider if you want to store the data in a different or proprietary way. You can download Microsoft’s Provider Toolkit and have a go. As a bonus, there’s a sample Microsoft Access provider for those of you who want to/have to store your information in an Access database.

By default, ASP.NET 2.0 uses a set of providers that store data in a SQL Server Express database located in your web site’s App_Data folder. Which is nice in that your site is self-contained and you don’t need to have “normal” SQL Server. But it’s bad in that SQL Server Express is kinda wimply, many hosting providers don’t support it, and it complicates xcopy deployment because the last thing you want to do is copy your development usernames on top of the production set.

If you have SQL Server Standard/Express 2000/2005 available, you may as well use it as the provider. Here’s how.

Configure Your SQL Server for Application Services

First you’ll need to install some stored procedures and tables in whatever SQL Server database you plan on using. Thankfully, there’s a tool that makes this easy. Navigate to C:WindowsMicrosoft.NETFramework2.0.<latest> and run aspnet_regsql.exe. Perform the following steps:

Click Next.
Check “Configure SQL Server for application services” and click Next.
Connect to your database server and choose your database from the dropdown. The database you choose will have some tables and procs added for membership et al. Click Next.
Click Next to confirm.
Wait a bit, and you’re done. Click Finish.

If you can’t or won’t run aspnet_regsql.exe (e.g. your company policies require database changes to be in scripts), there are a number of “Install” .sql files that you or your DBA can run. InstallCommon.sql will need to be run first, and you’ll need to change the @dbname variable at the top of each script.

Either way, once the database is configured, you can take a look at what got added — you’ll see a number of databases starting with aspnet_ that will contain the membership information.

Add a SQL Server Provider to Your web.config

Next you want to have ASP.NET use that SQL Server database instead of the Express database.

ASP.NET normally accesses the SQL Server Express database via a provider named “LocalSqlServer,” and Scott Guthrie posted a tip on renaming that provider so that it instead points to whatever SQL Server database you want. I don’t like doing that because a) it’s tricking ASP.NET, and b) you run the risk of confusing people by having a provider named “LocalSqlServer” point to something that may not be local. But, it’s easy to do if you want to — just add the following lines to your web.config’s <connectionStrings> section:

Anyhow, we’re not going to do that. We’re going to add explicit lines for our SQL Server database. First ensure there’s a connection string in your web.config for your Sql Server.

Next, add a Membership section and provider to your web.config’s <system.web> section:

<membership>
   <providers>
   <add 
      name="MySqlServerMembershipAuthentication"
      connectionStringName="myConnectionString" 
      applicationName="/MyApplication"
      description="This is a test database" 
      requiresUniqueEmail="false"
      enablePasswordRetrieval="false" 
      enablePasswordReset="true" 
      requiresQuestionAndAnswer="false"
      passwordFormat="Hashed" 
      minRequiredPasswordLength="4" 
      minRequiredNonalphanumericCharacters="0"
      type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.3500.0, Culture=neutral, 
         PublicKeyToken=b03f5f7f11d50a3a" />
   </providers>
</membership>

Use the right connectionStringName for your database, and give it a name you like. The applicationName attribute doesn’t have to be your virtual directory, but it should be unique to your application. This allows different applications to have users with the same name in the same database without colliding. You can read more about the attributes on MSDN, but remember that if you choose Encrypted for passwordFormat, you should include your own validation and decryption keys in your web.config like I blogged about previously on passwordFormat.

Lastly, if you plan on having user roles (Admins, Members, Serfs, etc) add a roleManager section to the <system.web> section:

Tell ASP.NET to Use Your New Providers

Now let’s configure ASP.NET to use the new providers you specified. In Visual Studio, click Website->ASP.NET Configuration from the menu. You’ll see the ASP.NET Web Site Administration Tool, which allows you to create users and roles, define application settings, and choose providers. Click the Providers tab, then click “Select a different provider or each feature.” You should see your new entries there.

Go ahead and select your new providers, and click Test to make sure they’re working correctly. If you want to click the other tabs to see what features they offer, go ahead and do so.

Create a User

Let’s create a user, just for testing. Click the Security tab. Click Create User, enter some test values (I named my user “test”), and click the Create User button. It’ll say whether the user has been successfully created or not.

Assuming the user was made successfully, close the window, and take a quick look at your web.config file again. You’ll see that the attribute defaultProvider has been added to the roleManager and membership tags. You can actually set that value manually in the web.config instead of using the administration tool.

For fun, connect to your SQL Server database again and view the aspnet_Users table. You should see the new user you created. Notice how the user has a unique UserId and ApplicationId, allowing different applications to have their own separate set of users.

If you view the aspnet_Applications table you’ll see the applicationName you chose in your membership section. The user’s password and other information are stored in the aspnet_Membership table. You can poke around a bit more, then delete the user by opening up the Web Site Administration Tool, going to the Security tab, clicking Manage Users, selecting the user and clicking Delete User.

Roles and Controls

Now that you’re set up for ASP.NET Membership, you can open up the Administration Tool again to create some Roles and more users and/or you can go ahead and drag Login, LoginView, ChangePassword, etc. controls onto your web forms. You’ll find them under the Login section in the Visual Studio toolbox when editing ASPX pages. Most of the Login controls need no code at all — you can edit their templates, and they’ll automatically read the membership information from your web.config and the providers.

Directories in your site can then be protected by creating a web.config inside them and using simple authorization blocks, e.g. inserting

<?xml version=”1.0″ encoding=”utf-8″?>
<configuration>
<system.web>
<authorization>
<allow roles=”SomeRole” />
<deny users=”*” />
</authorization>
</system.web>
</configuration>

protects a directory from everyone except users in the role “SomeRole.”

Conclusion

Well, what started out as a Cliffs Notes got bigger than I expected, but that’s OK. Hopefully I’ve shown you enough to not only free yourself from SQL Server Express, but encourage some of you to dive into the ASP.NET Membership system.