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

    Question

  • Hi,

    I spend two days looking for an anwser to this problem but no luck. I have a simple ASP.NET 2.0 application which run correctly on my local machine.
    But After uploading all files including web.config file on a Windows 2003 Server with SQLEXPRESS installed. I have this error. Please Help!!!!!!

    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.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: 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.
    Wednesday, November 30, 2005 5:41 PM

All replies

  • Wednesday, November 30, 2005 8:12 PM
  • I did a look at the thread but I don't understand under which account the web appplication needs to run. Do I have to askl my hosting company to change the application pool account so it match my local account?

    Wednesday, November 30, 2005 9:34 PM
  • Ok, I looked and looked and couldn't figure this out anywhere. I solved this problem above but then kept getting more and more issues. Each time I went down the slippery slope of granting more and more permissions. So, I tried something else and it worked.

    Current Process:
    What ASP.NET 2.0 does by default with application settings is create SQLExpress database within the App_Data folder of the application.

    If you look closely at the default connection string (LocalSqlServer), it actually attempts to dynamically attach the .mdf to the SQLExpress instance (.\SQLExpress). That is just plain stupid from a security and infrastructure standpoint. Not only will you need to use impersonation if you have IIS Authentication set to Anonymous only (which you have to use roles and users anyway!), but that user must have administrative rights in SQLExpress and on your local box. And that doesn't even bring in troubleshooting the procedure if something fails in the attaching or creating of the database. It is a nightmare that has caused me to re-install VS2005, SQLExpress and the .NET Framework to clear.

    One Solution:
    So, what's the alternative? Since all applications will use the same database, why not just create it yourself, instead of dynamically attaching it. That way you can give the ASPNET account the permissions to access the aspnetdb database without being able to do everything else (good security alternative). Also, you'll take the whole complicated attaching process out of the mix.

    How do you do this? Well, this is what I did on Win XP running SQLExpress and VS2005 prof:

    Change ASP.NET settings

    1) Open up the iis mmc (Administrative Tools > Internet Information Services) and either right click on Default Web Site (if you want this to apply to all current and future web apps) or on the specific web app with the problem.
    2) Choose the ASP.NET tab (make sure the version begins with 2) and click Edit Configuration.
    3) On the General tab, change the LocalSqlServer connection string to:
      data source=.\SQLEXPRESS;Integrated Security=SSPI;initial catalog=aspnetdb
    4) On the Application tab, make sure you are NOT using impersonation. Check that the Local Impersonation checkbox is cleared (which it is in the default installation).

    Create and Configure the aspnetdb for use with ASP.NET

    1) To create the database, you need to choose the Visual Studio 2005 command prompt (under Visual Studio Tools menu).
    2) On the commandline, type aspnet_reqsql. This will launch a wizard. Keep clicking next. The only setting you might have to adjust is the instance name under the Select the Server and Database step. If you have any other instance of SQLExpress or SQL 2000/2005 installed, then you could use that, but then you'd have to change your ASP.NET LocalSqlServer connection string.
    3) When it is done, it will create the aspnetdb database on your SQL instance. Now, you need to configure the ASPNET account for accessing and writing to it.
    4) Open SQL Server Management Studio (if you don't have it, then download the client tools for SQL Server 2005) and create a new login for the ASPNET account.
    5) Expand the Security directory and then right-click on the Logins to create a new Login
    6) Type in ASPNET as the login name (keep Windows Auth selected)
    7) On the User Mappings page, give the ASPNET account full access (check all of the roles if you like) to the aspnetdb database.

    Then, you're done. You shouldn't have to restart anything, but iis can always use it. Start->Run, then iisreset will do the trick. Now, you have a permanent db installed in SQLExpress (or SQL Server 2000/2005 with some adjustment) with the least ammount of privilege given to the ASP.NET process.

    This setup is also a lot easier to debug later! I hope that helps.


    Tuesday, March 28, 2006 12:52 PM
  • To run the Quickstart samples, you need to modify the web.config file to match your machine configuration:

    • Bridge Services. The Bridging to External Web Services samples require a MSN application ID. You can obtain an application ID from the Windows Live Developer Center. Follow the instructions on the Windows Live web site and paste the ID you obtain into the value attribute of the MSNQuickStartAppId setting.
    • Web Services with Windows Communications Foundation (WCF). To run the WCF based web services samples, you must first install the WinFX and WCF. For installation instructions, please refer to Installing WinFX and WCF.
    • Connection Strings. The basic web.config file that ships with the installer includes connection strings intended for SQL Server Express with user instances enabled. If you want to use an existing instance of SQL Server instead, make sure to modify the connection strings as appropriate and create a services database for use with the profile and authentication services samples.
    • App_Data Folder. Make sure that the App_Data folder is readable and writeable by the web server, if you are using SQL Server Express with user instances.
    Thursday, May 18, 2006 1:44 PM
  • Having spent a couple of days with exactly the same problem (site works local, SQL errors on live server), this post got us up and runing in 10 minutes. Many thanks, great work !
    Tuesday, July 4, 2006 1:27 PM
  • I found a possible alternative method for this.   Part of the sql connection string for connecting to a file.mdb is User Instance=True.  I changed it to False and it fixed my problem.  Still used impersonation because of a seperate problem but it did work.
    • Proposed as answer by Ale Gos Tuesday, September 1, 2009 4:59 PM
    Friday, October 6, 2006 2:08 AM
  • I have spent several days also trying to work around this problem.

    The database is the aspnetdb with the membership provider.  When the site was added to the production server, it was setup using it's own aplication pool.
    I tried setting up a loggin for web01\sitename.com_web as login on the sql server but to no avail.  Finally I moved the site out of its own application pool and all worked.

    Hope this gives some help on other places to look.

    Saturday, January 6, 2007 4:46 PM
  • you have to

    dettach the database (from the SQL administrator tool)

    delete the .ldf file (inside the MSSQL folder in program files)

    attach  database

     

    Thursday, February 22, 2007 3:09 PM
  • It gave me the path to solve the problem... after going to Edit Configuration on Asp.Net tab, I saw there are TWO connection strings (the default and my own connstring). My web.config had just one. I made the two the same value and now the website is working.

     

    Really thanks for the light.

    • Proposed as answer by David Witus Saturday, June 12, 2010 3:17 AM
    Saturday, April 14, 2007 2:49 AM
  •  

    Dear All,

     

    Please note that we have only need to login on the system with any administrative login to recompile the application after you have restarted the computer on which you are running SQL Express. Actaully local user has no authentication on the domain for SQL and ASP.NET account and you can resolve this problem by just logged in system after restart with a domain administrative user.

     

    CHECK AND VERIFY..

     

    Monday, May 28, 2007 6:09 AM
  • This didn't work for me. My asp.net 2 application works just fine on my development machine. Then I upload the database to a shared hosting service, run the application, and then get the "Failed to generate..." error message. Should I delete the database, re-create it on the development machine (after following your instructions), then reupload the database to the shared environment? Pls advise. This issue has driven me crazy!!!

    Monday, September 10, 2007 1:51 AM
  •  

    I recently ran into this error as I installed our ASP 2.0 product onto a client site with Windows Server 2003 and IIS 6.0.  After twenty installs I had never seen this ridiculous thing, and from the posts had little hope of solving.  The product only uses the SQLExpress database occasionally, as basically a copy our SQL Server database.  The client required us to use an Active Directory Domain account to run the Application Pool that the product was on.

     

    The problem immediately began occuring as the Web Application attempted to attach the SQLEXPRESS database.  I temporarily fixed the problem as I would then log into the machine that the application was running on as the AD Domain Account for the Application Pool and the program would run fine.  Not a very long term solution, nor was this as easy error to troubleshoot.  I mean, the App Pool is running as the user why couldn't it always attach the database...

     

    After traversing the replys and fixes, I knew this had nothing to do in this situation with default ASP databases or ASP.NET configuration.

     

    I fixed this problem by running SQLExpress as the AD Domain Account for the Application Pool.  By opening Start -> Program Files -> Microsoft SQL Server 2005 -> SQL Server Configuration Manager.  Double click SQLEXPRESS and have it run as the domain account.  Hit apply and restart the service and it works. 

     

    Still don't know why this worked...

     

     

    Monday, February 18, 2008 5:28 PM
  •  

    Dear All,

     

    Also, I am getting the same error in the following cas:

    1. Using Impersonation with a Domain Account,

    2. Connecting the a SQLExpress Database in a File in a Local Web Folder,

    3. The SQLExpress Service is running under a Local Account.

     

    I solved the problem by openning "SQL Server Configuration Manager" and changing the Service Account to run under a Fuly Authorized Domain Account.

     

    Another solution: Attach the Local MDF File to a SQL Server Standard Edition, and connect to it from the Web Application.

     

    Hope this will help others too.

     

    Can some one explain to me why I have to make the SQL Express Service run under a DOMAIN Account, where this is not necessary for the case of SQL Server Service Standard Edition ?

     

    • Edited by tarekahf Monday, February 1, 2010 7:53 AM I need to subscribe to this thread.
    Tuesday, June 24, 2008 9:13 AM
  • Just sharing, solved it for me with SQL 2000, 2005 and express on the same box, http://blog.krisvandermast.com/FailedToGenerateAUserInstanceOfSQLServerDueToAFailureInStartingTheProcessForTheUserInstanceTheConnectionWillBeClosed.aspx

    Hope it helps someone,
    Dan
    Saturday, July 5, 2008 10:00 PM
  • In IIS -> click the default web site under this mywebserver(webserver) folder right click then asp.net Tab -> edit configuration -> general tab there you can see the connection string i have edited that user instance = true and changed to false now its working fine for me !!

    Thanks

     

    • Proposed as answer by sunshine vn Tuesday, December 22, 2009 12:33 PM
    Thursday, August 14, 2008 7:11 AM
  • None of this works!!!!!.. i am ready to kill someone... i have been at this for 7 hrs straight and have to get this damn piece of s@#$ MS product running.
    Wednesday, June 10, 2009 2:44 AM
  • I do understand your frustration, and I also had the same pain. Please try this as an impersonator; I had similar problem, then read this article http://msdn.microsoft.com/en-us/library/aa292118(VS.71).aspx. I used windows standard web server 2008/Visual Studio Web Experss 2008/IIS 7.0. Add this wthin system.web block '<'identity impersonate="true" userName="Adminsname" password="mypasword"'/' '>' Hope it works for you. Manas Mukherjee
    Monday, October 12, 2009 12:24 PM
  • I was also struggling with the same problem. I was using IIS 7. But after i set my local user profile to "true", it worked.
    Get into ISS manager, under applications pools select DefaultApppool. Click on advance setting tab, then locate local user profile and set it true.
    You will be out of trouble.
    Enjoy :D
    • Proposed as answer by GameHex Thursday, February 9, 2012 3:00 AM
    Saturday, November 7, 2009 8:05 AM
  • Sudhir, do you mean 'Load User Profile' within the 'Process Model' section?
    Thursday, November 12, 2009 12:09 AM
  • It's working ............. thank UUU ...

    Tuesday, December 22, 2009 12:25 PM
  • The post near the top by thePrisoner06 essentially had everything to fix my problem but I thought i was supposed to add a user that already existed in the system called ASPNET. Unknown if it is due to the fact that I am running Windows 7 but that user doesn't exist. After I finally created a user (doesn't matter what it is called) and edited the connection string to use the specific user and password (I used SQL Server authentication) it finally worked. Thanks for everything.

    Visual Web Developer 2008 Express X64
    Windows 7 Ultimate X64
    • Proposed as answer by KoOSK Monday, January 11, 2010 1:30 PM
    Thursday, December 31, 2009 9:03 PM
  • ^^ FIRSTLY ^^ I never meant to propse the above post as the answer! I clicked the wrong button

    Anyway for anyone who has this issue that has not managed to solve it yet by deleting the folder etc Then you may be experiencing the issues I did and the solution was to setup a new application pool in IIS that was dedicated for that one problematic website.

    If your unsure how to do this then follow the guide below:

    FYI - I am running a dedicated server with multiple websites being hosted - The server is windows 2008 and running IIS 7.0

    • If your running IIS - Load IIS manager and go to Application Pools
    • Create a new application pool (name it anything you like)
    • Right click on the newely created application pool and set "Identity" = "Local System"
    • Once the above is complete go to the application pool that houses all your websites as standard - Right click and go to "View Applications"
    • When the apps load right click the website having the issue and click "Change Application Pool" - Change it to the newely created one.
    • Done!
    Hope that helps,
    • Proposed as answer by KoOSK Monday, January 11, 2010 1:38 PM
    Monday, January 11, 2010 1:38 PM
  • Thank you Sudhir A,  you helped me much. It did work. Thank you again for saving me one day!
    B/R
    Sunday, January 17, 2010 2:31 PM
  • KoOsk, Thanks for the info.  I have been fighting this problem for a few weeks in my development environment:  Windows 7, IIS 7, VS Web Developer Express 2008, SQL Express.  Here are all the things I had to do to be able to access the database from a web page.  This is compiled from many other posts from various places:

    1.  Configure SQL Express to run as local system:
    • Start Sql Server Configuration Manager.
    • Select Sql Server Services.
    • Right click on Sql Server (SQLEXPRESS) and choose Properties.
    • On the Log On tab, under Log on as, choose Built-in account, and select Local System from the drop-down.
    2.  Configure IIS with an application pool to run as local system.
    • Open IIS Manager
    • View the application pools.
    • Either create a new application pool (call it anything you like)  OR  Select the application pool that VS Web Developer created for your web site.
    • Right click and select Advanced Settings.
    • Under the Process Model group, change the Identity setting to Local System.
    3.  Configure IIS so that your web site to uses the newly configured application pool.
    • In IIS Manager, click on your web site and choose Advanced Settings.
    • Under the General group, change the Application Pool to the one you configured in step 2.
    I hope this helps.
    Sunday, January 17, 2010 8:39 PM
  • Thank u

           Its not working for me.. After I followed your steps I got following error when i click asp.net configuration tool  tab

    There is a problem with your selected data store. This can be caused by an invalid server name or credentials, or by insufficient permission. It can also be caused by the role manager feature not being enabled. Click the button below to be redirected to a page where you can choose a new data store.

    The following message may help in diagnosing the problem: Unable to connect to SQL Server database.

    please Any one can provide solution for above error...??
    windows&
    • Proposed as answer by Saturnine2010 Thursday, January 28, 2010 10:04 PM
    Monday, January 18, 2010 2:56 AM
  • I, too, had the same problem. I tried all of the solutions provided on the web (that I could find) and still the same error message:

    Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

    It occurred to me that the problem just might lie in my user account, in spite of it being an administrator user. I'm using XP Pro, and so I logged in as administrator on my machine. Lo and behold, it all works fine.

    I don't know if this solution will work for everyone, but it has for me.

    Good luck
    Thursday, January 28, 2010 10:32 PM
  • in my application i m getting this error..

    Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.


    i try many things.. search many blogs but no succes..

    i m using Windows 7 home premum, so i m not using IIS for this application.

    other configration
    SQL 2005 express, VS2008


    on first page of my application i m reading some data from database .. it is working fine at that time..

    but on login page.. when user is success in login..

    i write following code

                    FormsAuthentication.SetAuthCookie(this.LoginUser.LoginName, false);
                    FormsAuthentication.RedirectFromLoginPage(this.LoginUser.LoginName, false);


    after this it is giving above error.


    this code applicaiton is working fine on other pc.


    pls... help..


    thanks..
    Sunday, February 7, 2010 1:25 PM
  • Thank you so much!! This worked like a charm, crystal clear and easy to understand. Thanks a million!!
    Monday, February 15, 2010 3:57 PM
  • Thank you very much.. I was working on it for 9 hours continuously, but I cudnt fix this problem I have tried many ways but user instance was a problem which would not go. Ur post has helped me.... Thanks again
    Thursday, April 1, 2010 1:56 AM
  • I found the answer on the microsoft website for individuals running windows 7 and windows 2008.

    (WHEN using the sample web application using visual studio 2010)  iis settings for 7.5

    http://support.microsoft.com/kb/2002980

    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.

    3.    Click on the "Application Pools" node so that the application pools display in the main window of the management tool.

    4.    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.

    5.    Right-click on the selected application pool and choose "Advanced Settings"

    6.    In the "Advanced Settings" dialog box find the category called "Process Model".  The first row in the category will be the "Identity" row.

    7.    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 "…"

    8.    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.

    9.    In the dropdown box below the radio button choose "Network Service" for the identity.

    10.  Click "Ok" to close out the "Application Pool Identity" dialog box.

    11.  Click "Ok" to close out the "Advanced Settings" dialog box.

    12.  At this point the changes to the application pool identity will have been saved to IIS 7.5's configuration store.

     couple more things I had to do after this:

    Cannot open user default database. Login failed.
    Login failed for user 'NT AUTHORITY\NETWORK SERVICE'.

    so to correct this problem:

    Attach the database using the sql server studio so we can assign NT AUTHRITY\NETWORK SERVICE  login privledges

    NOW THAT THE NT SERVICE ACCOUNT HAS LOGIN PRIVLEDGES TO THE SQL EXPRESS DATABASE

    RESTART THE SQL EXPRESS ENGINE

    RESTART IIS

    (OR JUST REBOOT YOUR MACHINE TOTALLY)

    Thanks,

    Doug Lubey of Louisiana www.douglubey.com

     

    Saturday, April 17, 2010 3:14 AM
  • I was also struggling with the same problem. I was using IIS 7. But after i set my local user profile to "true", it worked.
    Get into ISS manager, under applications pools select DefaultApppool. Click on advance setting tab, then locate local user profile and set it true.
    You will be out of trouble.
    Enjoy :D


    You'r the man Sudhir;

    Thanks

    Thursday, May 20, 2010 11:47 AM
  • Thank u very much thePrisoner06. Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals

     

    Its really very helpful.........


    Friday, May 28, 2010 11:30 AM
  • THANK YOU! AAAAAAAAHHHHHHHHHHHHHHHH  eFlyer, thank you!
    Love, praise, and worship God :)
    Monday, May 31, 2010 2:25 AM
  • Thanks danatcofo

    I change instance to false and it work.

     

    Thursday, July 8, 2010 4:43 AM
  • am getting this error in the web page now

    Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.

    Thursday, July 8, 2010 4:56 AM
  • Wow this problem has given me so many headaches over the last few weeks. I get it fixed by trying numerous fixes like deleting the SQLEXPRESS folder under appdata not sure how Ive fixed it then the error returns.

    Narrowing it down its always been a permission problem within IIS 7.0. I have finally found a fix for this on my setup which is windows 7 with sql express 2008 r2 and as I said IIS 7.0

    http://support.microsoft.com/?kbid=2002980

    Hope it helps someone

    Cheers

    Tuesday, July 27, 2010 7:54 AM
  • Hey Thanks ...it worked
    Saturday, August 28, 2010 9:19 AM
  • HI Souhil 

    Even i am facing the same problem .Did you got any solution for this ..

    It would be grateful to you if you help me 

     

    Thanks and regards

    Ravi 

    Wednesday, December 1, 2010 3:06 PM
  • Hi,

    The thing that worked for me is:

    The iis process is started as another user, so it doesn't has any rights to the 'my  documents' directory, because my visual studio projects are located in 'my documents\visual studio 2010\projects'.

    So in the configuration in iis i changed the impersination of the iis to my user account, so it can access 'my documents'

     

    regards,

    Jeroen


    Jeroen
    Tuesday, March 1, 2011 7:17 PM
  • @ KoOSK... I do not understand yet why this worked but it did. I laughed when I saw it because it made no sense to me. thank you.
    Wednesday, April 20, 2011 9:39 PM
  • Greate  Thank you very much ,My site is working now ! 
    Monday, August 29, 2011 6:01 AM
  • Sudhir you are really great. I looked couple of other sites for this error and was quite frustrated. You gave a very simple solution and it worked. Thanks a lot.................
    Wednesday, August 31, 2011 2:43 AM
    • Edited by Keith Wanta Friday, September 9, 2011 10:13 PM
    • Proposed as answer by Tom_sizmore Sunday, September 30, 2012 4:08 PM
    Friday, September 9, 2011 10:11 PM
  • this worked fine for me thanks keith
    Sunday, September 30, 2012 4:09 PM