CODEWITHSUNDEEP
.netsql-serverado.net
Mark Cidade
Mark Cidade

Reputation: 99957

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

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.

Upvotes: 5

Views: 16031

Answers (6)

user8128167
user8128167

Reputation: 7676

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

Upvotes: 0

mukhtar ghaleb
mukhtar ghaleb

Reputation: 1

I have windows 8 and I test the solution

  1. Enable user instances
    exec sp_configure 'user instances enabled', 1.
    GO
    Reconfigure

  2. Restart your SQL Server instance.

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

Upvotes: 0

alketraz
alketraz

Reputation: 301

I followed all these steps but also had to go into

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

Problem solved... thank you

Upvotes: 0

Jon Limjap
Jon Limjap

Reputation: 95432

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

Upvotes: 3

Hamid Shahid
Hamid Shahid

Reputation: 4616

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"

Upvotes: 0

Roboblob
Roboblob

Reputation: 1795

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

Upvotes: 11

Related Questions