I have a feeling this is a pretty classic problem, and there's probably a variety of related problems out there - anyways, this caused me some headache today:
Scenario: I am developing an application that uses a simple .mdf database file (SQL Server Compact) to store and retreive data. The app runs well, even when taking it to other computers where SQL Server Express (SSE) is installed as a dependency application for my app. Today I installed a new version of SSE (2008 R2) on my own computer that I use to develop. Because of some clutter of old installations (2005 and 2008), I first removed the existing SSE completely, then installed the new one.
Problem: When trying to run my app after having installed the new SSE, it crashed with the following message:
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance
At first I did a compare of the parameters used to start the SQL Server service and the SQL VSS Writer with the same services on other computers where it worked. They were identical!
Solution: This posting on social.msdn.microsoft.com gave me the solution. Since I am running Vista, I renamed the following directory (instead of deleting it, to make sure I had a safe return in case it made things worse):
C:\Users\[username]\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS
I restarted my application, and voilla! I was back on track!
Reason: According to Scott J. Baldwin on the above mentioned posting, referring to Reen's suggestion B, "this error is often caused because the parent instance (for whatever reason) can't copy the system databases to the users local isolated storage folders. Sometimes it is because of a previous install of SQL Express has left files in that directory which is why Reen's suggestion sometimes works" - Thanks guys!