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

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 ?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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 <pathapp_data> /E /T /G SQLServerMSSQLUser$<ComputerName>$SQLEXPRESS:F

Method 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
   “DocumentsVisual Studio 20xxProjectsMyCustomWebProject” needs to
   have read and write access enabled for NETWORK SERVICE. Or, if your
   webiste project is called “MyCustomWebProject”, then the folder
   “DocumentsVisual Studio 20xxWebsitesMyCustomWebProject” 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.

Method 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

Method 4

SOLUTION FOR ENVIROMENT:

Win 7, IIS 7.0, Visual Studio 2010, Database.mdf File

Open the web config and check the conexion string:

Important, make sure the AttachDbFilename is NOT followed by C:inetpub<>App_Data

<add name="conexion" 
    connectionString="Data Source=.SQLEXPRESS;
    AttachDbFilename=|DataDirectory|Database.mdf;
    Integrated Security=True;
    User Instance=True" />

In IIS 7.0 Go to ApplicationPools, find the application pool created for the application.

• Click Advanced Settings
• Go to Identity and open it Select Custom
• Account and set the PC or Server user admin account

Then go to the WebSite in IIS 7.0 and click it…

• Locate and double click the Authentication icon
• Click on Anonymous Authentication and select Edit, then Select Application Pool Identity

  Locate the ASP.NET Impersonation and edit, Select on Specify User and then enter the credentials.

Restart IIS:
cmd > iisreset/noforce
or click the reset image button

Method 5

RUN->INETMGR->

go to Sites select your Site

Then go to Right panel and look for “Basic Settings”

Then Click on “Connect as” Then Select “Specific User” Then Click “Set”
Enter Local PC User Name And Password Then Click “OK”

Then Click on “Test Setting” if you see Both options are green then Ok.

Problem Solved

Enjoy…

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0