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):

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

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:

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

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:

    <remove name="AspNetSqlMembershipProvider"/>
    <add name="AspNetSqlMembershipProvider"
     type="System.Web.Security.SqlMembershipProvider, System.Web, Version=, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"

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.


  • Finally, someone gave the beginner's tips straight to the point saving us a lot of research time.

  • ASP.NET 2.0 will be more popular.

  • aspnet_regsql should let you register your membership and role data in a remote database, assuming you can reach it. I believe one of the command line switches will even create a .SQL file for you to run if need be.

    I really wish localSqlServer wasn't the default connection string. I'm tempted to remove it from the machine.config files on our servers because when people upload their starter kits the &quot;cannot find sql server&quot; error or whatever it throws up isn't exactly friendly.

  • Phil,

    Thanks for the info. I did find that you can use the aspnet_regsql with the -sqlexportonly option (along with the -A option to add support for all features). This will generate a SQL script which could be used remotely for setups on hosting servers.

  • I was racking the internet for a solution to installing my site on a remote ISP provider as well. They don't have SQLExpress, just SQL Server 2005. They allow the ability to run SQL Queries. I found them on my dev machine at:


    You need to change the database name in each script from ASPNETDB to whatever your database name is. Run the scripts (queries) in this order:







    I then changed the connection string to use the UID and PWD that I gave the database. Using Integrated Security=true did not work. What a pain, there must be an easier way!


  • I have access to a SQL Server (2000) on my network on another server that is set up for testing this kind of stuff. I can run the aspnet_setsql.exe program locally and it installs the aspnetdb on that instance of sql server. I'm not sure if I am experiencing:

    A) I don't know how to do is set up my web.config


    B) I can't give my local ASPNET user access to the database

    Any suggestions are appreciated?

  • aspnet_regsql.exe not aspnet_setsql.exe

  • What happens if you (not me ;-) ) change the machine.config connection string from data source=.\SQLEXPRESS; to... data source=.\SQL;?

    Would that work or will it create other problems?

  • I am trying to get the Club Sample working on SQL 2005 on a remote provier. I did what Roger Bedell said above, successfully with the scripts, but I am not sure the connection string for the UID and PW statement? Roger will you give us your example?

  • You should be able to use either of these (with the UID and PW of your account where needed):

    &quot;Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;&quot;

    - or -

    &quot;Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False&quot;

    (both connection strings produces the same result) is a handy site for this stuff.

  • I got it all going. Wow, it takes a little doing.

    Once you know how, but that is like anything. To me, I love the product, but if it weren't for this blog, I wouldn't have gotten it going. This stuff should be documented this way, explained like above within the MSDN KB.

    I should mention that I used the very same code in both connectionStrings including the Data Source, Inital Catalog, UID, PW and Integrated Security.

    MS need to brag about those scripts Roger mentions here, because they are the basic tools for setting up &quot;by the book&quot; Website security with SQL2005, using an outside host. Along with the Bil's fantastic explaination/examples on the Web.config.

    Well, it works. Thanks so much for your great blog.

  • Oh my god.. this was driving me up the wall. Great post... I have saved these links for the next guy.

Comments have been disabled for this content.