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

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

If you did and tried to find how to resolve this you probably Googled (or Binged) for hours without being able to find the solution that really works.

Well lets put an end to that. Two simple things need to be done:

Step 1. Enabling User Instances on your SQL Server installation
First we are gonna make sure we have enabled User Instances for SQL Server installation.

Go to Query Window in SQL Server Management Studio and type this:

exec sp_configure 'user instances enabled', 1.
Go
Reconfigure



Run this query and then restart the SQL Server.

Step 2. Deleting old files
Now we need to delete any old User Instances.
Go to your C drive and find and completely DELETE this path (and all files inside):

C:\Documents and Settings\YOUR_USERNAME\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

(Dont forget to replace the green text in the path with your current username (if you are not sure just go to C:\Documents and Settings\ path to figure it out).

Step 3.

• Run the Internet Information Services (IIS) Manager tool.  This tool can be accessed either from the Administrative start menu, or by typing "inetmgr" in the Windows Start-->Search textbox and selecting the inetmgr tool.
• In the left-hand pane of the IIS Manager tool expand the machine node.
• Click on the "Application Pools" node so that the application pools display in the main window of the management tool.
• If you are troubleshooting an ASP.NET 2.0/3.0/3.5 application select the "DefaultAppPool" application pool.  For ASP.NET v4 select the "ASP.NET v4.0" application pool.
• Right-click on the selected application pool and choose "Advanced Settings"
• In the "Advanced Settings" dialog box find the category called "Process Model".  The first row in the category will be the "Identity" row.
• Click on the "Identity" row and then click on the small button that shows on the right-hand side of the value cell.  The button displays the text "…"
• A dialog box called "Application Pool Identity" will popup.  Within that dialog box there are two radio buttons.  Make sure the first radio button titled "Built-in Account" is selected.
• In the dropdown box below the radio button choose "Network Service" for the identity.
• Click "Ok" to close out the "Application Pool Identity" dialog box.
• Click "Ok" to close out the "Advanced Settings" dialog box.
• At this point the changes to the application pool identity will have been saved to IIS 7.5's configuration store.