I'm using Sql server 2005 Express.
my connection string is as fallows:
Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ccinfo.mdf;Integrated Security=True;User Instance=True
When testing this website on my local computer everything works fine. But then I uploaded it to my server... dun dun dun....
Its a godaddy virtual server running windows server 2003.
When I try to connect to the database stored in my app_data folder I get this error message:
'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. '
What does this mean?
There was a similar discussion on a different forum (SQL Server Database Engine) that hopefully contains enough information to help you on this particular scenario. You can find it at: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125227&SiteID=1.
Please, take a look to this link, and if the information posted there doesn’t help you, please let us know.
Thanks a lot,
-Raul Garcia
SDE/T
SQL Server engine
|||I figured it out.
the problem was I needed to detach my mdf file from my local sql server before I uploaded the database to the host server. thanks anyway
|||I was wondering if you could provide any more information about what was causing the problem, and how detaching the mdf files fixed it?
I'm having the exact same problem on the exact same server setup described in the original post. I have already read the other threads mentioned above and tried detaching the mdf files, but nothing seems to work.
I was able to find a work around that gets past the error, but that introduces its own strange issues. Basically, if I go into IIS Manager and set the website to impersonate a user with sufficient access rights, it works -- BUT, only if I first login to the server as that user (via remote desktop, since it's a remote virtual dedicated server). I have to do this every few hours (login as the impersonated user, that is) or the app goes back to giving the same 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."
I can't figure out what logging in accomplishes -- unless somehow that lets ASP .NET or SQL Express retrieve the user's local app path? I don't need to start or run the app from the user's account -- just login to it.
The other thing I don't understand is if user instances in SQL Server Express Edition cause the system DBs to be copied into the user's local application path (which seems to be related to this problem), where do the default NETWORK SERVICE or ASPNET accounts have the files copied? I know that ASP .NET uses the Default User when no local app path exists, but I can't confirm what SQL Server Express does. I just know that giving "Everyone" write access to the SQL Express directory under Default User does not help.
If anyone can shed any light onto this situation, I'd really appreciate it. I've spent the last few days reading through everything I can find related to this issue, but can't find an explanation. I realize that not using user instances would eliminate the problem, but this should work, and I'd really like to figure out what's going on here.
|||User instances (RANU) is a feature that was originally designed for interactive users and it requires a local profile. Typically the profile is created when a user interactively logs in a machine.
The error message suggests that the user you are impersonating in your web application has not login to this server; and from your description (things work after you login in the machine), I think this is the case indeed.
I assume you are trying to use the attach database feature in RANU, but probably a different approach may work for your scenario. For example, create the user instance with attach database using a fixed (low privileged) Windows user for your application, and allow other users to connect directly via the instance named pipe (you can find the pipe name in the management view sys.dm_os_child_instances).
The main idea behind RANU is to allow any usre in the machine to run their own (isolated) copy of SQL Server as an application running under their own context. A copy of all default databases (including master) is copied to the application path. These files are not copied from the parent instance, instead, they are copied from the SQL Express “Template Data” folder. The only exception to this rule is resource database, a read-only system database that is shared across all instances.
I hope this information helped to answer your questions, and please, let us know if you have other questions.
-Raul Garcia
SDE/T
SQL Server Engine
No comments:
Post a Comment