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

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.

Letta

Answer 1

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

Letta

Superawesome. That did the trick for me as well! I searched for several articles and somehow they all got the path wrong. The important part is the AppData in your user folder. Thanks for saving me from going crazy! – Steeplejack

Worth nothing this post comes up in results as "entity framework." I'm using LINQ to SQL (not Entity Framework), and upgraded to SQL Server Express R2, and step 2 fixed my issue. None of my Visual Studio projects could connect to SQL Express after the upgrade until performing this fix. The particular framework doesn't matter - it appears to be a generic SQL Server Express problem. Thanks! – Salmanazar

I was actually about at the point of doing a clean windows install when I found this, #2 got me all fixed, thanks Joel. – Hoenack

Likewise on #2 - exercised some old MS voo-doo. – Mullein

I did #2, now I receive the following error: " A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)". I can't even access the SQL server form SSMS anymore... really regret trying this right now. – Koo

Answer 2

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.

Skylight

pretty sure this folder had files from an older version of sql express. – Passbook

Thank you. This saved me a day. – Berrios

Answer 3

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.

Forwarder

This should be the marked answer. – Jardine

Answer 4

In addition to the other solutions here, this one may also be helpful. Ensure the Application Pool is running as network service, and not ApplicationPoolIdentity.

This solution was found here: http://blogs.msdn.com/b/webdevelopertips/archive/2010/05/06/tip-106-did-you-know-how-to-create-the-aspnetdb-mdf-file.aspx

Adverse

Answer 5

As noted by others, deleting the contents of directory: c:\Users\\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS solved it for me. A note that may help others, on Windows 7, browsing the c:\users\username dir from document browser does not show the the AppData folder which threw me off for a while (as I thought I did not have the AppData dir) until I discovered that it does indeed exist (but does not show in the document browser in Windows 7), you just have to type in the full path name to get to it.

Stupe

It just hidden pal, go to tools -> show hidden files and folders will work – Lamed

Answer 6

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" 

Latonia

Answer 7

I phase this problem with my mdf file in windows form application and i do just restart my computer and my problem is solve.

Sitra

Answer 8

Just go to web.config file. Change in 'Connection String'="....../.../......?.......; User Instance=True'" to "....../.../......?.......; User Instance=False'"

Yes this may cause some security issues, but for some school/college practical-project(Which was my case) this can run your project.

change from User Instance=True to User Instance=False , in web.config

Plebeian

Answer 9

I have faced the issue when i was using SqlLocalDB and SqlExpress. Cause of the issue: When I connect to localdb from Visual Studio, a LocalDB instance is started for it and runs as our Windows account. But when Web Application, running in IIS as "Network service" or "AppPoolIdentity", is connecting to LocalDB, another LocalDB instance is started for it and is running as "Network service" or "AppPoolIdentity". As a result, even though both Visual Studio and Web Application are using the same LocalDB connection string, they are connecting to different LocalDB instances. Obviously the database created from Visual Studio on our LocalDB instance will not be available in Web Application's LocalDB instance. Solution: So, here i have list out the solutions for both cases (i.e) SqlExpress and LocalDB If we are using LocalDB as the datasource:

1. Set the application pool identity as "Network Service"
2. Update the applicationHost.config file as to include the below highlighted settings:

<processModel identityType="NetworkService" loadUserProfile="true" setProfileEnvironment="true" />

3. Change the SqlLocalDB as shared instance by running the below command in CMD.exe in admin mode SqlLocalDB.exe share "MyLocalDB" "MySharedLocalDB"

(Here "MyLocalDB" is my local db instance. You can find the same by executing the following command in CMD.exe in admin mode SqlLocalDB.exe) Thus, my connection string is as follows: <add name="DefaultConnection" connectionString="Data Source=(localdb)\ .\MySharedLocalDB;AttachDbFilename=|DataDirectory|\aspnet-DemoSite-20200716010415.mdf;Initial Catalog=aspnet-DemoSite-20200716010415;User ID=sa;Password=welcome123;User Instance=false"

4. Since I host the application in IIS, i make sure that the account "NT AUTHORITY\NETWORK SERVICE" have read/write access to the application root folder to avoid "Access Denied" exception and enable the account as System admin to access SqlInstance by executing the below SQL query exec sp_addsrvrolemember 'NT AUTHORITY\NETWORK SERVICE', sysadmin

If we are using SqlExpress as the datasource:

1.Make sure that the connection string has proper value as below: <add name="DefaultConnection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\aspnet-DemoSite-20200716010415.mdf;Initial Catalog=aspnet-DemoSite-20200716010415;User ID=sa;Password=welcome123;User Instance=false" 2.As highlighted above Jane, delete old files which located in C:\Users<userName>\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

Coed

Answer 10

I experienced a similar issue while running on IIS express, asp.net web application c# code that connects to local database .mdf file using SQLEXPRESS engine. I also tried to delete old files located in C:\Users\AppData\Local\Microsoft\Microsoft SQL Server Data\SQLEXPRESS this didn't work. However in visual studio in the applications web site properties on enabling Windows Authentication in properties setting (Enabled), the data Connection to the database worked.

Enable Windows Authentication on Visual Studio GUI

for IIS enable Windows Authentication; by performing a server-side configuration with the IIS Manager

1. In IIS Manager, select the IIS site under the Sites node of the Connections sidebar.
2. Double-click Authentication in the IIS area.
3. Select Anonymous Authentication. Select Disable in the Actions sidebar.
4. Select Windows Authentication. Select Enable in the Actions sidebar.

or in your web.config use the below configuration

<system.webServer>
  <security>
    <authentication>
      <anonymousAuthentication enabled="false" />
      <windowsAuthentication enabled="true" />
    </authentication>
  </security>
</system.webServer>

I use the below configuration for datasource connection to the database

<add name="defaultConn" connectionString="Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|\DB.mdf;Integrated Security=True;User Instance=False;Trusted_Connection=Yes" providerName="System.Data.SqlClient"/>

Courtier

Answer 11

To fix this, please Open the SQL Server Management Studio Express. In the query editor type this text:

sp_configure 'user instances enabled', 1;
 RECONFIGURE

Run it. Then restart the SQL Server database.

Ignatia

If sql server instance is not generating , how could you open Management Studio? – Lamed

Answer 12

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.

Diapason