Use ASP.NET Web Parts and Membership stuff with SQL Server 2000

By default ASP.NET 2.0 Web Parts uses SQL Express. To use this stuff with SQL Server 2000 you must configure the aspnetdb database on your SQL Server 2000 database server. Fortunately for us, Microsoft has created a utility to automate the process.

Follow these steps to setup the aspnetdb database:

1) run the utility - C:\WINDOWS\Microsoft.NET\Framework\v2.0.50215\aspnet_regsql.exe

*(this was written with ASP.NET 2.0 Beta 2 (v2.0.50215) as a reference, so with the final release of <a title=".NET" href="http://www.microsoft.com/net/" target="_blank">.NET</a> 2.0 the version number will be different.)*

2) Select the “Configure SQL Server for application services” option and click “Next”

3) Enter your server name, login credentials and the name of the database to create/edit with the aspnetdb stuff.

*(The database will hold your login and personalization data, so if you don't have complete control over the database server (if you use a remote hosting service like I do) you will want to point this utility to the database you are going to use for your web site. You know, keep all your sites data in the same database. This is the same database that the Membership Provider uses to store user credentials.)*

4) Add the following stuff to your web sites Web.config file:

*(this stuff tells ASP.NET to use your SQL Server 2000 database instead of SQL Express)*

<connectionStrings>
   <add name="SQLConnString" 
      connectionString="Data Source=SQL_SERVER_NAME;Initial Catalog=aspnetdb;Integrated Security=True"
      providerName="System.Data.SqlClient" />
</connectionStrings>

<system.web>
   <webParts>
      <personalization
         defaultProvider="SqlPersonalizationProvider">
         <providers>
            <add name="SqlPersonalizationProvider"
               type="System.Web.UI.WebControls.WebParts.SqlPersonalizationProvider"
               connectionStringName="SQLConnString"
               applicationName="/" />
         </providers> 
         <authorization>
            <deny users="*" verbs="enterSharedScope" />
            <allow users="*" verbs="modifyState" />
         </authorization>
      </personalization>
   </webParts>
</system.web>

And, that’s it! Wasn’t that simple to do. Even though it’s so simple, for some reason I could could only find one article online (http://www.ondotnet.com/pub/a/dotnet/2005/06/06/webparts_2.html) that covers this topic. So I thought I would write a little blog post about it to increase the number of content online that covers using a SQL Server 2000 database with ASP.NET 2.0 WebParts.