Just ran into this and thought I’d share. If you are trying to connect to a "user instance" of SQL Server from your Web application running on Windows 7 or Windows 2008 R2 and you’re getting a message that looks like this:
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.
I can probably help you out.
Note: this error only happens if you have User Instance=true in your connection string. The IIS team made a change to the default identity of the worker process. Starting with IIS 7.5, Application Pools run with a unique identity based on the Application Pool name, rather than NetworkService – the default identity for IIS6 and IIS7. The primary reason for this change is to increase the security of IIS and Application Pools by default, providing a much better sandbox between Applications and other Windows services by default.
Unfortunately, the new identity does not have a user profile, and as you can see from the error, this causes the SqlClient data stack to fail. There are a few things you can do to "fix" this error: 1) switch back to NetworkService 2) switch to a user account that has a local profile (like a real user / domain user account). To do that, fire open IIS Manager and browse to Application Pools node for your computer. Click on the AppPool for the application you are trying to run and select the “Advanced Settings” task (in yellow on right). Select identity and choose NetworkService as a built-in account, or select “Custom account” and type in the user/password.
If you’re a command-line person, you can do it this way (all on one line):
%windir%\system32\inetsrv\appcmd.exe set config