Login failed for user '<COMPUTER_NAME>\ASPNET'

[Matthew Louden]

I wrote ASP.NET application that access SQL Server database. When I run the
application, it yields "Login failed for user '<COMPUTER_NAME>\ASPNET'"
error message.

I then did the following, but still didn't work. Any ideas??

1. In IIS console, right click the virtual directory
2. click directory security tab
3. click edit button on anonymous access
4. click browse button
5. select <COMPUTER_NAME>\ASPNET, and uncheck anonymous access

 

[Hermit Dave]

well either you will have to use sql authentication and remove windows
authentication as it stands now

or you will have to as aspnet user to have access to the database you are
trying to use

HTH

HD

 

[Jeff S]

The message is coming from SQL Server - which does not recognize the ASPNET
user... so, you need to configure SQL Server to recognize the account. You
have a few options for accomplishing this - all depending on your usage
scenario. SQL Server's Books Online has some great documentation on the
relevant issues.

You don't have to "remove windows authentication as it stands now" - there
is no such thing.

HTH

Jeff - MCDBA

 

[Hermit Dave]

FYI,

I just did a revoke on 'Select' on two tables for aspnet windows user
account
and i can still access them using asp.net

i am connecting to the db using sql authentication....

the moment i set the connection string to be using trusted connection it
fails.

so you i think i was right to an extent when i said one of the options was
to use sql authentication instead of windows authentication for sql server
conneciton string

HD

 

[Matthew Louden]

ok but can i change to sql authentication? Since this is the question when I
install SQL Server.

 

[Jeff S]

Matthew,
You may already be aware of this, but in case you're not, this might be
helpful to know:

SQL Server runs in two authentication modes:
1. Windows Authentication Mode
2. Mixed Mode (which is Windows Authentication + SQL Server authentication)

In Windows Authentication mode, SQL Server trusts/allows in only users who
have been successfully authenticated by a Windows Domain controller (in this
mode, SQL Server does not maintain its own list of user IDs and passwords
that have been defined in SQL Server - as the ability to define users in SQL
Server is disabled)

In Mixed mode, SQL Server will additionally permit any users who have been
defined in SQL Server, itself (in these cases, SQL Server maintains its own
list of user IDs and passwords that have been defined in SQL Server).

Just to be clear, there is no such thing as removing Windows Authentication
*mode*. You can switch the server from running in Windows Authentication
mode to Mixed mode - but in either case Windows authenticated users can
still be granted access to the server. In other words, you get either
Windows or Windows + SQL Server doing all the authenticating; and Windows
authenticated users are always candidates for access to your server. As Dave
correctly pointed out and demonstrated, you CAN deny object access to a user
that authenticate through Windows (e.g., domain users). Just understand that
this ability is independent of the authentication mode the server is running
in. Perhaps somewhere between my responses and his and other responses you
can get what you need : )

If you want to set up some test user in SQL Server (which can simplify
things for initial development purposes), the server will have to be running
in mixed mode.

You are correct in observing that you have to make a choice during
installation.
To set up Mixed Mode security following initial installation, you can
complete the following steps in Enterprise Manager (this is from SQL
Server's Books Online):

1. Expand a server group.

2. Right-click a server, and then click Properties.

3. Click the Security tab.

4. Under Authentication, click SQL Server and Windows.

5. Under Audit level, select the level at which user accesses to Microsoft®
SQL ServerT are recorded in the SQL Server error log:
-None causes no auditing to be performed.
-Success causes only successful login attempts to be audited.
-Failure causes only failed login attempts to be audited.
-All causes successful and failed login attempts to be audited.

I hope this gets you closer to what you need

Jeff - MCDBA