Re: IIS6 Authentication Problem with SQL Server 2000

From: Tom Kaminski [MVP] ((A_at_T))
Date: 03/25/04

Date: Thu, 25 Mar 2004 09:00:31 -0500

In addition to what Ken said, why do you really need to authenticate each
user to SQL? Doesn't the AP.NET application control what each user can do?
They're not accessing SQL directly right? FWIW, in my shop we never do it
that way. We use one service account for all connections to SQL -
simplifies admin and gives us great performance with connection pooling.

Tom Kaminski IIS MVP - tools, scripts, and utilities for running IIS
"Ken Schaefer" <> wrote in message
> a) if you do this, you will lose the benefits of connection pooling, as a
> separate connection will be used for each security context (each user
> account will have it's own pool). So, this solution will not scale to a
> large number of users. It's OK if you have a small number of users
> b) the problem is double-hop authentication. When using IWA, the webserver
> does not have the user's password. It just gets a token from the DC, but
> token does not have permission to logon to network resources.
> Options:
> a) if you are using a Windows 2000 Domain, you can enable delegation. This
> allows the IIS server to impersonate the Windows account, and logon to the
> backend SQL Server. You need to use Kerberos authentication for this (not
> NTLM v2)
> b) if you are using a Windows 2003 Domain, when you enable constrained
> delegation, you can use Protocol Transition. This allows the user to
> authenticate using any of a number of mechanisms to the IIS server (eg
> Digest, or NTLM), and the webserver can still get an Kerberos token to
> to the SQL Server.
> Here are a few articles to get you started:
> Read chapter 12 from the Building Secure ASP.Net Application Book - it has
> very good information about building scalable, secure ASP.Net applications
> (eg using a trusted subsystem model):
> INF: SQL Server 2000 Kerberos support including SQL Server virtual servers
> on server clusters
> HOW TO: Configure an ASP.NET Application for a Delegation Scenario
> Authentication May Fail with "401.3" Error If Web Site's "Host Header"
> Differs from Server's NetBIOS Name
> HOW TO: Configure Computer Accounts and User Accounts So That They Are
> Trusted for Delegation in Windows Server 2003 Enterprise Edition (also
> includes Windows 2000 instructions)
> Configuring Users and Computers for delegation (there's a couple of
pages -
> use the links in the nav bar to get to them)
> Windows 2003 Protocol Transition
> Cheers
> Ken
> "corndog" <> wrote in message
> : We are running SQL Server 2000 and IIS 6 on separate machines.  We are
> building an intranet application and want to use integrated Windows
> authentication in order to identify the users and validate them in SQL
> Server because their security role determines what they can do in the
> application.  We have enabled integrated authentication on the IIS server.
> In the web.config file we have <identity impersonate = "true"/> and
> "<authentication mode="Windows" />" inside the <system.web> tags.  When we
> run the application we get the error: "Login failed for user 'NT
> AUTHORITY\ANONYMOUS LOGON'".  Article Q320354 indicates this is a bug, but
> the workaround does not solve our problem because it involves using
> annonymous access.  Is there any way to make integrated Windows
> authentication work with SQL Server?