8

I'm using Windows 7 Beta and trying to install a web application locally. This web site uses Microsoft SQL Server 2005 Express (SQLEXPRESS) and a MDB file in the web site's ~/App_Data folder. I was instructed to configure IIS7 to use Classic .NET AppPool for this web application.

Each time the web site loads, I receive the following error:

There was an error trying to connect to the Database Server: Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.

The Internet is packed with articles written on this subject. The prevailing wisdom seems to be:

  1. Configure the SQL Express Service to use the Local System account.
  2. Delete the following directory: C:\Users\username\AppData\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

Neither of these fixes have made any impact. I have tinkered with permissions and settings for hours to no avail. Can anyone suggest a fix or help me understand how to get more detailed information about the problem.

1
  • What service account is IIS running under? Have you tried creating an account for it?
    – Brent Ozar
    May 1, 2009 at 20:48

3 Answers 3

13

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.

0
1

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

1

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
    
    

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .