Error: Failed to generate a user instance of SQL Server

Question

I'm trying to connect to an MDF. I've even gone to the lengths of re-installing sql server express entirely (it is now the only flavor of SQL installed on my box, where previously I had 05 dev and express). I've verified that the paths are all correct, and thus far my google-fu hasn't helped.

The Full exception message is: 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.

The Connection string is: <add name="CustomerEntities" connectionString="metadata=res://*/Data.CustomerModel.csdl|res://*/Data.CustomerModel.ssdl|res://*/Data.CustomerModel.msl; provider=System.Data.SqlClient; provider connection string='Data Source=.\SQLEXPRESS; AttachDbFilename=\App_Data\CustomerDb.mdf; Integrated Security=True; User Instance=True'" providerName="System.Data.EntityClient" />

Thanks in advance for any insight you might be able to provide :-)

additional info: several of the references to this error I've found online do not apply to me. For example, one I've seen is where this error occurs when trying to start the user instance over remote desktop (I'm doing this locally). While another suggests that it has to do with leftover files from an old express installation ... I've looked in the prescribed locations and not found those artifacts. I also tried running sp_configure 'user instances enabled', '1', but it said that it was already set to 1.

Answer 1

Have you tried to connect to the SQL Server instance by using SQL Management Studio?

If this also doesn't allow you to connect then it could be because the SQL service is not starting correctly.

Verify that the service is running by checking the services in the control panel.

Answer 2

ok, it works now! guess it was a compound problem ... the steps I took to resolve it are as such:

1. Changed the following property in the connection string (note the subtle difference): AttachDbFilename=|DataDirectory|CustomerDb.mdf;
2. Deleted the contents of the following directory: c:\Users\<user name>\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS. I thought I had looked for this before, but I had actually looked in the Microsoft Sql Server folder. Again, a subtle difference.

Once I did these two things, the connection started working :-D

Answer 3

Here is the exact step-by-step tutorial showing how to fix this error:

Fix error "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance."