Like most ASP.NET developers I also first installed SQL Express 2005 together with visual studio.net 2005. After a while however I decided to get it off my dev laptop and install the full blown SQL Server 2005 instead because I wanted to test against this database and because I also needed it for an internal course at work. Things went fine, I also played around with the aspnet_regsql tool to make a dedicated database for my ASP.NET 2.0 services like Membership. I even changed the default setting for the LocalSqlServer connection string in my machine.config file in order to not be forced to override that setting in each new webproject I created.
After a while I felt the urge to reïnstall SQL Express 2005 because I downloaded several example sites and starter kits. Although it's quite easy to attach a .mdf file to SQL Server 2005, you can read my blogpost about it here: Attaching a .mdf file when you don't have the .ldf file available.
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."
Well I search around, gained some knowledge about the problem and thought I share it 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:
Figure 2: The properties window
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.