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 ) .
Overview:
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.
Scenario:
OK so now that we have that basic scenario down let’s see what we need to make this work.
Necessary ingredients:
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.
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
App_Data
Default.aspx
Default.aspx.cs
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 +
"</b><br>");
}
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:
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.
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.
<snip>
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
Ticket
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
Description:
Logon Failure:
Reason: Unknown user name or bad password
User Name:
Domain:
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: 10.10.34.66
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
sPNMappings:
host=alerter,appmgmt,cisvc,clipsrv,browser,dhcp,dnscache,replicator,eventlog,eventsystem,policyagent,oakley,dmserver,dns,mcsvc,fax,msiserver,ias,messenger,n
etlogon,netman,netdde,netddedsm,nmagent,plugplay,protectedstorage,rasman,rpclocator,rpc,rpcss,remoteaccess,rsvp,samss,scardsvr,scesrv,seclogon,scm,dcom,cifs
,spooler,snmp,schedule,tapisrv,trksvr,trkwks,ups,time,wins,www,http,w3svc,iisadmin,msdtc
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.
1.
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.
2.
Open the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters\
3.
Create the following entry:
Value: KerbDebugLevel
Type: DWORD
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).
4.
Create the following entry in the same registry location:
Value: LogToFile
Data: 1
5.
Reproduce the error
6.
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 :
STATUS_WRONG_PASSWORD ntstatus.h
# 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
Time: 5:57:59 PM
User: N/A
Computer: GARBAGEXP
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
http/sp132027c.request132027.local
Updated object
C:\tools>setspn -A http/sp132027c request132027\SVC_IISPool
http/sp132027c
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 ID: 540
Time: 4:12:32 PM
User: REQUEST132027\bob
Successful Network Logon:
User Name: bob
Domain: REQUEST132027
Logon ID: (0x0,0x410C4)
Workstation Name:
Logon GUID: {b4f121f9-561c-e513-3e50-ad7dbff4a2e9}
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.
spatdsg