This evening, I upgraded an installation of SQL Server Express 2005 to its 2008 counterpart because I have a few small websites that I've developed that rely on SQL User Instances.  The upgrade was smooth enough, but I was promptly and unexpectedly greeted with an error message that I had seen before:

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, in the past to address this issue I've had to 1) stop the SQL Express service, 2) delete the user's SQLEXPRESS directory, and 3) restart the service.  The user, in this case, is the account under which the User Instance is be created.

For instance, supposing that my user name were USERNAME, this directory would be C:\Documents and Settings\USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.  Note, that on a Vista/Windows Server 2008 machine that path is addressable (due to some very ingenious reparse point (a.k.a. junction) mappings, the actual path is C:\Users\USERNAME\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.

In this particular instance, however, this wasn't working for me.  The reason was immediately obvious: my SQL Express service runs under the NETWORK SERVICE account, not USERNAME.  I verified that I could indeed host the User Instance by explicitly impersonating a priviliged user in the web.config file:

<identity impersonate="true" userName="MACHINE\Account" password="somethingSecure" />

However, when I set it back to not impersonate (as it was before the upgrade to SQL Server 2008) the User Instance would not start.

Ultimately, I did have to delete Network Service's SQLEXPRESS directory, but it's found in a completely different location:  %WINDIR%\ServiceProfiles\NetworkService\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS.  Once I took care of that directory and restarted the SQL Services my site was off to the races.