"Kerberos delegation .. end to end" Part I



First off let me say that I am not a “SQL guy” nor am I an “IIS guy” .. I am primarily a platforms OS kinda guy.


However, I can wing my way thru some of those two technologies. This series of posts may not exactly follow best practices when it comes to SQL or IIS but it will definitely get you up and running.


You may be thinking, there are already a ton of resources to show how to get this up and running. Simply use your favorite search engine and look for ‘Kerberos delegation SQL’ and you will find dozens of HOW TO articles, blogs etc..

I am hoping that this one will differ in two ways. One, is that it is an exact step by step. Two, is that we will go into the WHY’s of why we do certain actions to make it all work ( mostly from an OS authentication perspective ) .



Kerberos delegation specific to a 3 tier server design


What does that mean to a non “kerberos guy”? It means you want the following scenario to work.


  • Bob logs on to his XP machine which is a member of a domain.
  • Bob accesses a web site which should give him data “x”
  • The web site needs to be accessed as Bob – not as some service account or system.
  • The web site needs to access a SQL server, running on a different machine --- as Bob.

OK so now that we have that basic scenario down let’s see what we need to make this work.

Necessary ingredients:

  • Domain controller ( Win2k3 SP2 )
  • XP client ( sp2 )
  • IIS Web Server ( Win2k3 Sp2 )
  • SQL server ( 2005 )

OK so this isn’t really an end to end scenario.. I’m not going to walk you through DCpromo DNS etc.. I assume you have the basic domain working. If not, go run DCpromo and then come on back.

Now, let’s assume we have not setup the Web Server or SQL server yet.


The first thing to think about is – what or who are these services going to run as? Best practices for security says – not as system. OK . So let’s make a few domain accounts to run them as.

On the DC ( domain controller ) create the following 3 users. Don’t put them in any magical groups or anything.

  •                 Domain\Bob
  •                 Domain\SVC_IISPool
  •                 Domain\SVC_MSSQLServer

Great. On to installing IIS. Go ahead and make sure you install ASP.NET also:



Now we are going to create a new Application Pool which will run as Domain\SVC_IISPool – I called my app pool "SVC_POOL"



Then go to the properties of the new App pool you just made and go to the identity tab and set it up to run as the domain account you made:


Now create a new Web site – and make sure you do not enable anonymous access. I made a dir called c:\website and the site should be set to windows integrated authentication only.

I then went to the “home directory” tab and set it to run under the app pool I created earlier – the one I called SVC_POOL:

Execute permissions should be set to "Scripts only" unlike the screen shot below.


Also – make sure that the web site is configured to use ASP.NET 2.0:


Now, for the actual web site I’m going to stick with Visual Studio ( I suppose you could use visual studio express – it’s free http://msdn.microsoft.com/vstudio/express/

Open Visual Studio and click on File -> New Web Site.

Point it to c:\website ( or wherever your site is )



It will make the following dir and files:

S:\website>dir /b




Change your Default.aspx.cs to look like this:

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Security.Principal;

public partial class _Default : System.Web.UI.Page


    protected void Page_Load(object sender, EventArgs e)


        Response.Write(" This page is running as : <b>" + WindowsIdentity.GetCurrent().Name +




Now it should run and you will see it under the debugger like so:



Let it add the web.config file and then you should see:



Lets try it from IE ( not via Visual Studio )

Oh no! Service Unavailable… Ok what’s wrong here. ( remember I really am not an IIS guy )



Let’s see what have I done so far:

  • Made web site – set to integrated auth
  • Made app pool to run as SVC_IISPool
  • Set site to run under the app pool

Hrmm ok well I suppose that the SVC_IISPool account needs NTFS permissions to c:\website.. let’s try that.

Nope still fails

OK lets set it to use the defaultAppPool. Yup – that seems to work OK – we get:


Turns out there is this command called: aspnet_regiis.exe


It appears that this command grants proper membership and permissions to the service account

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727>aspnet_regiis.exe -ga request132027\svc_iispool


Start granting request132027\svc_iispool access to the IIS metabase and other directories used by ASP.NET.

Finished granting request132027\svc_iispool access to the IIS metabase and other directories used by ASP.NET.

Then I restarted the App Pool and it started working:



But – I don’t want to authenticate as SVC_IISPool – I want it to say I accessed the web site as Bob ( or whoever it is )


Edit your web.config file and add the line in red.


    Set compilation debug="true" to insert debugging

            symbols into the compiled page. Because this

            affects performance, set this value to true only

            during development.


                                <compilation debug="true"/>


            The <authentication> section enables configuration

            of the security authentication mode used by

            ASP.NET to identify an incoming user.


                                <authentication mode="Windows"/>

                                <identity impersonate="true"/>


            The <customErrors> section enables configuration

            of what to do if/when an unhandled error occurs

            during the execution of a request. Specifically,

            it enables developers to configure html error pages

            to be displayed in place of a error stack trace.


Now you should see :



So lets try this from the remote XP machine as Bob.


Oh no.. I’m prompted for authentication and then get this:


Tried adding authenticated users NTFS to c:\website – nope still fails

So let's think about this - why would It be denied?

· The site is being accessed via http://name -- if we used http://host.domain.com we need to add it to the intranet sites in IE in order for IE to recognize it should use integrated authentication.


· IE is set to use Windows integrated authentication – verified via the Tools->Options->Advanced.


· We can look at the security event logs on IIS and see it is using Kerberos.


· The web site runs as SVC_IISPool - but we have set <identity impersonate="true"/> what does this mean to us?

Let’s look on the wire..specifically between the client and the DC. You can use Netmon or Wireshark to do so.

We see the TGS request and a successful reply:

Kerberos TGS-REP

   Pvno: 5

    MSG Type: TGS-REP (13)

    Client Realm: REQUEST132027.LOCAL

    Client Name (Principal): bob

        Name-type: Principal (1)

        Name: bob


        Tkt-vno: 5

        Realm: REQUEST132027.LOCAL

        Server Name (Service and Instance): HTTP/sp132027c.request132027.local

            Name-type: Service and Instance (2)

            Name: HTTP

            Name: sp132027c.request132027.local

        enc-part rc4-hmac

The IIS machine name is sp132027c.request132027.local.



OK – time for a little lesson on this thing called Kerberos:

Based on http://technet2.microsoft.com/WindowsServer/en/library/4a1daa3e-b45c-44ea-a0b6-fe8910f92f281033.mspx


Message 4: The Ticket-Granting Service Response

The TGS response includes the ticket from the requested target server and a session key for the client and the target server with which to encrypt and decrypt their session messages.

The ticket is encrypted with the target server's secret key , so the client cannot decrypt and modify the ticket.

The message containing the session key for use with the target server is encrypted with the session key being used by the client and the TGS. The client can decrypt the new session key and use it to encrypt the authenticator that it will send to the target server along with the new ticket.

The “target server” in this case, is the target that the KDC identified via the Service Principal Name (SPN) passed to it.

Message 5: The Application Server Request

After the client has requested and received a TGT from the AS, and has requested and received a service ticket from the TGS, the client is ready to send the ticket to the target server.

The KRB_AP_REQ message contains an authenticator encrypted with the session key that the client and target server share, the service ticket encrypted with the target server's secret key, and the optional mutual authentication request. The mutual authentication response, which takes the form of a KRB_AP_REP message, is only necessary if the client requires verification that the target server is truly the recipient of the KRB_AP_REQ and subsequent messages.

So we take that TGS_REP, extract the data destined for the target and package it up and toss it over to the target server in an AP_REQ.

So let’s take a look at the IIS audit log, since this is the target server in our scenario:

Event Type: Failure Audit

Event Source: Security

Event Category: Logon/Logoff

Event ID: 529

Date: 11/10/2007

Time: 3:58:01 PM

User: NT AUTHORITY\SYSTEM – why does it show us accessing as system? Should be Bob right?

Computer: SP132027C


Logon Failure:

  Reason: Unknown user name or bad password

  User Name:


  Logon Type: 3

  Logon Process: Kerberos

  Authentication Package: Kerberos

  Workstation Name: -

  Caller User Name: -

  Caller Domain: -

  Caller Logon ID: -

  Caller Process ID: -

  Transited Services: -

  Source Network Address:

  Source Port: 1150

That’s odd.. it is showing up as system ( that would be the local machine account. )


I would expect to see Bob – or at the least, SVC_IISPool.

The net sniff indicates the requested SPN is for via cName (client name ) == Bob and the SPN is for HTTP/sp132027c.request132027.local.

Hrmm .. a quick search in the AD shows IIS automatically registered that on the machine account.


OK that’s a bit of a lie. What is it really?

LDP.EXE shows us the object information as follows:

>> Dn: CN=SP132027C,OU=IIS,DC=request132027,DC=local

                2> servicePrincipalName: HOST/SP132027C; HOST/sp132027c.request132027.local;

So if there is no HTTP service registered how does it know to use the account SP132027C?

There is this thing called SPNMapping that we do:


All of the following SPN’s can be implicitly mapped to the “HOST” service. I didn’t create these mappings, they are default in the OS

dn: CN=Directory Service,CN=Windows NT,CN=Services,CN=Configuration,DC=request132027,DC=local







So – Bob asks for a TGS specifically for the service HTTP/sp132027c.request132027.local to present to the web server.

The KDC looks and says – yup , found that SPN registered on \\sp132027c I will encrypt the session key with the password that the machine \\sp132027c will be able to decrypt.

KDC hands the ticket to Bob – Bob then hands it to the web server.

Now, the web site is running under SVC_IISPool – which is the one who needs to be able to authenticate the ticket - not the local machine account for \\sp132027c .


SVC_IISPool looks at the ticket, tries to decrypt it and can not, since it doesn’t have the right password



Can we prove this?


Let’s turn on some logging on the IIS Server and see what he thinks of this whole thing.

How to turn on debug output – via http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerberr.mspx

There are a number of ways to view the debug output from Kerberos. The easiest way is by logging the debug output to a file and then opening this file in Notepad.


Click Start, click Run, type regedit.exe, and then press ENTER.

CautionIncorrectly editing the registry might severely damage your system. Before making changes to the registry, you should back up any valued data on the computer.


Open the following registry key:



Create the following entry:

Value: KerbDebugLevel


Data: c0000043 (this value will print the most standard set of debug messages. Try it first. If you still want to see more output, set it to ffffffff).


Create the following entry in the same registry location:

Value: LogToFile


Data: 1


Reproduce the error


Open the file lsass.log, located

Once we turn on the logging we see the following when IE is accessing the site ( I like to use tail.exe –f to monitor the log )

412.492> Kerb-Error: KerbVerifyApRequest failed to check ticket 29 0162A7A0

412.492> Kerb-Warn: Failed to verify AP request (need u2u? false): 0xc000006a. d:\nt\ds\security\protocols\kerberos\client2\ctxtapi.cxx, line 3759

Err.exe shows us that 6a is:

Y:\>err 0xc000006a

# for hex 0xc000006a / decimal -1073741718 :


# When trying to update a password, this return status

# indicates that the value provided as the current password

# is not correct.

# 1 matches found for "0xc000006a"

And 29 = KRB_AP_ERR_MODIFIED kerberr.h

Sure enough – if we look on the XP clients event logs we see:

Event Type: Error

Event Source: Kerberos

Event Category: None

Event ID: 4

Date: 11/10/2007

Time: 5:57:59 PM

User: N/A



The kerberos client received a KRB_AP_ERR_MODIFIED error from the server host/sp132027c.request132027.local. This indicates that the password used to encrypt the kerberos service ticket is different than that on the target server. Commonly, this is due to identically named machine accounts in the target realm (REQUEST132027.LOCAL), and the client realm. Please contact your system administrator.

Status wrong password.. the SVC_IISPool account cannot decrypt the AP_REQ it is handed.

So how to make sure that the AP_REQ piece we get back in the TGS_REP is encrypted with the right keys so SVC_IISPool’s account can decrypt it?

We need to add the explicit HTTP SPN’s to SVC_IISPool account!

The two commands below can be summarized as:


  “Please modify the servicePrincipalName attribute on the SVC_IISPool account, and add the SPN’s for http/sp132027c.request132027.local and http/sp132027c


C:\tools>setspn -A http/sp132027c.request132027.local request132027\SVC_IISPool

Registering ServicePrincipalNames for CN=SVC_IISPool,OU=SVC_Accounts,DC=request132027,DC=local


Updated object

C:\tools>setspn -A http/sp132027c request132027\SVC_IISPool

Registering ServicePrincipalNames for CN=SVC_IISPool,OU=SVC_Accounts,DC=request132027,DC=local


Updated object

We can see via ldp.exe that the account now has the SPN’s

Expanding base 'CN=SVC_IISPool,OU=SVC_Accounts,DC=request132027,DC=local'...

Result <0>: (null)

Matched DNs:

Getting 1 entries:

>> Dn: CN=SVC_IISPool,OU=SVC_Accounts,DC=request132027,DC=local

                4> objectClass: top; person; organizationalPerson; user;

                1> cn: SVC_IISPool;

                1> givenName: SVC_IISPool;

                1> distinguishedName: CN=SVC_IISPool,OU=SVC_Accounts,DC=request132027,DC=local;

                1> instanceType: 0x4 = ( IT_WRITE );

                1> whenCreated: 11/10/2007 10:12:50 Pacific Standard Time Pacific Daylight Time;

                1> whenChanged: 11/10/2007 15:43:09 Pacific Standard Time Pacific Daylight Time;

                1> displayName: SVC_IISPool;

                1> uSNCreated: 24635;

                1> uSNChanged: 24714;

                1> name: SVC_IISPool;

                1> objectGUID: e5d2f719-ff72-4e75-9ca1-608947d11107;

                1> userAccountControl: 0x10200 = ( UF_NORMAL_ACCOUNT | UF_DONT_EXPIRE_PASSWD );

                1> badPwdCount: 0;

                1> codePage: 0;

                1> countryCode: 0;

                1> badPasswordTime: 01/01/1601 00:00:00 UNC ;

                1> lastLogoff: 01/01/1601 00:00:00 UNC ;

                1> lastLogon: 11/10/2007 15:33:13 Pacific Standard Time Pacific Daylight Time;

                1> pwdLastSet: 11/10/2007 10:12:50 Pacific Standard Time Pacific Daylight Time;

                1> primaryGroupID: 513;

                1> objectSid: S-1-5-21-331918943-2602081550-104023558-1119;

                1> accountExpires: 09/14/30828 02:48:05 UNC ;

                1> logonCount: 7;

                1> sAMAccountName: SVC_IISPool;

                1> sAMAccountType: 805306368;

                1> userPrincipalName: SVC_IISPool@request132027.local;

                2> servicePrincipalName: http/sp132027c; http/sp132027c.request132027.local;

                1> objectCategory: CN=Person,CN=Schema,CN=Configuration,DC=request132027,DC=local;

                1> lastLogonTimestamp: 11/10/2007 10:17:02 Pacific Standard Time Pacific Daylight Time;


Now we go back to the XP machine and try again…


And IIS registers a successful event:

Event Type: Success Audit

Event Source: Security

Event Category: Logon/Logoff

Event ID: 540

Date: 11/10/2007

Time: 4:12:32 PM

User: REQUEST132027\bob

Computer: SP132027C


Successful Network Logon:

  User Name: bob

  Domain: REQUEST132027

  Logon ID: (0x0,0x410C4)

  Logon Type: 3

  Logon Process: Kerberos

  Authentication Package: Kerberos

  Workstation Name:

  Logon GUID: {b4f121f9-561c-e513-3e50-ad7dbff4a2e9}

  Caller User Name: -

  Caller Domain: -

  Caller Logon ID: -

  Caller Process ID: -

  Transited Services: -

  Source Network Address:

  Source Port: 1165

Whewww!! Seems like a lot of work to get that little ole BOB logon…


See http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/523ae943-5e6a-4200-9103-9808baa00157.mspx?mfr=true for more of a high level overview.


I think that will end this part 1 of the series on Kerb\Delegation and adventures of an IIS\SQL newbie.


If this was helpful, I'd be interested in knowing - or if you find the series interesting\informative - or utter crap.


Also - are these posts too long? I read through it and it seemed a little long.. but what do you think?



Next up – SQL installation.