I had sql express installed initially on my development PC.  I removed it to simulate a production machine that had only SQL 2005.  I also wanted to play around with aspnet_regsql utiltiy w/ sql 2005 to understand the membership providers, etc... 

After a while I installed the sql express again to play with the starter kits.  The installation went smoothly, I could see the extra database in SQL Server Management Studio but when I wanted to add a new local database, ie SQL Express database, in the App_Data subfolder of a web project I got this error:

"Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."

"Huh??? - Everything installed perfect but still not working???"

Looked high and low for a solution and wanted to share this with you.

First you'll need to open up SQL Server Configuration Manager. Navigate to that in the menu like Microsoft SQL Server 2005 > Configuration tools > SQL Server Configuration Manager. Take a look at figure 1:

Figure 1: The SQL Server Configuration Manager tool.

Double click, or right click and choose Properties, of the selected line and you'll get the properties window which you can see in Figure 2:

You'll need to make sure that the Local system is selected.

The second part of the solution's to delete the following folder on your hard drive: c:\Documents and Settings\[user]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS⁽¹⁾. This folder's used to store information and apparently it messes up the proper working of SQL Express.

~Carlos