"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.

 

k21

k22

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.

So does https://technet2.microsoft.com/windowsserver/en/library/8127f5ed-4e05-4822-bfa9-402ceede47441033.mspx?mfr=true

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

Element

Description

ServiceClass

A string that identifies the general class of service, for example, SqlServer. There are well-known service class names, such as www for a Web service or ldap for a directory service. In general, this can be any string that is unique to the service class. Be aware that the SPN syntax uses a forward slash (/) to separate elements; therefore, the forward slash cannot appear in a service class name.

Host

The name of the computer on which the service is running. This can be a fully-qualified DNS name or a network basic input/output system (NetBIOS) name. Be aware that NetBIOS names are not guaranteed to be unique in a forest, and Active Directory does not enforce uniqueness for the host component of an SPN.

Port

An optional port number to differentiate between multiple instances of the same service class on a single host computer. This component can be omitted if the service uses the default port number that is reserved for its service class (port number 389 for LDAP, for example).

ServiceName

An optional name that is used in the SPN of a replicable service to identify the data or services that are provided by the service or to identify the domain that is served by the service. This component can have one of the following formats (each of which are guaranteed to be unique within Active Directory):

The distinguished name or objectGUID of an object in Active Directory, such as a connection point object.

The DNS name of the domain for a service that provides a specified service for a domain as a whole.

The DNS name of an SRV or mail exchange (MX) record.

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.

spn trace

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.

 

k23

I created a new database.

k24

Create the new database and a table with some data…

k25

 

Save the table and then right click on it and choose Show Table Data .

 

k26

You can then populate the data with whatever you want..

k27

 

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.

 

k28

Run the debugger and it will fire up IE:

k29

 

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:

 

k30

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