Re: Unable to use Kerberos Delegation from IE6 --> IIS 5.0 (asp.net)--> SQL 2000

From: Bob (BobxNoSpam)
Date: 03/03/03 

From: "Bob" <BobxNoSpam>
Date: Sun, 2 Mar 2003 19:07:59 -0600

I made a typo below. It should have said:

    Web Name is WIA with only WIA Enabled (http://myweb/wia/default.aspx)

  "Bob" <BobxNoSpam> wrote in message news:u0vqP3y3CHA.2184@TK2MSFTNGP11.phx.gbl...
  Problem:

    We cannot get Delegation to work from IE6 Browser --> IIS (ASP.NET) --> SQL2K SP3

    When attempting to make a connection using ADO.NET connection string:

      "Data Source=MYSQL;Initial Catalog=PUBS;User Id=;Password=;Integrated Security=SSPI;"

    the following exception occurs:

      Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

    We believe this worked a couple weeks ago and it recently stopped working. At this point, no one is 100% sure what happened as we've tried so many permutations of settings. About the time we instaled SQL 2000 SP3, all our integrated apps ceased to work.

    Traditional ASP using same 3 boxes (Browser --> IIS --> SQL2K) still works so delegation appears to be working.

    I've listed all the details of our environment below.

  More Info

    ASP Connection string is:

    Provider=SQLOLEDB;Data Source=MYSQL;Initial Catalog=PUBS;User Id=;Password=;Integrated Security=SSPI;Network Library=DBMSSOCN;

    I've read and applied the great information found in:

    Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication
    http://msdn.microsoft.com/library/en-us/dnnetsec/html/SecNetHT02.asp?frame=true

    HOW TO: Enable Kerberos on a Non-Domain Controller for IIS Web Applications
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q326089

    SQL Server 2000 Books Online (Updated - SP3)
    http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp?SD=GN&LN=en-us&gssnb=1</em><br> <em>&gt; </em><br> < http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295255

      Search for: Security Account Delegation

    ACC2002: "Login Failed for User 'NT AUTHORITY\ANONYMOUS LOGON'." Error When You Try to Link a Table
    http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295255

    SETSPN Utility
    http://www.microsoft.com/windows2000/techinfo/reskit/tools/existing/setspn-o.asp

  Hardware Description:

    All 3 computers are in the Windows 2000 Domain COMPANY
    All 3 computers are running only TCP/IP

  Workstation (Browser):

    W2K Pro SP3 member

    User: MyUser

    IE6 with [X] Enable Integrated Windows Authentication checked.

  MYWEB (IIS 5.0):

    W2K member Server SP3

  MYSQL (SQL 2000):

    W2K member Server SP3
    SQL Server configured for TCP/IP Protocol Only
    SQL Server listening on ports 1433 and 11433
    SQL Server service running as COMPUTER\SQL_MYSQL user account
    COMPUTER\SQL_MYSQL is marked as [X] Account is trusted for delegation NOT REQUIRED???
    SPN created (see SPN SP3 below) NOT REQUIRED???

  Web Site Configuation:

    Web Name is WIA with only Basic Authentication Enabled (http://myweb/wia/default.aspx)

    WEB.CONFIG contains

      <system.web><identity impersonate="true" />

    MACHINE.CONFIG any of the following (all 3 have been tested):

      <processModel userName="SYSTEM" password="AutoGenerate"...

    or

      <processModel userName="machine" password="AutoGenerate"...

    or

                <processModel userName="COMPANY\ASPNET_MYWEB" password="password goes here"...

    IIS and W3SVC services are running as Local System

  Other Facts:

    Server Variables:

      ASPX page returns AUTH_TYPE = Negotiate thus Kerberos Authentication appears to be working.
      ASPX page returns AUTH_USER = COMPANY\MyUser

    COMPANY\MyUser

      Domain Administrator Account
      [x] Account is trusted for delegation

    COMPANY\ASPNET_MYWEB user account configured as:

      [x] Password Never Expires
      [x] Account is trusted for delegation

      Domain Security Policy (COMPANY) User Rights:

        Act as part of the operating system
        Logon as a batch job

      Local Security Policy (MYWEB) User Rights:

        Act as part of the operating system
        Logon as a batch job
        Logon as a service (no overriding Domain Policy on this)

      Read/write access is required to the %installroot%\ASP.NET Temporary Files directory. Sub-directories beneath this root are used for dynamically compiled output.

      Read/write access is required to the %temp% directory. This is used by the compilers during dynamic compilation.

      Read access is required to the application directory.

      Read access is required to the %installroot% hierarchy to allow access to system assemblies.

    COMPANY\MYWEB computer account configured as:

      [x] Trust computer for delegation

  SPN SP3
            C:\ >setspn -L MYSQL

    Registered ServicePrincipalNames for CN=MYSQL,CN=Computers,DC=COMPANY, DC=com:

    SMTPSVC/MYSQL
    SMTPSVC/MYSQL.COMPANY.com
    HOST/MYSQL
    HOST/MYSQL.COMPANY.com

    C:\>SETSPN -A MSSQLSvc/MYSQL.COMPANY.com COMPANY\SA_SQLSVR

    Registering ServicePrincipalNames for CN=SA_SQLSVR,CN=Users,DC=COMPANY,DC=com

    MSSQLSvc/MYSQL.COMPANY.com

    Updated object

    C:\>setspn -L COMPANY\SA_SQLSVR

    Registered ServicePrincipalNames for CN=SA_SQLSVR,CN=Users,DC=COMPANY,DC=com:

    MSSQLSvc/MYSQL.COMPANY.com
    MSSQLSvc/MYSQL.COMPANY.com:1433

  Bob @ Priority Software . com

Quantcast