System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server

Question

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.

score 11 · Answer 1 · edited Jan 28 '13 at 09:12

11

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.

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

edited Jan 28 '13 at 09:12

row1

5,568
3
46
72

answered Jul 06 '09 at 10:55

Roboblob

1,795
1
22
27

Great this solved mine too, but the path for windows 7 is better explained here: http://social.msdn.microsoft.com/Forums/en-US/lightswitchgeneral/thread/219f42cf-5c99-4a72-9acb-249210c835e2 – Vlax Apr 30 '13 at 09:43
Hi, thank you so much. this has solved my error too. :) – aru Jun 24 '15 at 08:28
I must have copied the folder over from a recent restore to a new drive (manually copying settings back, etc.). Removing it indeed did the trick. Is this mainly a cache directory? – James Wilkins May 29 '18 at 04:22

score 3 · Answer 2 · edited Nov 26 '14 at 09:50

3

You should add an explicit User Instance=true/false to your connection string

edited Nov 26 '14 at 09:50

Flexo

87,323
22
191
272

answered Aug 18 '08 at 05:31

Jon Limjap

94,284
15
101
152

score 0 · Answer 3 · edited Jun 13 '13 at 18:06

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"

score 0 · Answer 4 · answered Apr 25 '15 at 19:01

0

I followed all these steps but also had to go into

Control Panel > Administrative Tools > Services
Right-click on SQL Server (SQLEXPRESS)
Select the Log On tab
Select the Local System account and then click OK

Problem solved... thank you

answered Apr 25 '15 at 19:01

alketraz

171
1
10

This should be a comment or should be more complete. You followed all steps of what? – Joop Apr 25 '15 at 19:18
Apologies... this was my first post... For clarity I followed steps provided by Roboblob and also the steps listed in my post. But yes, this should have been put as a comment in the post in question. – alketraz Aug 06 '16 at 22:56

score 0 · Answer 5 · edited Mar 30 '16 at 13:57

0

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

edited Mar 30 '16 at 13:57

drneel

2,887
5
30
48

answered Oct 18 '15 at 19:02

mukhtar ghaleb

1
1
5

score 0 · Answer 6 · answered Aug 18 '21 at 16:38

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

System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server

6 Answers6

Linked