Fix SQL Server Express User Instance Connection Error in Visual Studio Projects

I reinstalled SQL Server 2008 Express on my dev machine recently and ran into an error below when I tried to open and run a Visual Studio application: “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.”

One solution, which is very cumbersome, is to copy the database file to the default folder, e.g. C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA and attach the database to the SQL Server Express instance. Then you change the connection string in the project. This takes too much effort. As you’d say, there’s got to be a better way.

Yes indeed. I decided to troubleshoot the issue so that I don’t have to change the connection string. It turns out that there are two issues that caused the error.

First, change the default account to LocalSystem in SQL Server Connection Manager. The default Local Service account is used when SQL Server Express is installed.

Because the Local Service account doesn’t have permissions to access the database residing under the My Document folder, you’ll like to see an error below: A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

The second issue has something to do with the user instance. If you are not logged in as a local administrator, you need to change the User Instance to “False”. You can either modify the connection string directly or right click the database in Server Explorer, select Modify Connection to open the connection dialog box, click Advanced and find the Use Instance property at the bottom of the Advanced Properties dialog box and change it to “False”. With the change, the connection uses the LocalSystem account instead of your current user account, which may not have access rights to the local database. Problem solved!