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

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