System.Data.SqlClient.SqlException: 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.
Anybody ever get this error and/or have any idea on it's cause and/or solution?
This link may have relevant information.
Update
The connection string is =.\SQLEXPRESS;AttachDbFilename=C:\temp\HelloWorldTest.mdf;Integrated Security=True
The suggested User Instance=false worked.
Here is the answer to your problem:
Very often old user instance creates some temp files that prevent a new SQL Express user instance to be created. When those files are deleted everything start working properly.
First of all confirm that user instances are enabled by running the following SQL in SQL Server Management Studio:
exec sp_configure 'user instances enabled', 1.
GO
Reconfigure
After running the query restart your SQL Server instance. Now delete the following folder:
C:\Documents and Settings\{YOUR_USERNAME}\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\{SQL_INSTANCE_NAME}
Make sure that you replace {YOUR_USERNAME} and {SQL_INSTANCE_NAME} with the appropriate names.
You should add an explicit User Instance=true/false to your connection string
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"
I followed all these steps but also had to go into
Problem solved... thank you
I have windows 8 and I test the solution
Enable user instances
exec sp_configure 'user instances enabled', 1.
GO
Reconfigure
Restart your SQL Server instance.
Delete the folder:
C:\Users\Arabic\{YOUR_USERNAME}\Local\Microsoft\Microsoft SQL Server Data
Replace {YOUR_USERNAME} with the appropriate names.
the source from Roboblob
Please note that I found Jon Limjap's answer helpful except that after I did more research I found that it only applies to database connection strings that contain AttachDBFilename, so I had to change my connection string in web.config from:
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf"
To:
connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
For details please see If add [user instances=true] to connection string, an exception is thrown
