Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path

Question

net project in vs2010 i have a db and table inside the app_data folder, i have created a deployment package and have imported the package into an iis server that is installed on my local mechine. now i get this error message while trying to preform an insertion.

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 connection string is:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\survey.mdf;Integrated Security=True;User Instance=True

when running it inside VS2010 it`s working fine.

how can i solve this probelm ?

Answer 1

Remove the User Instance=True. You don't need RANU instances. Next, make sure the SQL Server Express service account had the proper needed rights on your app_data:

cacls <path\app_data> /E /T /G SQLServerMSSQLUser$<ComputerName>$SQLEXPRESS:F 

Answer 2

This article helped solved this issue: http://support.microsoft.com/kb/2002980

Edit From the MSDN article (in case the link disappears):

Resolution for Windows 7 and Windows Server 2008 R2

For all web applications running under IIS 7.5, regardless of their project type, carry out the following steps:

1. Run the Internet Information Services (IIS) Manager tool. This tool can be accessed either from the Administrative start menu, or by typing "inetmgr" in the Windows Start-->Search textbox and selecting the inetmgr tool.
2. In the left-hand pane of the IIS Manager tool expand the machine node. Click on the "Application Pools" node so that the application pools display in the main window of the management tool.
3. If you are troubleshooting an ASP.NET 2.0/3.0/3.5 application select the "DefaultAppPool" application pool. For ASP.NET v4 select the "ASP.NET v4.0" application pool.
4. Right-click on the selected application pool and choose "Advanced Settings"
5. In the "Advanced Settings" dialog box find the category called "Process Model". The first row in the category will be the "Identity" row.
6. Click on the "Identity" row and then click on the small button that shows on the right-hand side of the value cell. The button displays the text "…"
7. A dialog box called "Application Pool Identity" will popup. Within that dialog box there are two radio buttons. Make sure the first radio button titled "Built-in Account" is selected.
8. In the dropdown box below the radio button choose "Network Service" for the identity.
9. Click "Ok" to close out the "Application Pool Identity" dialog box.
10. Click "Ok" to close out the "Advanced Settings" dialog box.
11. At this point the changes to the application pool identity will have been saved to IIS 7.5's configuration store.

Resolution for Web Application Projects and Websites

For web application projects (WAP) and websites located in a user's Documents Folder hosted under any version of IIS running as NETWORK SERVICE, carry out the following steps:

1. First confirm that IIS has been configured to run as NETWORK SERVICE. This is the default on IIS6 and IIS7. However if you are running on Windows 7 or Window Server 2008 R2 you will first need to follow the steps above in "Resolution for Windows 7 and Windows Server 2008 R2" to make IIS application pools run as NETWORK SERVICE.
2. From a command prompt, type net stop iisadmin /y . This will cause any running ASP.NET WAP applications to shutdown.
3. Exit out of all running Visual Studio instances.
4. NETWORK SERVICE must be granted Read permission to the top-level Visual Studio folder located in your user's Documents folder. The Visual Studio folder will have different names depending on the version: "Visual Studio 2005", "Visual Studio 2008", or "Visual Studio 2010".
5. NETWORK SERVICE must be granted both read and write permissions to your project's top-level project folder. For example if your WAP project is called "MyCustomWebProject", then the folder "Documents\Visual Studio 20xx\Projects\MyCustomWebProject" needs to have read and write access enabled for NETWORK SERVICE. Or, if your webiste project is called "MyCustomWebProject", then the folder "Documents\Visual Studio 20xx\Websites\MyCustomWebProject" needs to have read and write access enabled for NETWORK SERVICE.
6. NETWORK SERVICE must be granted both read and write permissions to the App_Data folder located within your web project's directory structure.

Answer 3

The solution with me is

   - On IIS ->> Application pool
   - Select Default App Pool (it may be different of your website, please choose your pool)
   - Advanced setting
   - Identity: ApplicationPoolIdentity
   - Load user profile: change to true Restart IIS