Is the login control with sql server express a known bug

[Tony Johansson]

Hello!

Here is some background information:
I use windows XP pro with a Sql Server express 2005 database with Sql Server
Management Studio Express
I use VisualStudio(VS) 2005

I use a file system web site that is using the built in Development Web
server.
I have two aspx files one to use when logging in and the other to redirect
to when I have been successully authenticated

I have implemented forms authentication by using ASP.NET Web Site
Administration Tool which
create an ASPNETDB.MDF and a web.config file
This Web.config file look like this after having been edited
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<system.web>
<authorization>
<allow users="John" />
<deny users="?" />
</authorization>
<authentication mode="Forms">
<forms loginUrl="LoginForm.aspx" timeout="5" cookieless="AutoDetect"
protection="All" />
</authentication>
</system.web>
</configuration>


So If I run the web application from VisualStudio 2005 everything works
fine.

If I now start Internet explorer and enter this url
http://localhost/Northwind/customerdata.aspx
the login form is displayed. I enter my crededentials(userName,password) and
then this error occur
Cannot open user default database. Login failed.
Login failed for user 'HEMPC\ASPNET'.


I have used Sql Server Management Studio Express to attach to the database
located at F:\NORTHWIND\APP_DATA\ASPNETDB.MDF and
found that everything seems to be right in the ASPNETDB database.
For example user guest is defined so ASPNET can use this user.
I know this because in another data named Northwind I don't have user ASPNET
defined because user guest is used by ASPNET.

Some information concerning login control and SQL Server express that I have
found claim that there is a bug when
having error like Cannot open user default database. Login failed.
Login failed for user 'HEMPC\ASPNET'.

Does anybody have some kind information that give a solution to my problem
why I can't
use IIS and forms authentication when having the database ASPNETDB.MDF ?

//Tony

 

[Patrice]

Cannot open user default database. Login failed.

Login failed for user 'HEMPC\ASPNET'.

For now it seems just that the ASPNET account (under which IIS 5 runs by
default) is not allowed to access the ASPNETDB database.

Have you already checked this ? (the problem is
http://support.microsoft.com/kb/316989/en-us, method 3 is this article is
basically : http://support.microsoft.com/kb/815154/en-us).

It works in VS because the development web server runs under your own
account...

 

[Tony Johansson]

This article http://support.microsoft.com/kb/815154/en-us).
doesn't match SQL Server Express because for example Database Access tab
doesn't exist in SQL Server express

//Tony

 

[Tony Johansson]

When I right click on HEMPC\ASPNET under Security->Login dialog
Login Properies is shown with these categories
General
Server Roles
User Mappning
Securables
Status

Can somebody tell me what I must change in these five sections to solve
problem
Cannot open user default database. Login failed.
Login failed for user 'HEMPC\ASPNET'.
when using IIS which is using account ASPNET to access ASPNETDB.MDF

//Tony

 

[Tony Johansson]

I do have SQL Server management studio express 2005 but the description is
not possible to use because
it differ so much between the SQL Server express and the the SQL Server that
is more advanced

//Tony

 

[Norman Yuan]

You need to know a bit more on ASP.NET security, such as how to configure
the ASP.NET, IIS and which account is running the ASP.NET.

In your case, when you run the app from VS, the VS built-in web server is
used to serve the ASP.NET app, and your account (it is likely you are an
Admin user of your computer), so, you do not have a problem.

When you run the app with IIS, it is complicated issue: the account used to
run your ASP.NET app could be different. I am not going to go through all
the possibilities. For example, based on your settings as you described
(also from your other posts), if the ASP.NET app is configured to use
"Windows" authentication mode, when the IIS is allowed "Anonymous" access,
"Machine\IUer_XXXX" account would be the account to run the ASP.NET app;
when the "Anonymous" is not allowed, "Machine\ASPNET" account is used; while
"Anonymous" is not allowed and "impersonation" is enabled, the client user's
window account is used...

So, firstly, do some study to make sure, as ASP.NET app developer, you need
to make sure you know which account runs your application and you can choose
a configuration to have right account to run it. Then it wuold be easy to
give that account a proper access to resources, such as SQL Server.

As I replied you in the other post, it unfortunately proved I was correct:
your ASP.NET uses SQL Server Express' USER INSTANCE because you followed a
book example without knowing what USER INSTANCE is. Most ASP.NET examples I
have seen do a bad job by irresposibly using SQL Server Express' USER
INSTANCE without emphasizing USER INSANCE is a complicated concept and
newbie should always avoid it until having full understand it. Try to remove
"USER INSTANCE=True" from the ConnectionString and manually attach the
Northwind database to SQL Server Express instance permanetly, once for all.
Then assign access to the database to proper account (your ASP.NET running
account, you should know it as developer, be it ASPNET account or other
account YOU configured).

 

[Tony Johansson]

I added ASPNET into the admin group but it didn't solved my problem.
I have done some changes so I might be able to understand what is happening.
I hope somebody can explain the question at the bottom after reading this
text

I have four items that is interest here.
ASPNETDB.MDF
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 I didn't had 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]

I do have SQL Server management studio express 2005 but the description is

not possible to use because
it differ so much between the SQL Server express and the the SQL Server
that is more advanced

So we always go back to my personal preference that is using scripts. Try :
use master
go
create login [YourPC\ASPNET] from windows
go

use aspnetdb
go
create user [YourPC\ASPNET]
go

exec sp_addrolemember 'db_datareader','YourPC\ASPNET'
exec sp_addrolemember 'db_datawriter','YourPC\ASPNET'
GO

The steps are :
- create a login (allows to connect to SQL Server) that maps to the ASPNET
local machine account (of course replace YourPC with your machine name)
- in the aspnetdb database, create a user that maps to this login (allows
access to this particular db)
- add this user in the db_datawriter, db_datareader groups so that this user
is allowed to read and write data to this db

In the UI you have a Securirty node under which you can create logins, under
the db node you have another Security Node under which you can create users,
and by showing the user properties you have checkboxes to include the user
in particular roles.

Also you may want to show your connection string. Even with SQL Server
Express (that I'm using at home not at work), my personal preference is to
run an instance (not to attach the file as needed) especially for a web
application....

Now the ASPNET account should be allowed to access to SQL Server Express. Do
you have the same error message, another one (in both case, restate the
exact error message you get) or does it work ?

Also you may want to post your connection string. My preference for a web
application is to connect to the instance (rather than attaching the file as
needed) using the exact same connection string than if it were a full
featured SQL Server Edition...