When I try to run an MVC 2 app on my local IIS 7, I keep getting this error:
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.
However, I don't have anything connected to SQL Server Express, and all my connection strings work fine when I'm running on my localhost.
What is the cause of such an error? How can I prevent it?
ASP.NET applications, including MVC 2 ones, by default create a SQL Server database to store users and roles. The database is called ASPNETDB and is stored in App_Data folder - if it is not there yet, ASP.NET will try to create it when the application starts.
To create/open this database, User instance of SQL Server Express is used. To start a User Instance the user profile must be loaded for the current user (in this case whatever account the ASP.NET application pool runs as). But the default configuration for IIS application pools is not to load the user profile to limit start time time and save memory.
To enable loading user profile for an application pool go to its configuration in IIS Manager and look for Load User Profile switch in Advanced Options. Set it to true and it should work.
If you would rather avoid using User Instances and loading the user profile, you can use a different database for your application's users and roles. Just go to IIS Manager again, find your Web Site, and look for Connection Strings section in ASP.NET configuration. I bet you will see a connection string called LocalSqlServer there. Just update it to point to the database you want to use. You can see this thread to learn how to create a new ASPNETDB database. If you create it on the main SQL Server instance you will not need the profile to connect to - just create a login in SQL Server and make sure your connection string is using it.
