Getting a sql server error when tryin to access the wesite using the remote URL rather than the localhost.

Question

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.

This is the error that I am getting. If I try to access the web site using the localhost it works fine. But, when I am usinfg it through the remote URL it throws this error.

I feel there is some thing wrong woth my web.config file.

The connection string I am using

In the C# code.....

        SqlConnection conn = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Thesis_Database.mdf;Integrated Security=True;User Instance=True");

Please Help.... SqlDataReader rdr = null;

Answer 1

This is related to impersonation.

The user hitting your page is required to have a local user account on the machine, and since you have "User Instance=True", a copy of the master db will be copied to:

C:\Documents and Settings\<Username>\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS

And according to the error it seems the user you are accessing the web page with does not have an account.

Could it be that you are not running impersonation on the webserver, so it's actually trying to mount the db instance as the AppPool user?

Answer 2

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

---

> <connectionStrings>   add this to your
> web.confog file.   <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.

go to iis manager.go toApplication Pool tab. on each and every Application pool Instance Click Advandced Settins and change the User Profie to "Local System". This should solve the issue.