Failed to generate a user instance of SQL Server

In IIS under Windows 7, Select the application pool and then "advanced settings." Under "process model" find "Load User Profile" and set it to true.

SQL should now load under the default app pool account.

I've had the same issues on my workstation but never on a server.

This is a temporary fix I found a while back on the web and it seems to work:

In the query editor type: exec sp_configure 'user instances enabled', 1 then: Reconfigure

Then restart the SQL Server database.

found this link today

and after a quick search on StackOverflow https://stackoverflow.com/questions/281500/error-failed-to-generate-a-user-instance-of-sql-server

Brian,

For this to all make sense, firstly take a look at the Process list for all users on the Task Manager.

w3wp.exe is the new WWW worker process. It is run in a security sandbox using the permissions of a user called DefaultAppPool.

In the old days, instead of the DefaultAppPool user, it would have been IUSR and IWAM users (see What are the IUSR and IWAM accounts for in IIS? ).

When you use an SQL membership provider, the w3wp.exe process will start a SQL Server User Instance and attach to the database specified in the connection string, commonly the App_Data/ASPNETdb.mdf. Running user-instance of SQL Server requires a Local Profile, so that temporary files have somewhere to go.

Troubleshooting steps:

1. Make sure the process w3wp.exe is run by DefaultAppPool user.
2. Make sure SQL Server has user-instances enabled (see config_value and run_value)

   exec sp_configure 'user instances enabled'

3. If not exec

   sp_configure 'user instances enabled', 1

   then: Reconfigure
4. Restart SQL Server, just in case ASPNETdb.mdf is attached by a different user instance of SQL Server.
5. Make sure local user profile C:\Users\DefaultAppPool directory exists. If not, turn on LocalProfile=True, this will create the local profile. This is done in IIS Manager, Application Pools, DefaultAppPool, Advanced Settings...
6. If you have further issues with the database, use SQL Configuration Manager to connect to the user instance and see if the db has been attached. You need to connect to the correct instance_pipe_name.

   SELECT 
   owning_principal_name, instance_pipe_name, heart_beat
   FROM
   sys.dm_os_child_instances