Failed to generate a user instance of SQL Server

Hello Everyone,
I have instaled SQLExpress September on my Windows Server 2003 Standard.
and I work on a windows XP SP2 for development using Visual studio Web Edition (Beta2) which comes with sql server 2005 too.
I've built my application and tested it on my computer and everything went fine.
But When I copied the application to the server, it showed the following 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.

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

I've read alot of threads but it doesn't seem that any of them deals with my problem.

Answer this question

Failed to generate a user instance of SQL Server

AmitJ

Dennis,

The reason for the error your are seeing is that the account under which you are trying to create a user instance of SQL Express does not have a local profile on the server. The error message suggests verifying this.

To fix this you either need to locally login using that account or adjust your impersonation settings on the application or modify the connection string.

When you are working in VS you are using an account which does have a local profile, so this problem does not exist. When however, you copy the application to the Intepub folder you are starting to run application in a different user context, thus the problem.

Describe your application in greater detail. Is it ASP.NET app Does it use impersonation Is the IIS server on the same machine where you develop the application How does you connection string look like How do you change it when you move the application

You misunderstood my previous questions.

Account SQL Server running under is not the same thing as the account you use to connect to SQL Server. Apparently, you are running SQL Server as NETWORK SERVICE. Next, profile1 looks like a hardware profile, not a user profile, what you need to check is User profiles on Advanced Tab of System properties.

You may also want to look in the error log to understand what account it tries to create a user instance under.

Boris.
richfu

Well, I was able to solve my problem as follows:
I checked my IIS Application pool that my domain uses, it appeare that I it was using an account that doesn't have a local user profile, so I changed the Application pool Identity to local system and it started working...
Thanks for everyone that has been trying to help...
Avi_zl
FYI: I get this error when the other computer on my home network (2 computers total) is turned on. It works fine when the other computer is turned off. This other computer is not used remotely (or in any way) with my SQL Server Express / VB 2005 Express application.
SteveJessup

While I might be suggesting the obvious, but have you checked what the error suggests

What account SQL Server is running under If it is a user account, does it have a user profile

If you create an app that just connects to a SQL Server instance, does it work
Nitin Patil
i did try to change "IIS Admin" Windows service to run as administrator, which has a local user profile, but it still didn't work in my case (running iis on xp professional, IIS version 5.0)

Sorry, i am new to IIS and SQl server 2005, can anyone give further help on this issue I really want to make my app. run on IIS, it ran ok on VS 2005 though.

Thanks in advance!
Gray_
Dear Sir,
Thanks for your reply, but I'm using Windows server 2003 Web Edition on my server.
Mac.

Q:have you checked what the error suggests
A:what I wrote in the first message was what I got as a suggestion
Q:What account SQL Server is running under
A:I connect using the administrator account.
Q:does it have a user profile
A:To be honest with you,
Second, SQL Server connects using "Network Service"
The error suggests checking the local user profile, I've checked it, it only shows profile1.
Q:If it is a user account, does it have a user profile
A:Yes its a user account,about user profile it has one, but do I need to do any extra configs especially for SQL express
Q:If you create an app that just connects to a SQL Server instance, does it work
A:when I create one through VS2005 and start it from VS it works fine, but when I copy it to the inetpub location it shows the error. strange!

Boris Bon thanks for trying to help, I appreciate it... but still no solution
BN25
I had this problem with the Commerce Starter Kit, running XP SP2. I changed the connection strings for both the SQLExpress DBs by setting the User Instance=False (was true). This sorted it out for development... I'll probably get into trouble with deployment like earlier in this thread, but I'll cross that bridge when I get there.
Prakash Paghdal
Hi, DennisCIS,

Yes, I have xp SP2 installed, I had to install it first before I can install VWD 2005. I may have missed something, it didn't work for me.

Regards,

mw2005
dwphilippe
Were you able to resolve this I am running into the same problem. I am using the personal site starter kit for VS2005 Express running on PWS on XP sp2. Any assitance you can offer would be appreciated. Thanks. My email is: igaydos @ ptd dot net
Stefaan Smekens

This is because of a known problem in Windows. See http://support.microsoft.com/default.aspx scid=kb;en-us;896613 for more details.

Thanks
Diego Vargas

I've had to add new line to my connection string in web.config file to fix the problem:

<connectionStrings>
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf"/>

Then the IIS application pool I used an acount that has a local user profile (local system is an example) but this could make a security hole in your system.

Hope this was helpful

Nickj_UK

Anyone made it work under IIS 5.x + XP SP2 environment or do I have to upgrade to Windows 2003 Server env.

One interesting thing is that I changed my application to use "Local impersonation", the error of "Failed to generate a user instance of SQL Server ... " is gone, but I got the other error instead as follows, I still didn't make it run on IIS 5.x + XP SP2 environment, I ran out of ideas now, if anyone could give a helping hand, it'd be very much appreciated.

Object reference not set to an instance of an object.

Line 13: 			SiteMapNode temp;
Line 14: 			temp = SiteMap.CurrentNode.Clone(true);
Line 15: 			Uri u = new Uri(e.Context.Request.Url.ToString());
Line 16: 			temp.Url += u.Query;
Line 17: 			if (temp.ParentNode != null) {

Source File: c:\my_apps\test1\Global.asax Line: 15

virtual machine

Dear mw2005,

Do you have service pack 2 installed on your windows XP its a requirement for working with SQL Server 2005, to view all the requirements:

http://www.microsoft.com/sql/editions/express/sysreqs.mspx
Failed to generate a user instance of SQL Server

Answer this question