"Kerberos delegation .. end to end" Part II
When we left off - I was about to install SQL.
Also my standard disclaimer for this series:
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 ) .
So for this part of the topic I am simply installing SQL 2005 … and true to an end user, clicking next, next, next, next.. until the blinky lights stop.
During the clicking , I also set it to use Windows authentication.
By default, I set it to run as local system, however this poses a security risk so I have changed this to run as our SQL service account we created in part one. Domain\SVC_MSSQLServer
I’ve read that you should use the SQL server configuration manager to change the service properties ( instead of the services.msc ) so I popped it open and found the SQL server and SQL server agent services and altered them to run as the domain service account we see here.
What does this do for us?
Let’s look at this from a Kerberos perspective.
When you install SQL as Local System it will automatically create the following SPN’s on the machine account which is running SQL:
3> servicePrincipalName: MSSQLSvc/sp132027b.request132027.local:1433; HOST/SP132027B; HOST/sp132027b.request132027.local;
When you switch to a service account, it will remove the MSSQLSvc SPN from the machine account and then register this Event in the event log:
Event Type: Information Event Source: MSSQLSERVER Event Category: (2) Event ID: 26037 Date: 11/15/2007 Time: 9:57:07 AM User: N/A Computer: SP132027B Description: The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies. |
Well – now that is an interesting event. Easily missed as an informational event ( for those yellow and red flag only folks ) . Of course, we know that in order to use delegation we are going to need Kerberos – so back to our tool Setspn.exe.
Again – the following is read as “On the domain account - request132027\SVC_MSSQLServer, please add the following data to the SPN attribute ‘MSSQLSvc/sp132027b.request132027.local:1433’ , thanks”
C:\tools>setspn -A MSSQLSvc/sp132027b.request132027.local:1433 request132027\SVC_MSSQLServer
Registering ServicePrincipalNames for CN=SVC_MSSQLServer,OU=SVC_Accounts,DC=request132027,DC=local
MSSQLSvc/sp132027b.request132027.local:1433
Updated object
My .02 on SPN’s
SecMakeSPN() gives a pretty good over view of the various components of an SPN.
Let’s just steal some of that content since we never know when it’s going to move\be removed.
Name Formats for Unique SPNs An SPN must be unique in the forest in which it is registered. If it is not unique, authentication will fail. The SPN syntax has four elements: two required elements and two additional elements that can be used, if necessary, to produce a unique name, as follows: ServiceClass/Host:Port/ServiceName Each of these SPN elements is described in the following table. SPN Elements
The components that are present in a service’s SPNs depend on how the service is identified and replicated. There two types of services: host-based services and replicable services. |
The IIS machine in this case – specifically requests the port :1433 in the TGS_REQ. I’m not enough of a SQL guy to change it – but it looks like I would change my client protocols data and the connection string in my web.config to use the new port? Maybe.. I didn’t test this.
Here I changed the SPN to a different port number and it fails the TGS_REQ and in the SQL audit logs I see it used NTLM to logon.
Event Type: Success Audit
Event Source: Security
Event Category: Logon/Logoff
Event ID: 540
Date: 11/15/2007
Time: 3:09:29 PM
User: REQUEST132027\Administrator
Computer: SP132027B
Description:
Successful Network Logon:
User Name: Administrator
Domain: REQUEST132027
Logon ID: (0x0,0xF65CA)
Logon Type: 3
Logon Process: NtLmSsp
Authentication Package: NTLM
Workstation Name: SP132027C
On to the “web app”
It’s really just a glorified chart – but it will do.
Go back to the same web app we made in Visual studio and either add a new connection to your SQL server, or Create a new database.
I created a new database.
Create the new database and a table with some data…
Save the table and then right click on it and choose Show Table Data .
You can then populate the data with whatever you want..
Now go back to your website in “design view” and drag the table from the left pane “Server Explorer” to the design view web page.
Run the debugger and it will fire up IE:
Yay.
Looks like it works .. it says I accessed the web page as Admin and we get the data.
So far so good.
Now if you launch IE from the XP client ( the one running as Bob ) you will see an error
Server Error in '/' Application.
--------------------------------------------------------------------------------
Runtime Error
Description: An application error occurred on the server. The current custom error settings for this application prevent the details of the application error from being viewed remotely (for security reasons). It could, however, be viewed by browsers running on the local server machine.
Details: To enable the details of this specific error message to be viewable on remote machines, please create a <customErrors> tag within a "web.config" configuration file located in the root directory of the current web application. This <customErrors> tag should then have its "mode" attribute set to "Off".
<!-- Web.Config Configuration File -->
<configuration>
<system.web>
<customErrors mode="Off"/>
</system.web>
</configuration>
<snipped>
So once you have added <customErrors mode="Off"/> you see a better error:
Server Error in '/' Application.
--------------------------------------------------------------------------------
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:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
<snipped>
Well – that’s no good.
Why are we using anonymous?
From the same logging we enabled previously:
Kerb log on IIS 408.504> Kerb-Cred: Acquiring cred, S4U required 408.524> Kerb-S4u: Trying S4UProxy for ls 0009F660 408.304> Kerb-Bnd: KerbInsertBinding binding cache disabled 408.304> Kerb-Bnd: Calling kdc 10.10.34.41 for realm REQUEST132027.LOCAL 408.304> Kerb-Bnd: KerbInsertBinding binding cache disabled 408.304> Kerb-Bnd: Calling kdc 10.10.34.41 for realm REQUEST132027.LOCAL 408.304> Kerb-Warn: KerbGetTgsTicket failed to unpack KDC reply: 0x3c 408.304> Kerb-Warn: Failed S4Uproxy request c00000bb(4) 408.524> Kerb-Bnd: KerbInsertBinding binding cache disabled 408.524> Kerb-Bnd: Calling kdc 10.10.34.41 for realm REQUEST132027.LOCAL 408.524> Kerb-Warn: KerbGetTgsTicket failed to unpack KDC reply: 0x3c 408.524> Kerb-Warn: Failed S4Uproxy request c00000bb(4) 408.524> Kerb-Warn: SpInitLsaModeContext failed to get outbound ticket, KerbGetServiceTicketByS4UProxy failed 0x8009030e 408.512> Kerb-Warn: KerbVerifyPacSignature contacting domain REQUEST132027.LOCAL for user bob 408.512> Kerb-Trace: KerbCreateTokenFromTicket for REQUEST132027\bob, (null) 408.512> Kerb-LSess: KerbCreateLogonSessionFromTicket NOT creating ASC logon session for 0:0x7050a, 408.512> Kerb-Trace: SpAcceptLsaModeContext called KerbMapContext ContextAttributes 0x5, 0 408.492> Kerb-S4u: KerbCreateDummyLogonSession created logon session for 0x0:0x7050a - 0009F240 408.492> Kerb-Cred: Cant go off box w/ non-fwdble logon session & no supp creds 408.492> Kerb-Cred: Cant go off box w/ non-fwdble logon session & no supp creds |
IIS Server sec log: Event Type: Success Audit Event Source: Security Event Category: Logon/Logoff Event ID: 540 Date: 11/14/2007 Time: 4:28:56 PM User: REQUEST132027\bob Computer: SP132027C Description: Successful Network Logon: User Name: bob Domain: REQUEST132027 Logon ID: (0x0,0x6DAC3) Logon Type: 3 Logon Process: Kerberos Authentication Package: Kerberos Workstation Name: Logon GUID: {560cf99e-ac15-ea70-9133-6efb83a7ffbe} Caller User Name: - Caller Domain: - Caller Logon ID: - Caller Process ID: - Transited Services: - Source Network Address: 10.10.34.66 Source Port: 1131 |
The SQLserver Security log: Event Type: Success Audit Event Source: Security Event Category: Logon/Logoff Event ID: 538 Date: 11/14/2007 Time: 4:28:56 PM User: NT AUTHORITY\ANONYMOUS LOGON Computer: SP132027B Description: User Logoff: User Name: ANONYMOUS LOGON Domain: NT AUTHORITY Logon ID: (0x0,0xE9988) Logon Type: 3 |
The SQLserver Application log: Event Type: Failure Audit Event Source: MSSQLSERVER Event Category: (4) Event ID: 18456 Date: 11/14/2007 Time: 4:28:56 PM User: NT AUTHORITY\ANONYMOUS LOGON Computer: SP132027B Description: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 10.10.34.43] |
So we see Bob get to the IIS Server via Kerberos.
Then we assume IIS, or more specifically SVC_IISPool , will then try to impersonate Bob and access the SQL back end. Kind of like delegation eh?
OK well I cant cover that here and now , this post is already too long. So next time we jump into the how's and why's of this .
spatdsg
Comments
Anonymous
November 26, 2007
When we last left off, we had just installed SQL. Also my standard disclaimer for this series: FirstAnonymous
April 14, 2008
Last week, I spent an all-nighter troubleshooting a Kerberos issue for a MOSS installation. AlthoughAnonymous
October 15, 2008
Instead of maintaining the SPN attribute yourself via setSPN you can via ADSIedit give the SELF user of the service account READ/WRITE permissions to servicePrincipalName. SQL will maintain the SPN itself every time you restart SQL. e.g. Select properties of the account SVC_MSSQLServer. select the security tab and locate the SELF user. Click the Advanced tab. Select effective permissions tab. check READ/WRITE on servicePrincipalName restart SQLAnonymous
November 06, 2010
Hi Spat, Did you write the next article in this series of blogs for Kerberos and sample application working on .NET and SQL .... I tried finding it here on your blogs but was unsuccessful. ThanksAnonymous
December 29, 2010
Yup - check out blogs.msdn.com/.../kerb-part-3.aspx Amit