I was super stoked to get Visual Studio 2010 for my Windows 7 machine but had major issues installing Microsoft SQL Server 2008 R2 to go along with it. I ran through setup and had a successful install, but when I went to create a new local SQL Server database in Visual Studio, the following extremely annoying error message would occur:
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.
I checked the SQL Server Configuration Manager. The SQLEXPRESS instance was running as NETWORK SERVICE. In Visual Stidio, the Tools/Options/Database Tools/Data Connections/SQL Server Instance Name was set to SQLEXPRESS (although I had also tried blank for the default setting).
The NETWORK SERVICE account had full control on the project's parent folder.
And yet it still didn't work. I did some Googling, er, I mean Binging, about it, and found that some user instance data is stored in a folder under your Windows account that simply needs to be deleted (or renamed, if you're a computer folder pack-rat).
On my Windows 7 machine, this folder was located at:
C:\Users\[MyUsername]\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS
I renamed it, and just like that, Visual Studio was happy and let me create my local SQL Server database. Afterwards, I went back to delete the folder for good and noticed that another SQLEXPRESS folder had been made in its place with the correct information. I know this issue also occurs in Windows XP with the path to the SQLEXPRESS under C:\Documents and Settings\COMPUTERNAME\USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS but for Windows Vista and Windows 7 users, the correct path is above.