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
"Matthew Louden" <ma*******@hotmail.com> wrote in message
news:ev**************@TK2MSFTNGP12.phx.gbl...
ok but can i change to sql authentication? Since this is the question when
I install SQL Server.
"Hermit Dave" <he************@CAPS.AND.DOTS.hotmail.com> wrote in message
news:Oo**************@TK2MSFTNGP11.phx.gbl... 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
"Jeff S" <Je******@asdf.com> wrote in message
news:uV**************@TK2MSFTNGP12.phx.gbl... 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
"Matthew Louden" <ma*******@hotmail.com> wrote in message
news:e7**************@tk2msftngp13.phx.gbl...
> 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
>
>
>
