ASP.NET 2.0 and the Provider Model, without SQL Express

By now, a lot of people have got Visual Studio 2005 installed (or maybe Visual Web Developer Express which is free for a year so grab your copy now) and are building web apps like there's no tommorow. When you install VS2005, you end up getting a bonus. SQL 2005 Express (or is it SQL Server 2005 Express? I can never remember names.) which is basically a new 2.0 version of MSDE. This is again very cool and an easy to use tool. Problem is that for some of us (and maybe I'm the only one) a lot of things people will be telling you (and tools you download) are setup for the VS2005 and SQL 2005 Express installs. For those of us that have a copy of SQL Server 2005 Developer Edition, there are some extra steps to get things working, namely in using the new ASP.NET 2.0 Provider Model.

Take for example the Starter Kits that Microsoft was touting at the launch (complete with straight-A college student building her website for a rock band called "The Windows"). These are great samples of code that you can run and create a new site with. In fact when you install them and create a new site one of the things the Welcome page in the IDE will tell you is "This site is ready to run! No changes are needed. Press CTRL+F5 to run the site". Ummm. Not quite for those of us, again, without the SQL Express setup.

The Starter Kits (and something your own apps should leverage if you're wise) use the ASP.NET Provider Model, a fancy way of handling security for your application by letting you setup roles, users, etc. Of course, all this information needs to live somewhere like, hmm, a database? (There are other providers you can hook up to things like ActiveDirectory). For the database method, you need to provision your database using a tool called aspnet_setsql.exe. This tool will create a series of tables in the database you specify so you just call some simple methods in your code (or through web.config) to restrict areas of your web application. Very slick and very handy. Get to know the Provider Model if you haven't looked at it yet.

Two things these starter kits will do is to use a local MDF file in the App_Data folder (another really cool thing ASP.NET does BTW, creating databases on the fly) and will tie you to the SQLEXPRESS instance name. Fine if you did the brain dead install but if you need a bit of a shove in the right direction (aka using SQL Server 2000 or 2005, not Express) then you need to do a couple of things. First, let's fix the connection string in web.config. By default (for the starter kits) here's the connection string it uses (this is from the Club Starter Kit):

<connectionStrings>
   <add name="ClubSiteDB" connectionString="Data Source=.\SQLExpress;Integrated Security=true;AttachDBFileName=|DataDirectory|Club.mdf;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

So this creates a connection string named "ClubSiteDB" with a data source (much like a regular connection string) but then uses a features of ASP.NET "AttachDBFileName" which will look for a database file called Club.mdf in the DataDirectory (App_Data by default). So let's change this to use a typical connection string you would use if the database was in SQL Server:

<connectionStrings>
   <add name="ClubSiteDB" connectionString="Data Source=(local);Initial Catalog=ClubSiteDB;Integrated Security=true" providerName="System.Data.SqlClient"/>
</connectionStrings>

Okay, so now it's connecting to our local SQL Server (this could be a remote name or IP address) and selecting the ClubSiteDB database. Next you need to provision your database to handle the membership and role provider. You do this by running the aspnet_regsql.exe program (from your C:\WINDOWS\Microsoft.NET\Framework\2.0\blah blah blah directory). This launches a little GUI to select the database and set it up. It's fine to set it up into the same database for your site, unless you prefer to have a separate one. Some remote hosts only let you have one database file so you decide, just be cautious of the table names and such).

Once this is setup two things are going to happen. First, you'll be able to selected the Security tab from the ASP.NET Configuration page now (previously it would give all kinds of errors like not being able to register stored procs and such). Second, you can use the Provider model in your app. You'll need to do two small modifications to your web.config to finish this off.

Add the following just below your modified connection string (still inside the connectionStrings tag):

   <remove name="LocalSqlServer"/>
   <add name="LocalSqlServer" connectionString="Data Source=(local);Initial Catalog=ClubSiteDB;Integrated Security=true" providerName="System.Data.SqlClient"/>

This name LocalSqlServer isn't just something I made up because it sounds cool, it's the name that ASP.NET will be looking for and one that's hard wired into your machine.config (thanks Microsoft!) if you had installed SQL Express (even if you didn't install it, the setting is there, yeah, very zen like). Second, you can override the Membership tag with your own personal preferences and take it from this:

<membership defaultProvider="AspNetSqlMembershipProvider"/>

To something like this:

<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="LocalSqlServer"
     applicationName="/"
     enablePasswordReset="true"
     enablePasswordRetrieval="false"
     maxInvalidPasswordAttempts="10"
     minRequiredPasswordLength="1"
     minRequiredNonalphanumericCharacters="0"
     passwordAttemptWindow="10"
     passwordStrengthRegularExpression=""
     passwordFormat="Hashed"
     requiresQuestionAndAnswer="true"
     requiresUniqueEmail="true"/>
   </providers>
</membership>

Key here is to use the LocalSqlServer name for the connection string. This will allow you to tweak the way your provider works. If you're using SQL Server 2005, things are a little secured down (by default) so if you don't set values like minRequiredPasswordLength you'll end up having to sucumb to the big, bad security settings and come up with a 215 character strong-password that isn't the same as your last 1024 ones. This just makes it a little easier for newbs, but feel free to use the default values.

That's about it and should hopefully get you building things rather than scratching your head wondering why you don't have access to your local machine. The Provider Model is probably one of the coolest features of ASP.NET because, with it, you can build secure web sites with various roles tied to security trimming the user sees. Hook that into using Master Pages and Web Parts (not SharePoint) and you can create a very dynamic site that's secure and fun. This information can be used for any ASP.NET 2.0 web app if you're trying to use your SQL setup and ASP.NET membership and role providers. Just remember:

1. Make sure you have the connection string setup correctly
2. Run the aspnet_setsql.exe program to setup your membership and role data in the database
3. Add a LocalSqlServer connection to your web.config

The only trick I'm working on now is how to do this remotely where you can't access the command line to run aspnet_setsql.exe (like where your hosting is remote). If someone has an answer let me know in the comments.

PS Scott Guthrie has an MSDN article on this that I found which explains some/most of what is here. Of course I discovered this while I was writing this post, but hey, what's another blog entry anyways. You can see Scott's article here.