Error connecting to SQL Server from Windows 7 / Windows 2008 R2 with ASP.NET

Posted: Oct 07, 2009  6 comments  

Average Rating

Tags
Administrators
ASP.NET
Developers
IIS7.5
SQL Server
Troubleshooting

Share this Post

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.

image

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. 

image

 

If you’re a command-line person, you can do it this way (all on one line):

 

%windir%\system32\inetsrv\appcmd.exe set config  
-section:system.applicationHost/applicationPools /[name='YOUR_APPPPOOL_NAME_HERE'].processModel.identityType:"NetworkService"  
/commit:apphost

Comments

Hi Anonymous - send me mail with more details on what you are running into and I'll try to help you out.

Oct 07 2009 by bills

Hi,

 Is there a more permanent solution to this issue in the pipeline?  Web applications  are often set up to run under 'service accounts'  (rather than user accounts) that have 'login as service, and deny user login' policies applied for security and might not have a profile.  Forcing accounts to have a profile would not be an ideal long term fix, neither is dropping back to a common account such as network service as it makes it much harder to control data access from the database side of things.

Also, what is the impact of setting 'Load User Profile' to true in the above screenshot?

Oct 07 2009 by AndyW2009

KB article here:  support.microsoft.com

Oct 16 2009 by bills