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.

Answer 1

Hi Souhil
Take a look at this thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125227&SiteID=1

Answer 2

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?

Answer 3

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.

Answer 4

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.

Answer 5

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 !

Answer 6

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.

Answer 7

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.

Answer 8

you have to

dettach the database (from the SQL administrator tool)

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

attach  database

 

Answer 9

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.

Answer 10

 

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

 

Answer 11

Check out this solution
At: http://dotnettogo.com/blogs/emad/archive/2007/06/08/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_2E00_.aspx

Answer 12

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!!!

Answer 13

 

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

 

 

Answer 14

 

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 ?

Answer 15

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

Answer 16

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

 

Answer 17

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.

Answer 18

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

Answer 19

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

Answer 20

Sudhir, do you mean 'Load User Profile' within the 'Process Model' section?