ASP.NET (IIS 6.0) Windows authentication/SQL Server problem

From: Nick Gilbert (news_at_nickgilbert.com)
Date: 10/03/03


Date: Fri, 3 Oct 2003 17:37:19 +0100

Hi,

On Windows 2000, I use the following connection string in my ASP.NET
applications:

<add key="ConnectionString" value="server=(local);Integrated
Security=SSPI;database=knowledge" />

I then created a login in SQL Server for the ASPNET/<machine name> user and
assigned it to a WWWUsers role. This meant that SQL Server automatically
made requests from the website under the WWWUsers role. In IIS, I then
removed anonymous access from the /admin/ folder on the website, which then
caused IE to prompt for a username and password. If I logged in as
'webadmin' and a webadmin login had been created on SQL Server for that
database in the WWWAdmins role, then it would automatically run website
queries by administrators under the WWWAdmins role.

*HOWEVER*, under Windows 2003 Server (IIS6), this seems to be broken. It
either doesn't prompt for a username and password, or if it does, it seems
to ignore it completely and SQL Server still puts me in the WWWUsers role. I
have put the NETWORK AUTHORITY/NETWORK SERVICE user in the WWWUsers role,
and the webadmin user (a valid Windows account) is still in the WWWAdmins
role.

Why is IIS 6 not behaving the same way as IIS 5.5 on Windows 2000? Have I
made a mistake somewhere or is there a way to make it work in the same way
as Windows 2000?

Thanks,

Nick Gilbert



Relevant Pages

  • Re: Granting permission to a database - need help
    ... Windows authentication, and not use an application login, as then ... Links for SQL Server Books Online: ... USE Driver Access ...
    (microsoft.public.sqlserver.programming)
  • Re: ODBC worked until Server reboot defaulted to windows authentic
    ... I got the exact spelling of the windows login with the case and I rebooted ... The odbc source is set up in windows and that connects on the test. ... The SQL Server Mgmt studio connects also but only as Windows authentication. ...
    (microsoft.public.sqlserver.setup)
  • Re: login 101..
    ... On Windows 2003, SQL Server 2005 can enforce the Windows password complexity ... Windows authentication - SQL Server uses a special protocol to ask ... user is in the list of allowed logins, ...
    (microsoft.public.sqlserver.security)
  • Re: Granting permission to a database - need help
    ... folder and it would have the database connection info. ... Windows authentication, and not use an application login, as then ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.programming)
  • RE: How to create a trusted connection
    ... You need to grant access for the Windows login by referring to the books ... is set to use Windows authentication to be able to do trusted connection. ... There are two modes of authentication in SQL Server: ...
    (microsoft.public.sqlserver.security)