Re: Unable to use Kerberos Delegation from IE6 --> IIS 5.0 (asp.net)--> SQL 2000
From: Bob (BobxNoSpam)
Date: 03/24/03
- Next message: John Saunders: "Re: Managed to Unmanaged security."
- Previous message: Chris Blanco: "Managed to Unmanaged security."
- Maybe in reply to: Bob: "Re: Unable to use Kerberos Delegation from IE6 --> IIS 5.0 (asp.net)--> SQL 2000"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "Bob" <BobxNoSpam> Date: Mon, 24 Mar 2003 14:26:55 -0600
WIA was not possible because the SQL Server's port # had been changed from 1433 to 1433,12345 (i.e. it now had 2 listening ports).
The web application was using an alias that forced port 12345 to be used.
No problem with this configuration except SQL Server isn't smart enough to register all the ports in the Active Directory.
Thus, the only registered Service Principal Name (SPN) was for port 1433.
The solution was to manually register the additional port of 12345 and WIA worked immediately.
PROBLEM:
========
When attempting to make a connection using ADO.NET connection string:
"Data Source=SQLBOX;Initial Catalog=PUBS;User Id=;Password=;Integrated Security=SSPI;"
the following exception occurs:
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.
CAUSE:
======
SPNs not registered correctly to reflect SQL port change.
RESOLUTION:
===========
C:\>SETSPN -A MSSQLSvc/SQLBOX.company.com:11433 COMPANY\SA_SQL Registering ServicePrincipalNames for CN=SA_SQL,CN=Users,DC=company,DC=com
MSSQLSvc/SQLBOX.company.com:12345
Updated object
C:\>SETSPN -L COMPANY\SA_SQL
Registered ServicePrincipalNames for CN=SA_SQL,CN=Users,DC=company,DC=com:
MSSQLSvc/SQLBOX.company.com:12345
MSSQLSvc/SQLBOX.company.com:1433
"Bob" <BobxNoSpam> wrote in message news:#vO$$Y73CHA.2644@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
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
- Next message: John Saunders: "Re: Managed to Unmanaged security."
- Previous message: Chris Blanco: "Managed to Unmanaged security."
- Maybe in reply to: Bob: "Re: Unable to use Kerberos Delegation from IE6 --> IIS 5.0 (asp.net)--> SQL 2000"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
