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(1). This folder's used to store information and apparently it messes up the proper working of SQL Express.
~Carlos