Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> .NET Development >> .NET Framework Data Access and Storage

User credentials delegation from IIS on WinXP to SQL Server on Win2003SRV fails

Problem:

I am trying to create an asp.net website with integrated windows authentication
to access SQL databases. IIS resides on WinXP and SQL Server
on Win2000 SRV. Both are in the same NT Domain. IIS and SQL Server cannot
reside on the same machine and a stand alone web server is
ideal as the website needs to access multiple SQL Servers. IIS is set to
Integrated Windows Authentication. The anonymous access in IIS settings is unchecked.
The web.config file has Identity element
set for impersonation <identity impersonation="true">
The machine running IIS & the SQL Server
are set to be "trusted for delegation" in active directory. The domain user
accounts that will be accessing the databases are not marked as "Account
is sensitive and cannot be delegated". SPN is set and registered.
The connection string that the web app uses to connect to SQL database is:
"Data Source=PWSSQLT;Integrated Security=SSPI;Initial Catalog=Pace_Master;Persist Security Info=true"
with which the user credentials should be flown to the SQL database.
But instead the delegation fails and results in the following ANONYMOUS authentication failure error.
All this works if I use http:\\localhost to access website (but fails when IPAddress is used) or
pass user credentials of an nt domain account (this is the same account that fails to login to SQL SRV
by setting only the impersonation attribute of the identity element to true & no user credentials)
in the identity tag of the web.config file. So I am confused as to what might be causing the login failure.
Is this NT configuration issue related to Kerberos authentication or the account access under which
SQL Server is running or IIS settings related issue. So far I haven't found info to help resolve the
issue. Any help would greatly be appreciated.


Authentication Failure Error:
--
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Description: An unhandled exception occurred during the execution of the current web request.
Please review the stack trace for more information about the error and where it originated in the code.

--

Exception Details: System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Source Error:

// Open the connection, and return it

oConn.Open();
return oConn;


Source File: e:\ING eIMS\App_Code\DataAccess\ConnectionManager.cs

Stack Trace:
[SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]

System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader
dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject,
SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity,
SqlConnectionString connectionOptions, Object providerInfo, String newPassword,
SqlConnection owningObject, Boolean redirectedUserInstance)
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options,
Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)

System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open()

INGRS.DataAccess.ConnectionManager.GetConnection() in e:\ING eIMS\App_Code\DataAccess\ConnectionManager.cs:
DAActivity.Page_Load(Object sender, EventArgs e) in e:\ING eIMS\DA\DAStatus\DAActivity.aspx.cs
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e)

System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e)
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()

System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210


The Application event log indicates the "Authentication Type" to be "Negotiate" instead of "Kerberos" or "NTLM" and "Is Impersonated" to be false.

[5402 byte] By [kkos] at [2007-12-28]
«« TAPI lineIntializeEx() Compact Framework V1
»» Performance degradation in December CTP of AS 2005 SP2
# 1

This error is going to happen by default because you need Kerberos delegation to allow hopping credentials from once machine to another.

So this will work by default:

User1 on Machine1 -> IIS on Machine2 + SQL on Machine2

This will not work:

User1 on Machine1 -> IIS on Machine2 -> SQL on Machine3

The reason is Windows does not allow hopping credentials more than one single machine hop by default. It does this for security reasons. When a hop fails, you get the ''NT AUTHORITY\ANONYMOUS LOGON' user.

For a good book explaining the in's and out's of this issue pick up a copy of:

"Building Secure Microsoft ASP.NET Applications" from Microsoft Press.

Matt

MattNeerincx at 2007-9-4 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 2

That is true. But what I am trying to do here is to pass identity from

User1 on Machine1 -> IIS on Machine2 -> SQL on Machine3 with Kerberos delegation.

Can the windows identity make the double-hop if Kerberos delegation is set up correctly?

If so can somebody point out what additional steps are required to achieve the second hop with Kerberos delegation?

(The configuration steps that I followed for Kerberos delegation are listed in my previous post).

Thank You.

kkos at 2007-9-4 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 3

Looks like I responded too soon.

From looking at what you have configured, you are very close to getting this working with Kerberos.

Here are a few other issues that could trip you up:

1. Open up IE and make the target web site a trusted site. Latest version of IE will not do Kerberos with non-trusted sites.

2. In IE under Tools | Internet Options ensure that "Enable Windows Integrated Authentication" is checked.

Once the above two are complete, turn on security auditing on the IIS machine and hit the machine from the remote client. Check the security log and ensure that you are getting Kerberos logins, you should see a success audit like below:

Successful Network Logon:

User Name: MyUser

Domain: MyDomain

Logon ID: (0x0,0x88A596)

Logon Type: 3

Logon Process: Kerberos <- This is the important thing you need to see!

Authentication Package: Kerberos

Work on one problem at a time. Keep working on the IE part until you can get a successful Kerberos login in event log.

Then you need to work on the IIS to SQL hop.

For SQL Server it's fairly straightforward, you need to have an SPN registered for the SQL Server instance. By default SQL will create one for you if you run SQL under LocalSystem or Network Service. If you run SQL Server service under a domain account you have to manually register the SPN for SQL Server using the SETSPN tool. Client needs to resolve fully qualified name (FQDN) of sql server via DNS, you can test this via ping -a <ip of SQL> should return FQDN.

MattNeerincx at 2007-9-4 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 4

I tried to handle the IE part first to get a successful Kerberos login in event log but no luck.

I made sure that the "Enable IWA" option (previously set) is set and added the "target site entry as a trusted site" and sent a request to IIS from remote client. The security event log indicates the following:

Logon Type: 3

Logon Process: NTLmSsp

Authentication Package: NTLM

So the first hop resolves to NTML instead of Kerberos. Please let me know if I need to check other settings to get the first hop to go through Kerberos login. I really need to get this working to be able to get our intranet web site up and running in a secure mode.

Thank You.

kkos at 2007-9-4 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 5

Sounds like you are close.

IE is not too complicated. Basically when you type in the URL, IE takes the root of this and performs a forward and reverse lookup to get the fully qualified name of the target server. Then it prepends HOST/ to the FQDN, like so:

http://fooserver/mywebpage.html

IE takes fooserver -> forward lookup -> 123.123.123.123 -> reverse lookup -> fooserver.mydomain.com

Next IE prepends HOST -> HOST/fooserver.mydomain.com

Now IE has the SPN of the target server, IE passes this to InitializeSecurityContext function to start SSPI.

InitializeSecurityContext checks active directory for SPN entry for HOST/fooserver.mydomain.com, if this is found, then Kerberos is attempted, if it is not found NTLM is used.

So easy way to check is on client machine use the web server name do ping, then ping -a <ip of web server>, this will tell you what IE is using for FQDN.

You can add additional HOST SPNs to target web server like short name to help this process work as well, for example:

setspn -A HOST/fooserver fooserver

But typically windows has the HOST SPNs setup already for you.

Matt

MattNeerincx at 2007-9-4 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 6

IIS runs as local system on an WinXP machine and hence there is no need to explicitly set an SPN for this service right? When I ping the IP address of the web server with -a option it returns the FQDN.

Thank You.

kkos at 2007-9-4 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 7
I'll check with my buddy in IE to see if there is anything else we need to enable. Let me know what version of IE you are using.
MattNeerincx at 2007-9-4 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 8

My buddy pointed out this KB, but I think we covered all of these:

How to configure an ASP.NET application for a delegation scenario

http://support.microsoft.com/kb/810572

But just to be sure I would read the supplemental issues under the "Configure Internet Explorer for Delegation" section.

Another tool you can try is wfetch from IIS support tools (search for wfetch you can download it for free). With it you can force Kerberos auth as a test and see if you can get the hops to work at least.

MattNeerincx at 2007-9-4 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
# 9

Versions that I am using:

IE: 6.0 SP2

IIS: 5.1

Thank You.

kkos at 2007-9-4 > top of Msdn Tech,.NET Development,.NET Framework Data Access and Storage...
.NET Development
.NET Framework Data Access and Storage

.NET Development Hot Topic

.NET Development New Topic

.NET Development

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified