ASP.Net 2.0 Community Starter Kit SQL Express Conversion to SQL 2005

The ASP.Net 2.0 Community Starter Kits make the assumption that the host machine is running SQL Express.

This is because, I presume, they’re presented under the Visual Web Developer 2005 Express Edition site.

If you’re running Visual Studio.Net 2005 and Microsoft SQL Server 2005 the starter kits will not work out of the box.

You have to add an IIS application and convert the database to SQL 2005.

The Personal Web Site goes so far as to assume the aspnetdb.mdf file is already on your drive. To create an aspnetdb.mdf file you’d normally use the Visual Studio 2005 command prompt and run aspnet_regsql.exe. Or, you can get the kit I describe below and make the kluge fix as I did. aspnetdb.mdf will contain the user roles and memberships. You can avoid aspnetdb.mdf completely by changing the second web.config sql reference to be the same as the first, i.e., both ClubSiteDB and LocalSqlServer name the same initial catalog. Create xyz database (catalog), define your tables, keys, etc., then point both ClubSiteDB and LocalSqlServer to xyz.

Here is one way to solve the problem. The most elegant way would have been to run aspnet_regsql, but I wasn’t aware of that at the time I was writing this post.

Convert an mdf Express Personal Web Site kit to SQL Server 2005
Both the Personal Web Site and the PayPal-enabled eCommerce starter kits require aspnetdb.mdf. Since The PayPal kit has a copy, we’ll install that kit first.

These examples presume:
a physical web directory root of c:\aaweb
a SQL Server 2005 instance of 6510GZ\SQLSERVER2005
an ASPNET (aka 6510GZ\ASPNET) Windows account as the aspnet_wp account
Windows XP Pro

Commerce Start Kit (PayPal):
Download the PayPal Kit: The PayPal Kit link leads to a PayPal site link which leads to CommerceStarterKit.org. Register and download the kit.

Unzip the contents using “Use Folder Names” into your physical web directory area. I unloaded my zip to c:\aaweb\commerce. Open the IIS MMC, navigation to the c:\aaweb\commerce\website directory; get the properties for the Website folder and “Create” the Website application.

Note that the application runs from Website, not from the commerce directory above Website. When you open the web in Visual Studio 2005 you will open c:\aaweb\commerce\Website.

Open Sql Server Management Studio. My SQL 2005 instance is named 6510GZ\SQLSERVER2005. In Databases:
attach c:\aaweb\commerce\website\app_data\aspnetdb.mdf. Rename the result to ASPNET (it works, though I meant to rename it ASPNETDB).
attach c:\aaweb\commerce\website\app_data\commercedb.mdf. Rename the result to CommerceDB.

For both databases:
navigate to database security
add the 6510GZ\ASPNET system user
give the user aspnet_membership_fullaccess schema and aspnet_membership_fullaccess role membership.

In Visual Studio 2005 open the website c:\aaweb\commerce\website. Change the web.config configuration from Express to Server. Remove the commercedb.mdf and aspnetdb.mdf connection string elements and replace with Server connection strings:

New connection strings:
‹connectionstrings›
‹clear›

‹add name=”CommerceTemplate” connectionstring=”Data Source=6510GZ\SQLSERVER2005;Initial Catalog=CommerceDB;Integrated Security=True;”›

‹add name=”LocalSqlServer” connectionstring=”Data Source=6510GZ\SQLSERVER2005;Initial Catalog=ASPNET;Integrated Security=True;”›

Personal Web Site
The Personal Web Site download leads to this license agreement. Download the zip, unzip into c:\aaweb\personal, using folder names to maintain the zip directory structure.

The Personal.mdf database that comes with the personal starter kit is missing the personal.ldf log file. The mdf points to a non-existant ldf.

Open SQL Server Management studio. Create a new database named Personal. Make the owner 6501gz\ASPNET.

Start a new query. Open c:\aaweb\personal\app_data\personal-add.sql. Add the command:
use personal;
to the top of the script,
and execute the script.

Open Visual Studio 2005. Open the web site c:\aaweb\personal.

Change web.config connection strings as follows (remove the existing strings):
‹connectionstrings›
‹clear›

‹add name=”Personal” connectionstring=”Data Source=6510GZ\SQLSERVER2005;Initial Catalog=Personal;Integrated Security=True;”›

‹add name=”LocalSqlServer” connectionstring=”Data Source=6510GZ\SQLSERVER2005;Initial Catalog=ASPNET;Integrated Security=True;”›
‹/connectionstrings›