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?

Relevant Pages

  • RE: Beginners Questions
    ... We do use Windows form on the presentation layer which is on ... terminal server and call web services on the business logic side. ... of using "proxy" authentication on SQL Server. ... > I have written an app with a Windows Forms UI that is deployed to clients ...
  • Re: Windows Authentication and software in a hosted environment.
    ... What type of authentication are you using for the VPN? ... If you are using something like ISA server then the user is passing windows ... In my experience with hosted DB solutions almost always SQL auth is used. ...
  • Re: Accessing SQL Server w/ Forms on Intranet
    ... Windows Integrated Authentication as that is what our network runs on. ... > of authentication are you using on SQL Server - Windows? ...
  • Re: Connecting to SQL Without Windows Authentication
    ... Are you connecting to the SQL ... Server via named ... >> I have a Windows 2000 server ... If I connect using SQL Authentication, ...
  • Re: SQL / IIS Application Pool Identity
    ... Set the authentication tag in web.config to "windows" (this way it actually ... to sql, which will obviously be different for each user. ... I want to use the application pool identity to make the> connection to the SQL server database. ...