Error: Failed to generate a user instance of SQL S

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" />

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

Even despite above answers solved the problem many people, I m still finding these falling short of the essence of the problem. Closest to it is the answer by @sohaiby above. But it wrongly refers to using Management Studio.

The error message on top of this topic tells very clearly that the problem is with generating a user instance. What instance is and how it is used is described here in every detail: https://msdn.microsoft.com/en-us/library/ms254504(v=vs.110).aspx

Personally, I encountered this problem when switching DB connection from windows authentication mode to SQL Server authentication. I solved it by just modifying a part of connection string to: "User Instance=false;" instead of "User Instance=true;", which worked fine with windows authentication.

After I changed to "User Instance=false;" my connection worked fine without any additional manipulations. I cannot insist that it will work or will be suitable in all scenarios. However, I will definitely recommend trying it before other drastic methods described above, such as erasing SQL server work directories.

Answer 2

I started getting this error this morning in a test deployment environment. I was using SQL Server Express 2008 and the error I was getting was

"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."

Unsure about what caused it, I followed the instructions in this post and in other post about deleting the "C:\Users\UserName\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" directory, but to no avail.

What did the trick for me was to change the connection string from

"Data Source=.\SQLExpress;Initial Catalog=DBFilePath;Integrated Security=SSPI;MultipleActiveResultSets=true"

to

"Data Source=.\SQLExpress;Initial Catalog=DBName;Integrated Security=SSPI;MultipleActiveResultSets=true"

Answer 3

I experienced the same error when i moved the code from one machine to another. i am using VS2010 and SQLEXPRESS 2008 comes along with it.

Trick , deleting all the contents from the following folde "C:\Users\UserName\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" worked for me.