Cannot open user default database. Login failed. Login failed for user 'HEMPC\ASPNET'

[Tony Johansson]

Hello!

I have made my problem clearer because I have added more information that
might help
to solve my silly problem that cause
Cannot open user default database. Login failed.
Login failed for user 'HEMPC\ASPNET'.

I hope somebody can explain the question at the bottom after reading this
text

I have four items that is interest here.
ASPNETDB.MDF(This database is generated automatically by the ASP.NET web
Site Administation Tool)
CustomerData
web.config
The well known Northwind database


TEST NUMBER 1 using the Northwind database running from VisualStudio
I used the code below and run from VisualStudio using the built-in
development web server and the result back was HEMPC\Tony
*********************************************************
public partial class CustomerData : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection sqlConn = null;
try
{
ConnectionStringSettings cs =
ConfigurationManager.ConnectionStrings["MyConnectionString"];
string connString = cs.ConnectionString;
sqlConn = new SqlConnection(connString);
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandText = "select suser_sname(), current_user";
sqlCmd.Connection = sqlConn;
sqlConn.Open();
string svar = (string)sqlCmd.ExecuteScalar();
Response.Write(svar);
}
catch (Exception)
{
throw;
}
}
}
Here is the connection string that was located in web.config that was used
in Test number 1
<connectionStrings>
<add name="MyConnectionString"
connectionString="Data Source=.\SQLEXPRESS;
Initial Catalog=Northwind;
Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>


TEST NUMBER 2 using the Northwind database running IIS
Was the same as Test 1 except that I run from IIS so the result back was
HEMPC\ASPNET
If I in this Test 2 didn't have any HEMPC\ASPNET defined for Security->Users
and had disable the Guest account for the Northwind database I get this
error message
Cannot open database "Northwind" requested by the login. The login failed.
Login failed for user 'HEMPC\ASPNET'.


TEST NUMBER 3 using the ASPNETDB.MDF running from VisualStudio
Was the same as Test 1 but had a different connectionstring see below in
web.config the result back was HEMPC\Tony
Why do I get this error message ???
Cannot open user default database. Login failed.
Login failed for user 'HEMPC\Tony'.
if I removed this attribute User Instance=True from the connection string
<connectionStrings>
<add name="MyConnectionString"
connectionString="Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;
Integrated Security=True;
User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

TEST NUMBER 4 using the ASPNETDB.MDF running from IIS
Was the same as Test number 3 except that I run from IIS
When I run this I got this error message.
Cannot open user default database. Login failed.
Login failed for user 'HEMPC\ASPNET'.


So as a summary we have the same Security->Logins for both the Northwind
database and the ASPNETDB.MDF
My second summary I have exactly the same definitions for Security->users
for ASPNET for both the Northwind database
and the ASPNETDB.MDF

My first question that I hope somebody can answer
If I removed this attribute User Instance=True from the connection string in
Test number 3 why do I get error message
Cannot open user default database. Login failed.
Login failed for user 'HEMPC\Tony'.

My second question why does it work so fine when I use the Northwind
database for both when running
from VisualStudio and from IIS but when I use the ASPNETDB.MDF it works only
from VisualStudio.
I mean it's just two databases with different names and a little different
connectionstring so how can the result be so completely different.

My third question and the one that is most important is why do I get this
error message
Cannot open user default database. Login failed.
Login failed for user 'HEMPC\ASPNET'.
in Test Number 4

I hope that somebody might get some bright idea about the error message
Cannot open user default database. Login failed
because in Test number 3 I got this error message
Cannot open user default database. Login failed.
Login failed for user 'HEMPC\Tony'.
when I removed this attribute User Instance=True from the connection string

Conclusion for the problem
Cannot open user default database. Login failed.
Login failed for user 'HEMPC\ASPNET'.
I mean that if ASPNET didn't have access to the ASPNETDB.MDF I would
instead receive error message something like
Cannot open database "ASPNETDB" requested by the login. The login failed.
Login failed for user 'HEMPC\ASPNET' as I got in Test number 2

//Tony

 

[Patrice]

Good point.

Check the default database for the ASPNET login in SQL Server Management
Studio (under security, select the ASPNET connection and use "properties",
the default database should be at the bottom of the ASPNET connection
properties).

The default database must be valid even if you specify explicitely the db
name in the connection string (likely because it connects first to the
default database before switching to the one defined in the connection
string).

 

[Tony Johansson]

If I rightclick on HEMPC\ASPNET under Security->Login and take properies I
have just as you say a combo box field
at the bottom. In this combobox field I have these values to choose from.
F:\NORTWIND\APP_DATA\ASPNETDB.MDF
master
model
msdb
Nortwind
tempdb

I just wonder which one should I choose. As you can understand this account
ASPNET is sometimes using
for example database Nortwind and sometimes as in my case the
F:\NORTWIND\APP_DATA\ASPNETDB.MDF
everything depends on what I use in the connectionstring.

//Tony

 

[Patrice]

And the current value was ? Just use "master" which is the default. The
connection will use what is defined in the connection string but the default
one needs to be valid (my guess is that the connection to SQL Server is
established using the default database then the database defined in the
connection string is selected, so it fails at the previous step if the
default database is not valid).

I notice that you are using a user intance
(http://msdn.microsoft.com/en-us/library/ms254504(VS.80).aspx) as the
ASPNETDB is defined by its full path. I would use the default instance
whenever I can (such as for a web application as you have full control over
the server). Not sure buf if aspnetdb is the default database it could
perhaps explain the problem (as this is a user istance, the db is not
available at all time).